Ajax & WordPress on Front End - Database not updat

Question

So I'm working on a WordPress website. I'm having a bit of a difficult time getting the user input to update the database.

JS:

var ID = $(this).attr('id');
var name = $("#name_input_"+ID).val();
var createDate = $("#created_input_"+ID).val();
var stats = $("#status_input_"+ID).val();
var dateExpire = $("#disbanded_input_"+ID).val();
var dataString = 'id=' + ID + '&name=' + name + 
'&createDate=' + createDate + '&stats=' + stats + '&dateExpire=' + dateExpire;

// can place loading image here

if (name.length > 0 || createDate.length > 0 || 
stats.length > 0 || dateExpire.length > 0) {
  $.ajax({
    type: "POST",
    url: "../edit-items.php",
    data: dataString,
    cache: false,
    error: function(xhr, status, error) {
      var err = eval("(" + xhr.responseText + ")");
      alert(err.Message);
    },
    success: function (html) {
      $("#name_"+ID).html(name);
      $("#createDate_"+ID).html(createDate);
      $("#stats_"+ID).html(stats);
      $("#dateExpire_"+ID).html(dateExpire);
    }
  });
} else {
  alert("Enter text to make an edit.");
}
});

PHP (edit-items.php)

global $wpdb;

if($_POST['id']) {
    $id = esc_sql($_POST['id']);
    $name = esc_sql($_POST['name']);
    $created = esc_sql($_POST['createDate']);
    $stats = esc_sql($_POST['stats']);
    $dateExpire = esc_sql($_POST['dateExpire']);

    $query = "UPDATE items SET itemName='$name', createDate='$created', itemStats='$stats', dateExpire='$dateExpire' WHERE committee_id='$id';"

    $wpdb->query($wpdb->prepare($query));
  }

Whenever I try to update the table, I am able to do it successfully on the front end, but on refresh, the data change does not stick. Checking the database further verifies that the data has not been changed... It seems like the "success" is passing in the AJAX, but for some reason, it's not updating the database.

Any insight on this would be helpful!

Edit Decided to start using the admin-ajax.php method. Everything is the same except I changed the url in the JS to url: "/wp-admin/admin-ajax.php", and the code from edit-items.php is now in functions.php like so:

 function editCommittee() {  
  global $wpdb;

if($_POST['id']) {
    $id = esc_sql($_POST['id']);
    $name = esc_sql($_POST['name']);
    $created = esc_sql($_POST['createDate']);
    $stats = esc_sql($_POST['stats']);
    $dateExpire = esc_sql($_POST['dateExpire']);

    $wpdb->update('itemsTable',
                array(
                    'name' => $name
                ),
                array(
                    'committee_id' => $id
                ),

                array(
                  '%s'
                )
  );
  echo $id;
  exit();
  }
}
add_action('wp_ajax_editItems', 'editItems');

Answer 1

$wpdb->update( 'table',
array( 'column' => $name),
array( 'ID' => 1 ),
array( '%s'), 
array( '%d' )
);

Answer 2

Ok, so first localize your ajaxurl object

add_action( 'wp_enqueue_scripts', 'frontend_enqueued_scripts' );

/**
 * Localization object
 *
 * @since 1.0.0
 */
function frontend_enqueued_scripts() {

    wp_enqueue_script( 'script', get_template_directory_uri() . '/js/custom.js', array( 'jquery' ), '', true );
    wp_localize_script( 'script', 'ajax_object', array(
        'ajaxurl' => admin_url( 'admin-ajax.php' ),
    ));
}

If you have, in your functions.php a place where you are enqueuing front end scripts place the wp_enqueue_script and wp_localize_script part in there. Also if you are placing your ajax calling javascript inside a file that is not called custom.js change the name to point to it. I always use custom.js as a file where I store all my theme related javascript.

The above code will create an ajax_object object on your front end that will be available to the code inside the custom.js, since you've attached it to that script. The handles in the enqueued file and localized script (in our case script) must be the same for this to work.

Then you can create, in functions.php file, or in any included file where you put your ajax functions, a callback function

/**
 * Front and back end ajax hooks.
 */
add_action( 'wp_ajax_edit_committee', 'edit_committee' );
add_action( 'wp_ajax_nopriv_edit_committee', 'edit_committee' );

/**
 * Ajax callback function
 *
 * @since 1.0.0
 */
function edit_committee() {
    global $wpdb;

    if ( isset( $_POST['id'], $_POST['committee_nonce'] ) && wp_verify_nonce( sanitize_key( $_POST['committee_nonce'] ), 'committee_nonce_action' ) && '' !== $_POST['id'] ) { // Input var okay.
        $id          = ( isset( $_POST['id'] ) && '' !== $_POST['id'] ) ? sanitize_text_field( wp_unslash( $_POST['id'] ) ); : ''; // Input var okay.
        $name        = ( isset( $_POST['name'] ) && '' !== $_POST['name'] ) ? sanitize_text_field( wp_unslash( $_POST['name'] ) ); : ''; // Input var okay.
        $created     = ( isset( $_POST['create_date'] ) && '' !== $_POST['create_date'] ) ? sanitize_text_field( wp_unslash( $_POST['create_date'] ) ); : ''; // Input var okay.
        $stats       = ( isset( $_POST['stats'] ) && '' !== $_POST['stats'] ) ? sanitize_text_field( wp_unslash( $_POST['stats'] ) ); : ''; // Input var okay.
        $date_expire = ( isset( $_POST['date_expire'] ) && '' !== $_POST['date_expire'] ) ? sanitize_text_field( wp_unslash( $_POST['date_expire'] ) ); : ''; // Input var okay.

        $updated = $wpdb->update( 'itemsTable', array(  'name' => $name ), array( 'committee_id' => $id ), array( '%s' ) );
        if( false !== $updated ) {
            wp_die( 'success' );
        } else {
            wp_die( 'fail' );
        }

    }
}

