deleting mysql records with ajax

Question

I would like to know the best way to delete records from a live database and refresh the page instantly. At the moment I am using ajax, with the following javascript method:

function deleterec(layer, pk) {
   url = "get_records.php?cmd=deleterec&pk="+pk+"&sid="+Math.random();
   update('Layer2', url);
}

if cmd=deleterec on the php page, a delete is done where the primary key = pk. This works fine as in the record is deleted, however the page is not updated.

My update method is pretty simple:

function update(layer, url) {
    var xmlHttp=GetXmlHttpObject(); //you have this defined elsewhere

    if(xmlHttp==null) {
        alert("Your browser is not supported?");
    }

    xmlHttp.onreadystatechange = function() {
        if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
            document.getElementById(layer).innerHTML=xmlHttp.responseText;
        } else if (xmlHttp.readyState==1 || xmlHttp.readyState=="loading") {
            document.getElementById(layer).innerHTML="loading";
        }

       //etc
    }

    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
}

how to delete or alter record, and upate the page.

At the moment my ajax framework works by passing data to a javascript update method, which works fine for selecting different queries to display in different layers.

I want to add the functionality to delete, or alter the records in a certain way.

I am wondering if it is possible when clicking a link to execute a query and then call my update method and refesh tge page. Is there any easy way to do this given my update methods?

I would like to avoid rewriting my update method if possible.

WOuld the simplest method be to have the php page(only in the layer) reload itself after executing a mysql query?

Or to make a new "alterstatus" method, which would pass delete or watch as a paramter, and have the php execute a query accordingly and then update the page?

edit: The links are generated like so. deleterec would be called from an additional link generated.

{

$pk = $row['ARTICLE_NO'];

echo '<tr>' . "\n"; 

 echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n"; 

echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n"; 

echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['ARTICLE_NAME'].'</a></td>' . "\n"; 


echo '<td><a href="#" onclick="deleteRec(\'Layer2\', \'' . $pk . '\')">'.$row['ARTICLE_NAME'].'</a></td>' . "\n"; 

echo '</tr>' . "\n";

}

edit: the update method can not be modified, as it is used by the updateByPk and updateBypg methods which need a layer.

Answer 1

Without digging too much into your code specifics, I don't know of any way to update/delete from the server side DB without doing a round trip (either AJAX or a page navigation). I would however recommend using a JavaScript framework (like jQuery, or something else) to handle the AJAX and DOM manipulations. That should, in theory, alleviate any cross-browser troubleshooting on the client side of thinbs.

Answer 2

