Using Ajax, JavaScript, PHP to delete database entries

Question

I am very new to web programming in general and I have some issues in creating a simple client database. I have to find a way to delete clients from the database using Ajax. I try to find a solution without using jQuery... So far I managed to set up a script and php code to delete one database entry; However it is not possible to delete another client without refreshing the page first. Could someone help me find out what to do to solve this problem?

Relevant parts from my index.php

<?php
    require_once "CRM_DB.php";
    $clients = CRM_DB::showAll();
?>

...

<div class="container">

    <div class="row mb-4">
    <div class="col-sm-12">
        <h2>All Entries</h2>
        <table class="table">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>E-Mail</th>
                    <th>Comment</th>
                    <th>Created At</th>
                    <th>Updated At</th>
                    <th>Delete</th>
                    <th>Edit</th>
                </tr>   
            </thead>
            <tbody>
                <? foreach($clients as $client): ?>
                    <tr id="table-row">
                        <td><?= $client->id ?></td>
                        <td><?= $client->name ?></td>
                        <td><?= $client->email ?></td>
                        <td><?= $client->text ?></td>
                        <td><?= $client->created_at ?></td>
                        <td><?= $client->updated_at ?></td>
                        <td>
                            <form action="" class="delete-form m-0" method="post">
                                <input type="hidden" id="del-id" name="id" value="<?= $client->id ?>">
                                <input type="hidden" name="delete" id="delete" value="1">
                                <button type="submit" class="btn btn-danger btn-sm" id="delete_btn" onclick="deleteID(); return false">Delete</button>
                            </form>
                    </tr>
                <? endforeach; ?>
            </tbody>
        </table>    

....

<script type="text/javascript">

    function deleteID() {

        var id = document.getElementById('del-id').value;
        var del = document.getElementById('delete').value;
        var params = 'delete='+encodeURIComponent(del)+'&id='+encodeURIComponent(id);
        var url = 'crm.php';

        xmlhttp = new XMLHttpRequest();

        xmlhttp.onload = function() {
            if(this.readyState == 4 && this.status == 200) {
                document.getElementById('table-row').innerHTML = this.response;
                    }
                };

        xmlhttp.open("POST", url, true);
        xmlhttp.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
        xmlhttp.send(params);
    }
</script>

crm.php

if(isset($_POST["delete"]) && isset($_POST["id"])) {
    $id = $_POST["id"];
    CRM_DB::deleteClient($id);
}

CRM_DB.php

    public static function deleteClient($id) {
        $db = self::db();

        $query = "DELETE FROM clients WHERE id = ?";
        $result = $db->query($query, [$id]);
        return $result;
    }

    public static function showAll() {
        $db = self::db();

        $query = "SELECT * FROM clients";
        $result = $db->query($query);

        $clientArray = [];

        for($i = 0; $i < $result->num_rows; $i++) {
            $row = $result->fetch_array();

            $client = self::convertToObject($row);
            $clientArray[] = $client;
        }
        return $clientArray;
    }

    public static function convertToObject($row) {
        return new Client($row["id"], $row["name"], $row["email"], $row["text"], $row["created_at"], $row["updated_at"]);
    }

}

use parent / sibling selectors to find the correct form elements rather than trying ( and failing ) to use ID attributes which must be unique — Professor Abronsius
– Professor Abronsius, Commented Jun 6, 2020 at 13:34

Chris · Accepted Answer · 2020-06-06 12:32:14Z

1

I think the problem might be, that you are assigning the same element ID for multiple elements at <input type="hidden" id="del-id" name="id" value="<?= $client->id ?>">. At first, this is not valid HTML. Element IDs have to be unique in HTML. Secondly, the function deleteID() doesn't know, which client should be deleted. It just picks the first (of many) HTML elements with the ID "del-id".

Solution: remove the hidden field "del-id" and pass the ID of the client as a function argument, e.g.

<button type="submit" class="btn btn-danger btn-sm" id="delete_btn" onclick="deleteID(<?= $client->id ?>); return false">Delete</button>

...

function deleteID(clientID) {
    var del = document.getElementById('delete').value;
    var params = 'delete='+encodeURIComponent(del)+'&id='+encodeURIComponent(clientID);
    var url = 'crm.php';
...
}