I added the sanitization checks. It's crucial that you include a nonce in your form that you'll use to submit the data.

You can add it by placing

wp_nonce_field( 'committee_nonce_action', 'committee_nonce' );

inside the <form> element on your front end. This will generate a nonce, which is a good security measure, especially when you're writing to the database.

The callback function will check if the nonce is set, if the $_POST['id'] variable is set and not empty, and will first verify the nonce.

Then you can carry on with the execution of the function.

I left all the $POST variables there even though you're not using them, but maybe you'll want to use them later on. I also sanitized them and unslashed them.

The // Input var okay. comment is for phpcs purposes, you can ignore that.

Then you preform the update. If you have the itemsTable in the database it should update it with the data you provided.

The last but not the least is the javascript code. I assumed that you are preforming the ajax call on some kind of button click. This needs to be changed for it to work (I used #update_button, you'll place the correct id or class of your button)

jQuery(document).ready(function($) {
    'use strict';

    $('#update_button').on('click', function(e){
        e.preventDefault();

        var ID = $(this).attr('id'); // Button that is clicked is in $(this) object
        var name = $('#name_input_'+ID).val();
        var create_date = $('#created_input_'+ID).val();
        var stats = $('#status_input_'+ID).val();
        var date_expire = $('#disbanded_input_'+ID).val();

        // Can place loading image here
        if ( name.length > 0 || create_date.length > 0 || stats.length > 0 || date_expire.length > 0 ) {
            $.ajax({
                type: 'POST',
                url: ajax_object.ajaxurl,
                data: {
                    'action' : 'edit_committee',
                    'id' : ID,
                    'name' : name,
                    'create_date' : create_date,
                    'stats' : stats,
                    'date_expire' : date_expire,
                    'committee_nonce' : $( '#committee_nonce' ).val()
                },
                cache: false,
                error: function( jqXHR, textStatus, errorThrown ) {
                    console.log( jqXHR + ' :: ' + textStatus + ' :: ' + errorThrown );
                },
                success: function (html) {
                    if ( html == 'success' ) {
                        $('#name_'+ID).html(name);
                        $('#createDate_'+ID).html(create_date);
                        $('#stats_'+ID).html(stats);
                        $('#dateExpire_'+ID).html(date_expire);
                    }
                }
            });
        } else {
            alert('Enter text to make an edit.');
        }

    });

});

I've also changed variable names to short_name format, instead of shortName. I find that neater.

Notice that for the url, we used ajax_object.ajaxurl - this is the localized ajax_object from the beginning, referencing to the correct path of the admin-ajax.php file. The data is just your string, but written as an object.

So when you click the button, your ajax call will put all your data in a global $_POST variable.

You can check it by having inspector running and clicking Network > XHR tab

Also if you're not sure what your $_POST variable holds, put

error_log(print_r($_POST, true));

in your edit_committee() function, right before the validation check (the if condition). If you have debugging enabled in your wp-config.php

define('WP_DEBUG', true);
ini_set('log_errors',TRUE);
ini_set('error_reporting', E_ALL);
ini_set('error_log', dirname(__FILE__) . '/error_log.txt');

You'll have error_log.txt in the root of your WordPress installation, and you can check what the contents of the $_POST variable is.

Hope this helps ;)

Answer 3

Here is the issue, for ajax requests we use http://example.com/wp-admin/admin-ajax.php.
Also your data string should cantain &action=editItems ( same as you use in wp_ajax_editItems )

You ajax request code should look like...

  $.ajax({
    type: "POST",
    url: "/wp-admin/admin-ajax.php",
    data: dataString + '&action=editItems',
    cache: false,
    error: function(xhr, status, error) {
      var err = eval("(" + xhr.responseText + ")");
      alert(err.Message);
    },
    success: function (html) {
      $("#name_"+ID).html(name);
      $("#createDate_"+ID).html(createDate);
      $("#stats_"+ID).html(stats);
      $("#dateExpire_"+ID).html(dateExpire);
    }
  });

For more details check out https://codex.wordpress.org/AJAX_in_Plugins

Hope that helps :)

Answer 4

I'm quite sure that you didn't send POST data to PHP file - check it. Type var_dump($_POST) in php, and add console.log(html) in success part of ajax.

Try also change your dataString variable in JS to this:

var dataString = {
 id:ID,
 name:name,
 createDate:createDate,
 stats:stats,
 dateExpire:dateExpire
};