phpnewbie phpnewbie - 7 months ago 24
Javascript Question

delete_flag column not updating

I have been struggling to make it work, Heres wat I am trying to do .

I have a loan database with a col name - delete_flag(int(11)) where 0 - is not deleted and 1 is deleted .
ex.

+----+-------------+-------------+
| id | loan | delete_flag |
+----+-------------+-------------+
| 1 | John | 0 | this one is visible
+----+-------------+-------------+
| 2 | Meresa | 1 | this one is not visible to user
+----+-------------+-------------+


So for all the loans which are not existing anymore are set to 1 .
My row ids for the table are sent via ajax the result for them in Network tab shows correct row-id like
ids : "11"

My php code to delete the or I would say Update the delete_flag col with value = 1 looks like following :

<?php
session_start();

$con = new mysqli($server,$username,$password,$database);

if(isset($_POST['ids'])){
$ids = $_POST['ids'];
$id_count = count($_POST['ids']); // count array

for($i=0; $i < $id_count; $i++)
{
$id = $ids[$i];

//mysqli_query($con,"DELETE1 FROM k_loans WHERE loan_id=".$id);// this works perfectly ,permanently deleting the record in database .

$result= mysqli_query($con,"UPDATE k_loans set delete_flag = 1 WHERE loan_id='.$id'");

var_dump($result);
echo "successful delete";
}

}
?>


Now if using the following code -

`mysqli_query($con,"DELETE1 FROM k_loans WHERE loan_id=".$id);`


deletes the record from the received id sent via ajax call.

But since I want to keep the record in database, but hidden from the user , I am using this code :

$result= mysqli_query($con,"UPDATE k_loans set delete_flag = 1 WHERE loan_id='.$id'");


The result for the following in the Network tab shows following :


bool(true) successfully deleted.


But strangely no record is updated . When I use the same query in phpmyadmin , it works but not in the script. Anyone who can really help me solve the issue elaborate . Thanks.

Answer

You really should be using prepared statements with bind variables. The problem with your original statement is in the quote marks around your $id value. If you used bind variables, then you wouldn't need to worry about missing or malformed quote marks, or about escaping your values to prevent SQL injection, because prepared statements handle this for you.

$query = "UPDATE k_loans set delete_flag = 1 WHERE loan_id=?";
$stmt = mysqli_prepare($con, $query);
mysqli_stmt_bind_param($stmt, "i", $id);
mysqli_stmt_execute($stmt);

EDIT

Note also, that where you are using the same query multiple times with different id values, you only need to prepare the statement once; you only need to repeat the bind/execute inside your loop

$query = "UPDATE k_loans set delete_flag = 1 WHERE loan_id=?";
$stmt = mysqli_prepare($con, $query);
for($i=0; $i < $id_count; $i++) {
    $id = $ids[$i];
    mysqli_stmt_bind_param($stmt, "i", $id);
    mysqli_stmt_execute($stmt);
}
Comments