You also don't need to pass the parameter "delete", since the function context is already about deleting a client. You can hardcore this value.

Also: NEVER EVER trust user input. You should check, if the clientID is a valid integer and if the user has the permission to delete the client!

answered Jun 6, 2020 at 12:32

Chris

212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Si1701 Over a year ago

Thank you for your input! But unfortunately the problem still remains even if I do implement your changes :(

Professor Abronsius · Accepted Answer · 2020-06-07 06:57:46Z

ID attributes must be unique and will often cause issues like the one you faced here if not dealt with correctly. For the most part they have superior alternatives in my opinion - it is entirely feasible to traverse the DOM using parent/child/sibling types selectors which can be further enhanced with the use of document.querySelector and document.querySelectorAll to target specific nodes or collections of nodes.

If you modify the portion of HTML in the loop - assign a dataset attribute to the table row and remove the form as below - note the table row has it's ID changed to a class attribute so changes to the css might be needed. The FORM essentially does nothing as the input elements are hidden so the user cannot legitimately modify the values - so it could be removed as the request is being sent by ajax.

        <tbody>
            <? foreach($clients as $client): ?>
                <tr class="table-row" data-id='<?=$client->id;?>'>
                    <td><?= $client->id ?></td>
                    <td><?= $client->name ?></td>
                    <td><?= $client->email ?></td>
                    <td><?= $client->text ?></td>
                    <td><?= $client->created_at ?></td>
                    <td><?= $client->updated_at ?></td>
                    <td>
                        <button type="button" class="btn btn-danger btn-sm">Delete</button>
                    </td>
                </tr>
            <? endforeach; ?>
        </tbody>

You could then modify the javascript to remove the inline event handlers and create a a separate ajax function

<script>

    const url='crm.php';

    const callback=function(r,id){
        let tr=document.querySelector('tr[data-id="'+id+'"]');
        let l=tr.childNodes.length || 7;
            tr.innerHTML='';

        let td=document.createElement('td');
            td.setAttribute('colspan',l);
            td.textContent=r;

        tr.appendChild( td );
    };

    const ajax=function(url,params,callback){
        let query=Object.keys( params ).map( k=>{
            return [ k, params[k] ].join('=');
        }).join('&');

        let xmlhttp = new XMLHttpRequest();
            xmlhttp.onload = function() {
                if( this.readyState == 4 && this.status == 200 ) {
                    callback( this.response, id );
                }
            };
            xmlhttp.open( 'POST', url, true );
            xmlhttp.setRequestHeader( 'Content-Type', 'application/x-www-form-urlencoded' );
            xmlhttp.send( query );          
    };


    Array.from( document.querySelectorAll('tr.table-row > td > button.btn-danger') ).forEach( bttn=>{
        bttn.addEventListener('click',function(e){
            /* create the payload for this row */
            let params={
                'id':this.parentNode.parentNode.dataset.id,
                'delete':true
            };

            /* call the ajax function with collected parameters */
            ajax( url, params, callback )
        });
    });
</script>

update:

to remove the row from which the button was clicked ( and not display any feedback etc ) you could try:

const callback=function(r,id){
    let tr=document.querySelector('tr[data-id="'+id+'"]');
    tr.parentNode.removeChild( tr );
};

updated

In light of the above being untested, the need to remove the table row from the display on deletion and the issues remaining I put together a mockup of the above with a slight modification to the javascript. It doesn't show the php record selection/query but otherwise is fully functional.

The ajax function takes a fourth argument - a reference to the table row itself on which the button was clicked. Passing this argument makes it easy to delete the row rather than rely upon using the param.id with querySelector as a lookup mechanism.

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['id'], $_POST['task'] ) ){
        ob_clean();

        /* pseudo sql only for illustration */
        switch( $_POST['task'] ){
            case 'edit':
                $sql=sprintf('update `clients` set `name`=?, `email`=? where `id`="%d"', intval( $_POST['id'] ) );
            break;
            case 'delete':
                $sql=sprintf('delete from `clients` where `id`="%d"', intval( $_POST['id'] ) );
            break;
        }

        exit( $sql );
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title>CRM Contact Management</title>
        <script>
            document.addEventListener('DOMContentLoaded', function(e){
                const url=location.href;    //'crm.php';



                const ajax=function(url,params,callback,row){
                    let query=Object.keys( params ).map( k=>{
                        return [ k, params[k] ].join('=');
                    }).join('&');

                    let xmlhttp = new XMLHttpRequest();
                        xmlhttp.onload = function() {
                            if( this.readyState == 4 && this.status == 200 ) {
                                callback( this.response, row );
                            }
                        };
                        xmlhttp.open( 'POST', url, true );
                        xmlhttp.setRequestHeader( 'Content-Type', 'application/x-www-form-urlencoded' );
                        xmlhttp.send( query );          
                };



                Array.from( document.querySelectorAll('tr.table-row > td > button.btn-danger') ).forEach( bttn=>{
                    bttn.addEventListener('click',function(e){
                        console.info( this, e, this.dataset.task );

                        const callback=function(response,row){
                            console.info( response );
                            row.parentNode.removeChild( row )
                        };

                        /* "this" refers to the button, so it's grandparent is the table row */
                        let tr=this.parentNode.parentNode;

                        /* create the payload for this row */
                        let params={
                            'id':tr.dataset.id,
                            'task':this.dataset.task
                        };

                        /* call the ajax function with collected parameters */
                        ajax( url, params, callback, tr );
                    });
                });





                Array.from( document.querySelectorAll('tr.table-row > td > button.btn-edit') ).forEach( bttn=>{
                    bttn.addEventListener('click',function(e){
                        console.info( this, e, this.dataset.task );

                        const callback=function( response,row ){
                            let l=row.childNodes.length || 7;
                            row.innerHTML='';

                            let td=document.createElement('td');
                                td.setAttribute('colspan',l);
                                td.textContent=response;

                            row.appendChild( td );
                        };

                        /* "this" refers to the button, so it's grandparent is the table row */
                        let tr=this.parentNode.parentNode;

                        /* create the payload for this row */
                        let params={
                            'id':tr.dataset.id,
                            'task':this.dataset.task
                        };

                        /* call the ajax function with collected parameters */
                        ajax( url, params, callback, tr );
                    });
                });

            });
        </script>
    </head>
    <body>

        <div class="container">
            <div class="row mb-4">
                <div class="col-sm-12">
                    <h2>All Entries</h2>
                    <table class="table">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Name</th>
                                <th>E-Mail</th>
                                <th>Comment</th>
                                <th>Created At</th>
                                <th>Updated At</th>
                                <th>Delete</th>
                                <th>Edit</th>
                            </tr>   
                        </thead>
                        <tbody>
                        <?php
                            while( $rs=$res->fetch_object() ){
                                printf(
                                    '
                                    <tr class="table-row" data-id="%d">
                                        <td>%d</td>
                                        <td>%s</td>
                                        <td>%s</td>
                                        <td>%s</td>
                                        <td>%s</td>
                                        <td>%s</td>
                                        <td>
                                            <button data-task="delete" type="button" class="btn btn-danger btn-del btn-sm">Delete</button>
                                        </td>
                                        <td>
                                            <button data-task="edit" type="button" class="btn btn-edit btn-sm">Edit</button>
                                        </td>
                                    </tr>',
                                    $rs->id,
                                    $rs->id,
                                    $rs->name,
                                    $rs->email,
                                    $rs->text,
                                    $rs->created_at,
                                    $rs->updated_at
                                );
                            }
                        ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

    </body>
</html>

Thank you! Deleting the client from the database works now. But I still need the table to refresh after deleting a row. When I press the delete button nothing seems to happen, but after refreshing the page you see that it actually deleted the client from the database. But i would need just the table to refresh after deleting a client without refreshing the whole page. I hope that wasn't too confusing....
I must have missed that requirement from the question - I do tend to skip-read ( aka: tldr ) see edit for how to delete row
Hm Thanks! But it did not work. Still showing the row after I clicked on the button :(
any errors in the console? None of the above was tested so there could well be errors or I may have missed some piece of the puzzle?!
Yes I checked the console: Uncaught ReferenceError: id is not defined at XMLHttpRequest.xmlhttp.onload; This happens when calling the function callback callback( this.response, id );

Collectives™ on Stack Overflow

Using Ajax, JavaScript, PHP to delete database entries

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related