Javascript not pulling correct data from MySQL after using XMLHttp to update the DB

Question

The newNet.php runs and correctly creates the new entry. The netID variable is an auto increment so it is created automatically. My goal is to then retrieve it and use it in the showActivities() function to display the record just created. For example it should resolve like this; showActivities(55);

The problem is the SQL always returns the previous value of netID, 54 instead of 55; If I say echo $result + 1; Then, according to page source it shows the correct number is being resolved in the showActivities function but the function can't find and return the data. But looking at the DB it has successfully been inserted.

So step by step:

1. newNet.php runs, it adds a record to a MySQL db
2. get the netID value just created in #1
3. pass it to showActivities() which displays it on the page.

How can I get what I want here? It would seem that the DB does not get updated fast enough to accommodate the request from showActivities(), is this possible?

 function newNet(str) {
   str = str;
     var xmlhttp = new XMLHttpRequest();
     xmlhttp.onreadystatechange = function() {
       if (xmlhttp.readyState === 4 && xmlhttp.status === 200) {
         document.getElementById("status").innerHTML = xmlhttp.responseText;
     }}

     xmlhttp.open("POST", "newNet.php", true);
     xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
     xmlhttp.send("q="+str);

     showActivities(<?php $stmt=$db_found->prepare("select max(netID) as netID from netLog limit 1");
       $stmt->execute();
       $result=$stmt->fetchColumn();
       echo $result;
     ?>);
 }

Answer 1

Two problems here:

1. As I said in the comment your problem has to do with the asynchronous nature of AJAX. The request actually needs time to process. But your code will immediately start executing showActivities, so no results are back from the request, because that is still "baking".

2. Your PHP code is rendered upon page load. So the old value will be loaded from the database. On page load it will be 54. You can fire that Ajax request ten times and netID will still be 54 since PHP is run server side and not client side.

How to fix this:

You have a xmlhttp.onreadystatechange function which listens to the request and fires everytime a step is done. This is called a callback function. At step 4 the pizza is done and 200 means it isn't burned and actually looks good. When the status is like this data is returned and can be accessed.

 var xmlhttp = new XMLHttpRequest();
 xmlhttp.onreadystatechange = function() {
   if (xmlhttp.readyState === 4 && xmlhttp.status === 200) {
     document.getElementById("status").innerHTML = xmlhttp.responseText;
     //execute all relevant code that needs data from server from here.
     showActivities(xmlhttp.responseText);
 }}

In newNet.php you should execute the PHP code that is now in the arguments bit of showActivities(). Execute this after the script has done its inserts to the database.

   $stmt=$db_found->prepare("select max(netID) as netID from netLog limit 1");
   $stmt->execute();
   $result=$stmt->fetchColumn();
   echo json_encode($result);

You can json_encode the result. This way it is printed as JSON to the page. We can load this variable into a JavaScript variable.

function showActivities(data)
{
   //remember data is a JSON string, time to convert it into JavaScript
   var netID = JSON.parse(data);
   alert(netID); //should show the highest netID
}

Learn more:

• JSON
• Asynchronous calls for the pizza reference and a more elaborate approach here
• XMLHttpRequest

To sum up

• PHP is always ran before the page is loaded and cannot be ran again without reloading the page. For that XMLHttpRequest was invented.
• XMLHttpRequest takes time to complete, a callback must be used to process its results.