Clever Geek Handbook

How can I use mysqli / php to update all records after the query has already been executed?

I have a custom form that sets a single "current" record. No more than one entry can be installed at a time. So, I present the user with one dropdown, they select the item they want to install and hit "UPDATE" at the bottom of the form.

PHP / Mysqli needs to go in and set the "current" column to 0 for all records, and then update the value from the form to "1".

At first I just counted the number of rows and ran a bunch of queries to update the column to 0 or 1 if loop count = row id. Well ... it quickly broke when I started doing testing on other sites and the index numbers were higher than the total row count. Yes, the dumb way to do it initially!

This is what I tried to do with PHP / MySQL code:

// $link  is the database link defined elsewhere. This does work as I use it all over the place
$setCurrent = X; // This is the number passed from my form

$init_query = "SELECT id, current FROM myTable";

if ($stmt = $link->$prepare($init_query) {
  $stmt->execute() or die ($stmt->error);
  $stmt ->bind_result($id, $current)
  while ($stmt->fetch()){   
    if ($id == $setCurrent){
    
    $update_sql = "UPDATE myTable SET current ='1' WHERE id='".$setCurrent."'";
    $stmt2 = $link->prepare($update_sql);
    $stmt2->execute; 
    
    }
    
    else {
    $update_sql =  "UPDATE myTable SET current ='0' WHERE id='".$id."'";
    $stmt2 = $link->prepare($update_sql);
    $stmt2->execute;
 }
 $stmt->close();
Run code


This fails and gives me a fatal error: impure error: a member function call is done with boolean in .....

I am puzzling over this and cannot figure out what is going on. It has been a few years since I was working in PHP / MySql and this is my first break in OO Mysqli. Please, be careful:)

+3


source to share


2 answers


You are missing two closing curly braces. One for the first if()

and one forwhile()



+1


source


why do them one at a time? You can do it in one request

$setCurrent = X; 
$query = 'UPDATE myTable
          SET `current` = (id = :current)';
$stmt = $link->prepare($query);
$stmt->bindValue(':current', $setCurrent);
$stmt->execute();

(and abusing the fact that if id is $ setCurrent then the part between () resolves to true, which is 1.)

some explain:

SELECT 10=10;

will give a kind of "TRUE". But since Mysql does not give true, it gives 1.same for:

SELECT 10=20;

This is FALSE, so you get 0.



Now back to your query: you want to get the value 0 for all records for which id is not equal to some number. And you want 1 for equal: So you need to compare the column id value with $ setCurrent. When they match, you get 1, and you put that 1 in the "current" column, And when they don't match, all other cases, then you get 0 and that 0 goes to the Current column.

And yes, it can also be done like:

UPDATE mytable
SET `current` = CASE id
                 WHEN $setCurrent  THEN 1
                 ELSE 0
               END CASE

or using IF, But other syntaxes are much shorter

change backtics are needed around the column name as current is a reserved word

+1


source






More articles:


All Articles