You could create a callback that at the client side takes care of updating the screen. You can do that within your XHR function.

    function update(layer, url) {
        var xmlHttp=GetXmlHttpObject(),
             callbackFn = function(){ 
                  /* ... do thinks to reflect the update on the user screen,
                         e.g. remove a row from a table ...*/
                 };

        if(xmlHttp==null) {
            alert("Your browser is not supported?");
        }

        xmlHttp.onreadystatechange = function() {
            if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
                /* if the server returns no errors run callback 
                   (so server should send something like 'ok' on successfull
                   deletion 
                */
                if (xmlHttp.responseText === 'ok') {
                      callback();
                }
        //=>[...rest of code omitted]

Answer 3

You have two choice:

• Do a complete round trip, ie don't update the UI until you know the item has been successfully deleted, OR

• Lie to your users

If the results of the operation are questionable and important, the use the first option. If you're confident of the result, and people don't need to know the details, use the second.

Really, nothing keeps people happy so much as being successfully lied to.

Answer 4

I am wondering if it is possible when clicking a link to execute a query and then call my update method and refesh tge page. Is there any easy way to do this given my update methods?

So, why don't you just submit a form?

Answer 5

When you say "update instantly" I presume you mean update the Document via Javascript. Ajax and page refreshes don't go together.

How are you displaying your existing rows of data? Say for example you were listing them like this:

<div id="row1">row 1</div>
<div id="row2">row 2</div>

Where row1 and row2 are rows in your database with primary keys 1 & 2 respectively. Use a simple javascript function to remove the associated div from the DOM:

function deleterec(pk) {
    url = "get_records.php?cmd=deleterec&pk="+pk+"&sid="+Math.random();
    update(pk, url);
}

function update(pk, url) {
    var xmlHttp=GetXmlHttpObject(); //you have this defined elsewhere

    if(xmlHttp==null) {
        alert("Your browser is not supported?");
    }

    xmlHttp.onreadystatechange = function() {
        if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
            document.getElementById(layer).innerHTML=xmlHttp.responseText;
            removeDomRow(pk); //You may wish to check the response here
        } else if (xmlHttp.readyState==1 || xmlHttp.readyState=="loading") {
            document.getElementById(layer).innerHTML="loading";
        }
    }
    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
}

And the following function to manipulate the DOM:

function removeDomRow(pk){
        var row = document.getElementById('row' + pk);
        row.parentNode.removeChild(row);
}

If you're using tables:

<tr id="row1">
    <td>row 1</td>
</tr>
<tr id="row2">
    <td>row 2</td>
</tr>

You could use:

 function removeDomRow( id ) { // delete table row
    var tr = document.getElementById( 'row' + id );
    if ( tr ) {
      if ( tr.nodeName == 'TR' ) {
        var tbl = tr; // Look up the hierarchy for TABLE
        while ( tbl != document && tbl.nodeName != 'TABLE' ) {
          tbl = tbl.parentNode;
        }
        if ( tbl && tbl.nodeName == 'TABLE' ) {
          while ( tr.hasChildNodes() ) {
            tr.removeChild( tr.lastChild );
          }
          tr.parentNode.removeChild( tr );
        }
      }
    }

In respect to theraccoonbear's point, if you were to make use of a framework such as Qjuery things would be far easier:

$('#row'+id).remove();

Answer 6

I would not use a HTTP GET method to delete records from the database, I would use POST. And I would not use Ajax since the interaction you are looking for is clearly synchronous : delete then update. I would use a regular submit (either JS or HTML).

That said, the only remaining solution if you are really committed to use XHR is a callback based on response from the server like suggested by Renzo Kooi.

Answer 7

To Delete and update DOM:

echo '<td><a href="#" onclick="deleteRec(this, \'' . $pk . '\')">'.$row['ARTICLE_NAME'].'</a></td>' . "\n"; 

function deleterec(row, pk) {
    var rowId = row.parentNode.parentNode.rowIndex;
    url = "get_records.php?cmd=deleterec&pk="+pk+"&sid="+Math.random();
    update(rowId, url);
}

function update(rowId, url) {
    var xmlHttp=GetXmlHttpObject(); //you have this defined elsewhere

    if(xmlHttp==null) {
        alert("Your browser is not supported?");
    }

    xmlHttp.onreadystatechange = function() {
        if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
            document.getElementById(layer).innerHTML=xmlHttp.responseText;
            deleteRow(rowId); //You may wish to check the response here
        } else if (xmlHttp.readyState==1 || xmlHttp.readyState=="loading") {
            document.getElementById(layer).innerHTML="loading";
        }
    }
    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
}


function deleteRow(i){
    document.getElementById('myTable').deleteRow(i)
}

Answer 8

I have found that there are three basic operations that one performs with an Ajax based administration page, update, delete and append. Each of these actions changes the DOM in inherently different ways.

You've written a function that can update an existing div in the DOM, but this function won't work well if you want to remove a div from the DOM like you do in this question nor will it work well when you decide that you want to add new records using Ajax.

In order to handle this correctly, you first need to assign an unique id to each row that you output:

$pk = $row['ARTICLE_NO'];

echo '<tr id=\"article_' . $pk . '\">' . "\n"; 

        echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['USERNAME'].'</a></td>' . "\n"; 

echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['shortDate'].'</a></td>' . "\n"; 

echo '<td><a href="#" onclick="updateByPk(\'Layer2\', \'' . $pk . '\')">'.$row['ARTICLE_NAME'].'</a></td>' . "\n"; 


echo '<td><a href="#" onclick="deleteRec(\'article_' . $pk .'\', )">'.$row['ARTICLE_NAME'].'</a></td>' . "\n"; 

echo '</tr>' . "\n";

And then you need to create a delete function that can remove the table row:

function delete(layer, url) {
    var xmlHttp=GetXmlHttpObject(); //you have this defined elsewhere

    if(xmlHttp==null) {
        alert("Your browser is not supported?");
    }

    xmlHttp.onreadystatechange = function() {
        if(xmlHttp.readyState==4 || xmlHttp.readyState=="complete") {
      if(xmlHttp.responseText == 'result=true') {
       // Here you remove the appropriate element from the DOM rather than trying to update something within the DOM
       var row = document.getElementById(layer);
       row.parentNode.removeChild(row);
      }
        } else if (xmlHttp.readyState==1 || xmlHttp.readyState=="loading") {
            document.getElementById(layer).innerHTML="loading";
        }

       //etc
    }

    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
}

And then lastly adjust your deleterec function:

function deleteRec(layer, pk) {
   url = "get_records.php?cmd=deleterec&pk="+pk+"&sid="+Math.random();
   delete(layer, url);
}

As a final note I have to echo the sentiments of others that have suggested the usage of a framework. The usage of any framework be it jQuery, Prototype, Dojo or other, is going to have both short term and long term benefits. Additionally, I would NEVER actually use GET to perform an operation of this nature. All that one has to do to force the deletion of an element is hit the appropriate URL and pass in the relevant article number.

Answer 9

I would have voted up one of the other answers that recommended jQuery, but I don't have enough points yet.

I think the easiest way to achieve the "update" you're looking for is to either have your AJAX delete return the relevant post-delete HTML, or you could use jQuery to fire off the delete and then delete the tr, div, etc. from the page.

jQuery.post("get_records.php ", { cmd: "delete", pk: 123 }, function() {
    jQuery("tr.row123").remove(); 
})