John Davidson

Message:

I have table with many rows. I have exp date and I want to change status of those rows where date is already exp.

It work well if I do not use UPDATE. If I just echo them. But when I want to UPDATE status of this row to 0, problems start. My problem is that it change only 1 row and not all of them that needs to be whit status 0.

$sql = "SELECT * FROM table";

$result = mysqli_query($conn, $sql);

$resultcheck = mysqli_num_rows($result);

$id = $row['id'];



if($resultcheck > 0) {

while($row = mysqli_fetch_assoc($result)) {



$expdate = $row['date'];

$exp = strtotime($date);



$today = date('m/d/Y');

$td = strtotime($today);



if($td>$exp) {

$status=0;



$sql = " UPDATE table SET status = '$status' WHERE ID = '$id' ";

$result = mysqli_query($conn, $sql);

}

}

}

Any advice how to fix that, I tried several option but nothing worked.

---

My Answer:
You need to move the line `$id = $row['id'];` inside the while loop so that it gets the id of each row in the loop. Also, you should use prepared statements to prevent SQL injection. Here is the updated code:

php
$sql = "SELECT * FROM table";
$result = mysqli_query($conn, $sql);
$resultcheck = mysqli_num_rows($result);

if($resultcheck > 0) {
while($row = mysqli_fetch_assoc($result)) {
$id = $row['id'];
$expdate = $row['date'];
$exp = strtotime($expdate);

$today = date('m/d/Y');
$td = strtotime($today);

if($td > $exp) {
$status = 0;

$update_sql = "UPDATE table SET status = ? WHERE ID = ?";
$stmt = mysqli_prepare($conn, $update_sql);
mysqli_stmt_bind_param($stmt, "ii", $status, $id);
mysqli_stmt_execute($stmt);
}
}
}


This code will update the status of all rows where the expiry date has passed. Make sure to replace `table` with your actual table name.