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"]);
    }

}

Answer 1

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>

Answer 2

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!