PHP, JavaScript, and AJAX & updating a SQL database without page refresh

Sorry this is so long. I’m trying to be thorough…

I’ve looked over a lot of forum posts and tutorials, and I’ve gotten the first part of my equation to work, but I’m stuck here and I’m not sure how to move forward. My initial setup with this project was a single page that took user input, passed it into SQL, and output an HTML table based on the input, (e.g. - which user they selected…)

I wanted to modify this so that the requests could be sent without refreshing the page, and I was pointed to AJAX by the super friendly stackoverflow community. A few weeks later, I’m at the point where I have successfully built this into my project, but I am stuck now trying to figure out how to utilize AJAX multiple times.

My initial page, test.php, starts out with this select:

                $PM = mysqli_query($con, "SELECT DISTINCT PMName FROM report WHERE PMname <> '' ORDER BY PMName ASC");
                <span class="custom-dropdown custom-dropdown--red">
                <select class="navbar-inverse" placeholder="PM Name" name="PMName" onchange="showUser(this.value)">
                while ($row = mysqli_fetch_row($PM)) {
                $selected = array_key_exists('PMName', $_POST) && $_POST['PMName'] == $row[0] ? ' selected' : '';
                printf(" <option value='%s' %s>%s</option>\n", $row[0], $selected, $row[0]);

Then I have a JavaScript function that the response from this dropdown passes to:

    function showUser(str) {
      if (str !==".PM") {
        if (str=="") {
        if (window.XMLHttpRequest) {
          // code for IE7+, Firefox, Chrome, Opera, Safari
          xmlhttp=new XMLHttpRequest();
        } else { // code for IE6, IE5
          xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
        xmlhttp.onreadystatechange=function() {
          if (xmlhttp.readyState==4 && xmlhttp.status==200) {

I then have a second page called getuser.php that contains the bulk of my code, (so I won’t post it here,) but it takes the variable (str) from the JavaScript function and populates it into this:

$q = $_GET['q'];
$sqlPM= "SELECT * FROM report WHERE PMName = '".$q."'";
$result     = mysqli_query($con, $sqlPM);

$result is then used to populate this:

while ($row = mysqli_fetch_array($result)) {
    $LineID             = $row['LineID'];
    $trackNumber        = $row['TrackNumber'];
    $PMName             = $row['PMName'];    

which in turn populates my big HTML table that is displayed to the user. Prior to re-writing this utilizing AJAX, the user could input data into various fields in the HTML table that was generated, then hit a submit button:

<form action="" method="POST">
      <div class="container pull-right">
        <h2 class="pull-left">
          <input class="button" name="update"<?= $LineID ?>" type="submit" id="update" value="UPDATE" onclick="SubmitForm();">

which would POST whatever changed in the table and write it back to the database with this:

for ($n = 0, $t = count($_POST['PMComments']); $n < $t; $n++) {
    $UpdateValue             = $_POST['Update'][$n];
    $PMCommentsValue         = $_POST['PMComments'][$n];
    $PMMRecommendationsValue = $_POST['PMMRecommendations'][$n];
    $OutcomeValue            = $_POST['Outcome'][$n];
    $NewGPPercentValue       = $_POST['NewGPPercent'][$n];
    $AdditionalGPValue       = $_POST['AdditionalGP'][$n];
    $LineID                  = $_POST['LineID'][$n];
    $sqlUPDATE = "UPDATE report SET PMComments = '$PMCommentsValue' , PMMRecommendations = '$PMMRecommendationsValue' , Outcome = '$OutcomeValue' , NewGPPercent = '$NewGPPercentValue', AdditionalGP = '$AdditionalGPValue'  WHERE LineID = $LineID ;";
    $doUPDATE = mysqli_query($con, $sqlUPDATE);
    if (!$doUPDATE) {
        die('Could not update data: ' . mysqli_error($con));
    if ($OutcomeValue <> 'null') {                  
      $sqlMOVE  = "INSERT INTO results SELECT * FROM report WHERE LineID = $LineID ;" ;
      $sqlDELETE = "DELETE FROM report where LineID = $LineID ;" ;
      $doMOVE = mysqli_query($con, $sqlMOVE);
      if (!$doMOVE) 
          die('Could not MOVE data: ' . mysqli_error($con));
      $doDELETE = mysqli_query($con, $sqlDELETE);
      if (!$doDELETE) 
          die('Could not DELETE data: ' . mysqli_error($con));

The thing that I’m having a hard time with now is that this update button doesn’t work anymore. When I click it it shoots me back to the beginning of test.php and does not write to the database. This is only half the problem, as my main goal is to get this update statement to ALSO run without refreshing the main test.php page. I’m completely stuck on this…

Is there any error message being written to the console to indicate where the process is failing?

You might also consider using console.log() calls to write the values of variables at different points in the process to the console so that you can check that the values are what you expect them to be at those points in the script (that way you don’t have to set breakpoints and interrupt execution).

I’m viewing this on my phone, so forgive me if I missed something…

Can you post the javascript function “SubmitForm”? From your description (reloads test.php) I would guess that one of two things is happening.

  1. because you have action=" " the form is just posting to the same page. This is the default behavior which implies the SubmitForm function isn’t overriding it as you are expecting.
  2. the form tags aren’t properly closed (going solely by the snippets you posted) which is causing errors in your js that prevents the form from submitting the way you are expecting.