Try to delete entire row from table using delete button in PHP

Question

i am trying to delete entire row in table from database using function, i write delete query in function. but delete button is not working. how can do it

this is my code

    <?php
include 'conn.php';
$result = ("SELECT * FROM usrdata");
$count = mysqli_query($conn, $result);

echo'<table border=1px>';  // opening table tag
echo'<th>id</th><th>email</th><th>Password</th>';

while ($data = mysqli_fetch_array($count)) {
// we are running a while loop to print all the rows in a table
    echo'<tr>'; // printing table row
    echo '<td>' . $data['id'] . '</td><td>' . $data['email'] . '</td>  <td>' . $data['password'] . '</td>';
    echo '<td><input type="button" name="delete" value="delete"></td>';
}

echo'</tr>'; // closing table row
echo '</table>';

if ($_GET) {
    if (isset($_GET['delete'])) {
        delete();
    }
}

function delete() {
    $delete1 = ("DELETE FROM `usrdata` WHERE id = '$id'");
    $result = mysqli_query($conn, $delete1) or die(mysqli_error());

    echo "record deleted";
}
?>

Thanks in advance .

Answer 1

1.You need to have a form with id of record and submit button to submit something.

2.You need to pass id as well as db comnnection object to delete function.

You have to do it like below:-

<?php
    include 'conn.php';
    $result = ("SELECT * FROM usrdata"); 
    $count=mysqli_query($conn,$result);

    echo'<table border=1px>';
    echo'<tr><th>id</th><th>email</th><th>Password</th><th>Action</th></tr>'; 

    while($data = mysqli_fetch_array($count)){
        echo'<tr>';
        echo '<td>'.$data['id'].'</td><td>'.$data['email'].'</td>  <td>'.$data['password'].'</td>'; 
        echo '<td><form method="post"><input type="hidden" name = "id" value ="'.$data['id'].'"><input type="submit" name="delete" value="delete"></form></td>';//add form with id hidden field and submit button
        echo'</tr>';
    }


    echo '</table>';
?>
<?php
    if(isset($_POST['delete'])){
        delete($_POST['id']); //pass the id to function
    }

    function delete($id,$conn)
    { // function with id as a parameter
        $delete1 =("DELETE FROM `usrdata` WHERE id = '$id'");
        $result = mysqli_query($conn,$delete1) or die(mysqli_error($conn));

        echo "record deleted";

    }
?>

Note:- Your code is wide-open for SQL INJECTION. to prevent from it use prepared statements

Reference:-

mysqli prepared statements

PDO prepared statements

Answer 2

To illustrate the comment above - the function does not know what the variable $id is as it is not defined within the function. So you can either assign as a parameter. Additionally, which I didn't immediately spot, was that the delete function similarly requires the $conn variable to be defined either as a parameter or as a global within the function itself.

The generated HTML was incorrect - the closing table row tag NEEDS to be within the loop and the th sections should be within a table row.

function delete($conn,$id){
    $delete1 =("DELETE FROM `usrdata` WHERE id = '$id'");
    $result = mysqli_query($conn,$delete1) or die(mysqli_error());
    echo "record deleted";
}

or declare as global within the function

function delete(){
    global $id;
    global $conn;
    $delete1 =("DELETE FROM `usrdata` WHERE id = '$id'");
    $result = mysqli_query($conn,$delete1) or die(mysqli_error());
    echo "record deleted";
}

The variable needs to be defined first, a hidden field in the form with the ID or some other means to define the variable.

Better yet would be to use a prepared statement in conjunction with the above so

function delete( $conn=false, $id=false ){
    if( $conn && $id ){
        $sql='delete from `usrdata` where id = ?';
        $stmt=$conn->prepare( $sql );
        if( $stmt ){
            $stmt->bind_param('i',$id);
            $result=$stmt->execute();
            $stmt->close();
            echo $result ? 'record deleted' : 'error';
        }
    }
}

---

As a possible solution, hastily written so excuse errors, you could perhaps do something like this:

The table is wholly contained within a form ( delrecord ) with a single hidden field id which is assigned a value by some javascript using the buttons dataset attribute data-id

<?php

    include 'conn.php';
    $result = ("SELECT * FROM usrdata"); 
    $count=mysqli_query( $conn, $result );


    if( $_SERVER['REQUEST_METHOD']=='POST' && !empty( $_POST['id'] ) ){

        function delete( $conn=false, $id=false ){
            if( $conn && $id ){
                $sql='delete from `usrdata` where id = ?';
                $stmt=$conn->prepare( $sql );
                if( $stmt ){
                    $stmt->bind_param('i',$id);
                    $result=$stmt->execute();
                    $stmt->close();
                    echo $result ? 'record deleted' : 'error';
                }
            }
        }

        $id=filter_input( INPUT_POST, 'id', FILTER_SANITIZE_NUMBER_INT );
        call_user_func( 'delete', $conn, $id );
    }


    echo'
    <form name=\'delrecord\' method=\'post\'>

        <input type=\'hidden\' id=\'h_id\' name=\'id\' />

        <table border=1px>
            <tr>
                <th>id</th>
                <th>email</th>
                <th>Password</th>
                <th>&nbsp;</th>
            </tr>';

    while( $data = mysqli_fetch_array( $count ) ) {
        echo"
            <tr>
                <td>{$data['id']}</td>
                <td>{$data['email']}</td>
                <td>{$data['password']}</td>
                <td><input data-id='{$data['id']}' type='button' name='delete' value='delete'></td>
            </tr>";
    }

    echo '
        </table>
    </form>

    <script>
        var col=Array.prototype.slice.call( document.querySelectorAll("input[type=\'button\'][name=\'delete\']") );
            col.forEach(function(bttn){
                bttn.onclick=function(event){
                    document.getElementById("h_id").value=this.dataset.id;
                    document.forms.delrecord.submit();
                }.bind(bttn)
            })
    </script>';
?>