php mysql update database on button click

Question

as the title states I am trying to write a code that will update a boolean data in column (I called 'status') for a specific row. I used while loop in table to display the rows of new registered and where the status is NULL, I've put two buttons (accept, reject) each in td so they'll be displayed to each name, What I want is when the accept button clicked, it sets the status of its row in the table to 1, and when reject is clicked, same thing but sets 0 instead of 1.

I've did a lot of research over this but hit a road block after road block, so I really hope your help in this, many thanks!

Here is my code:

<table id="sHold" style="border:none;">

<?php
    $conn = mysqli_connect('localhost', 'root', '', 'srs-db') or die('ERROR: Cannot Connect='.mysql_error($conn));

    function getStudent () {
        global $conn;
        $query = "SELECT * FROM student_table WHERE status IS NULL;";
        $result = mysqli_query($conn, $query);

        $i = 1;

        while ($row = mysqli_fetch_array($result)) {
            $sId = $row['student_id'];
            $sName = $row['student_name'];

            echo "<tr id='sNew".$i."'>";
            echo "<td>".$i." - </td>";
            echo "<td>$sId</td>";
            echo "<td>$sName</td>";
            echo "<td><button name='sAcc".$i."'>Accept</button></td>";
            echo "<td><button name='sRej".$i."'>Reject</button></td>";
            echo "</tr>";

            $i++;
        }
        if (isset($_POST['sAcc'.$i])) {
            $row['status'] = 1;
        }
    }

    getStudent();

?>

</table>

Answer 1

First of all, you miss <form> element. Your form inputs are useless without it, or without ajax.

Secondly, your $_POST check will only check last item. Since after you exit loop $i is set to last value in the loop. So your example will only work on last item.

<button> will now send $_POST with one of indexes sAcc or sRej. And it's value will be ID of your entry.

<table id="sHold" style="border:none;">
<form method="post" action="">
<?php
    $conn = mysqli_connect('localhost', 'root', '', 'srs-db') or die('ERROR: Cannot Connect='.mysql_error($conn));

    function getStudent () {
        global $conn;
        $query = "SELECT * FROM student_table WHERE status IS NULL;";
        $result = mysqli_query($conn, $query);

        $i = 1;

        while ($row = mysqli_fetch_array($result)) {
            $sId = $row['student_id'];
            $sName = $row['student_name'];

            echo "<tr id='sNew".$i."'>";
            echo "<td>".$i." - </td>";
            echo "<td>{$sId}</td>";
            echo "<td>{$sName}</td>";
            echo "<td><button type='submit' name='sAcc' value='{$sId}'>Accept</button></td>";
            echo "<td><button type='submit' name='sRej' value='{$sId}'>Reject</button></td>";
            echo "</tr>";

            $i++;
        }
    }

    if (isset($_POST['sAcc']) && intval($_POST['sAcc'])) {
        $user_id = (int) $_POST['sAcc'];

        // Do the database update code to set Accept
    }
    if (isset($_POST['sRej']) && intval($_POST['sRej'])) {
        $user_id = (int) $_POST['sRej'];


        // Do the database update code to set Reject
    }



    getStudent();

?>
</form> 
</table>

Tip: I assume you're beginner. I remade your code. But you dont need to put this code into function. Use functions to handle data retrieval for example. Dont use it to display html.

Answer 2

<table id="sHold" style="border:none;">

<?php
    $conn = mysqli_connect('localhost', 'root', '', 'srs-db') or die('ERROR: Cannot Connect='.mysql_error($conn));

    function getStudent () {
        global $conn;
        $query = "SELECT * FROM student_table where status='NULL'";
        $result = mysqli_query($conn, $query);

        $i = 1;

        while ($row = mysqli_fetch_array($result)) {
            $sId = $row['student_id'];
            $sName = $row['name'];

            echo "<tr id='".$sId."'>";
            echo "<td>".$i." - </td>";
            echo "<td>$sId</td>";
            echo "<td>$sName</td>";
            echo "<td><button name='sAcc' id='acc-".$sId."' onclick='approveuser(this.id)'>Accept</button></td>";
            echo "<td><button name='sRej' id='rec-".$sId."' onclick='approveuser(this.id)'>Reject</button></td>";
            echo "</tr>";

            $i++;
        }

    }

    getStudent();

?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>
function approveuser(id){

     trid=id.split('-')[1];
     //alert(trid);

    $.ajax({
        url: "update.php",
        type:"post",
        data:{ val : id },


        success: function(result){
            //alert(result);
            $('table#sHold tr#'+trid).remove();
            alert('Updated');

    }
    });
}
</script>
//The code give below this update.php pge(ajax page)
<?php
$data=$_POST['val'];
$status =explode('-',$data);
$user_id=$status[1];

if($status[0]=='acc'){
    $value=1;
}
elseif($status[0]=='rec'){
    $value=0;
}

  $conn = mysqli_connect('localhost', 'root', '', 'srs-db') or die('ERROR: Cannot Connect='.mysql_error($conn));
 mysqli_query($conn,"update student_table set status='$value' where student_id=$user_id");

?>