2

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 .

4
  • Any error coming? Commented Jan 12, 2018 at 7:39
  • 2
    the function delete has no idea what $id is - either pass it as a parameter or declare as global inside the function. That said this is vulnerable to sql injection Commented Jan 12, 2018 at 7:39
  • trying both way but doesn't work. i think button click event is not pressed or not work Commented Jan 12, 2018 at 8:26
  • @AlivetoDie can you please explain how to submit form inside the while loop. i am creating button in while loop so how can i submit button inside the WHILE LOOP. pls help Commented Jan 12, 2018 at 8:28

2 Answers 2

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>';
?>
Sign up to request clarification or add additional context in comments.

4 Comments

trying both way but doesn't work. i think button click click event is not pressed
do you have a form ( can't see one in the above )? You need to ensure that the variable $id is defined before trying to use the delete function which requires this variable
Additionally - there was an error in the html that was generated - the closing </tr> tag needs to be inside the loop other wise you will get an ungodly mess in the HTML and it would definitely not work as expected
there are errors in the answer above ( the accepted one ) so you will need to edit appropriately
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

3 Comments

@aasifghanchi glad to help you. I have added note and reference section. Please read and try to change code accordingly to make it free from SQL INJECTION
when i run code without conn.php file in function this give error. so why add this file inside function and working fine.
@aasifghanchi please check my updated answer once again as i have some incorrect HTML structure there. It's corrected now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.