CODEWITHSUNDEEP
phpmysqli
Gladium
Gladium

Reputation: 31

How to update the selected rows?

I'm using PHP and mysqli to select 5 random rows, while that works just fine I would also like to update the status of the selected rows to 1 at the same time those rows are fetched. I've seen many examples of the similar question, but couldn't find one that I could use. I appreciate any help.

$query= $mysqli->prepare("SELECT ID, Name, Age FROM table WHERE status=0 ORDER BY RAND() LIMIT 5");
$query->execute();
$query->bind_result($ID, $Name, $Age);

while ($query->fetch()) {
    echo "<h1>$Name</h1>";
    echo "<p>$Age</p>";
}

Upvotes: 1

Views: 58

Answers (1)

Qirel
Qirel

Reputation: 26460

You can run a separate query, where you update based on the IDs you fetched. Put all the IDs into an array, then use a prepared statement where you build the query dynamically.

$ids = [];
$query = $mysqli->prepare("SELECT ID, Name, Age FROM table WHERE status=0 ORDER BY RAND() LIMIT 5");
$query->execute();
$query->bind_result($ID, $Name, $Age);

while ($query->fetch()) {
    $ids[] = $ID;
    echo "<h1>$Name</h1>";
    echo "<p>$Age</p>";
}
$query->close();

// Create query dynamically
$stmt = $mysqli->prepare("UPDATE table SET status=1 WHERE id IN (".implode(", ", array_fill(0, count($ids), "?")).")");
// Bind dynamic values 
$stmt->bind_param(str_repeat("i", count($ids)), ...$ids);
$stmt->execute();
$stmt->close();

Upvotes: 1

Related Questions