title: jQuery/AJAX retrieving ID of newly inserted record and using this in HTML before page refresh
body: <p>Using jQuery/AJAX, I'm trying to
  a) update an existing record in an HTML table consisting of $firstname, $lastname, and an auto-incrementing $user_id and
  b) return the new database entry along with its ID number and display them in the HTML without a page refresh.</p>
  
  <p>The plan is to use jQuery .on("click"...) to get the data in the two name fields and fire the php file add-user.inc.php to insert these, leaving the value of $user_id as NULL, so it auto-increments:</p>
  
  <pre><code>$(document).on("click", ".insert", function(){
      var firstname = $('input.newdata[name="firstname"]').val();
      var lastname = $('input.newdata[name="lastname"]').val();
      $.ajax({ // begin insert ajax
          url: "php-includes/add-user.inc.php",
          type: "GET",
          data: { // begin insert data
              'firstname': firstname,
              'lastname': lastname
          }, // end insert data            
      }); // end insert ajax
  // closing }); comes later
  </code></pre>
  
  <p>Then, within the click event, so that the browser can identify the newly inserted row correctly for further data manipulation without a page refresh, to use another AJAX call to retrieve the id value of this newly inserted record:</p>
  
  <pre><code>    $.ajax({ // begin retrieve ajax
          url: "php-includes/retrieve-user_id.inc.php",
          data: "",
          datatype: "application/json",
          type: 'get',
          success: function(data) {
             var user_id = data;
  
              // declare var newrecord which is a table row containing
              // &lt;tr&gt; with the variable user_id as its ID
              // and &lt;td&gt;s containing inputs containing the newly inserted data
  
             $('table tr:last').before(newrecord); // .. and insert this into the table
          },  // end retrieve data
      }); // end retrieve ajax
  });
  </code></pre>
  
  <p><strong>add-user.inc.php:</strong></p>
  
  <pre><code>// connect to db
  
  $firstname = $_GET['firstname'];
  $lastname = $_GET['lastname'];
  
  $insert_user_sql = "INSERT INTO `table` (user_id, firstname, lastname)
      VALUES (NULL,'$firstname','$lastname')";
  
  $result = $db-&gt;query($insert_user_sql);
  </code></pre>
  
  <p><strong>retrieve-user_id.inc.php</strong></p>
  
  <pre><code>// connect to db
  
  $retrieve_user_sql = "SELECT `user_id` from `table` ORDER BY `user_id` DESC LIMIT 1";
  $retrieve_user_result = $db-&gt;query($retrieve_user_sql);
  $retrieve_user_num_rows= $retrieve_user_result-&gt;num_rows;
  
  
  if ($retrieve_user_num_rows &gt; 0) { 
      while ($row = $retrieve_user_result-&gt;fetch_object()) {
          $user_id = $row-&gt;user_id;
          $result = ($user_id + 1);
      }
  } else {
      $result = 1;
  }
  
   echo $result;
  </code></pre>
  
  <p>The user_id number inserted into the HTML is intermittently wrong, sometimes incrementing by 2 instead of 1, sometimes not incrementing, giving two table rows with the same ID until the page is refreshed and the correct ID number is retreived through pure PHP. This causes problems if several records are entered with no page refresh being done in between the inserts, as a single ID can be targetting more than one row.</p>
  
  <p>There are no problems if a page refresh is performed between each insert.</p>
  
  <p>Any ideas? Thanks in advance.</p>