1

I am updating Columns in Rows in a Table.

Previously these Columns were set to NULL.

After an UPDATE the columns now have data in them.

But 'affected_rows' remains 0?

Any ideas why?

$query = $conn->prepare("UPDATE table 
                            SET column1=? 
                                WHERE column2 LIKE ? AND column3 IS NULL");


$query->bind_param('ss', $value1, $value2);

$query->execute();

echo "Affected rows: " . $query -> affected_rows . "<br />";  // Returns 0 everytime?
Cranberry
  • 11
  • 4
  • Are the preceding calls actually succeeding, or just failing silently? Have you added code to check that the pre and post conditions are what you expect them to be? – Sammitch Oct 20 '21 at 21:56
  • Well, the database table is being updated if that is what you mean by succeeding. I check the database manually after running the code and the updates are working. – Cranberry Oct 21 '21 at 13:28
  • This code is part of a 'while' loop but that shouldn't affect it?? – Cranberry Oct 21 '21 at 13:34

1 Answers1

0

You need to change your query to properly evaluate NULL values. So change the keyword LIKE into IS

$query = $conn->prepare("UPDATE table 
                            SET column1=? 
                                WHERE column2 LIKE ? AND column3 IS NULL");
Habib Mohammed
  • 41
  • 3
  • Yes, that was just a typo on here, the actual code does say IS NULL. I have also put data in there like '**' and then tested it as "AND column3 = '**' " with the same problem. Data is updated but affected_rows still returns 0. – Cranberry Oct 21 '21 at 13:32
  • This code is part of a 'while' loop but that shouldn't affect it?? – Cranberry Oct 21 '21 at 13:34
  • In that case, you should check whether **column3** has **NULL** value (not empty string) on your db. Also if the code is part a loop, you might need to check the execute statement at each loop using your debugger (it really helps) because you will get affected rows at the end. **Why is it in a loop anyway?** – Habib Mohammed Oct 22 '21 at 00:06
  • Column 3 does have NULL value, when I look at it via MySql (before Update) not after, I have set it to Null a few times too. It is in a WHILE Loop as I am reading through an Array to get the new values for the Update. – Cranberry Oct 25 '21 at 13:59