1

From PHP, if I execute a query like the following with mysqli_stmt_execute:

UPDATE users 
SET hair_color = 'brown' 
WHERE height > 180;

How can I figure out which rows in the table were actually updated and affected by the query?

Please note that I am looking to solve this problem in only PHP, without the use of DB triggers, etc. Very specifically, this question came about because we're trying to remove all triggers from our DB.

HartleySan
  • 7,404
  • 14
  • 66
  • 119
  • http://php.net/manual/en/mysqli.affected-rows.php – Noman Nov 30 '17 at 05:41
  • 1
    I don't want the number of affected rows, I want the actual rows back. Or rather, the primary keys of the affected rows, etc. – HartleySan Nov 30 '17 at 05:43
  • 4
    Possible duplicate of [UPDATE/DELETE in mysql and get the list of affected row ids?](https://stackoverflow.com/questions/8873805/update-delete-in-mysql-and-get-the-list-of-affected-row-ids) – Erfan Ahmed Nov 30 '17 at 05:46
  • if you take a look at [`Return Values`](http://php.net/manual/en/mysqli.affected-rows.php#refsect1-mysqli.affected-rows-returnvalues) and example # 1 this output return the primary keys – Noman Nov 30 '17 at 05:47
  • Noman, which part of example #1 shows that? Thanks. – HartleySan Nov 30 '17 at 06:07
  • Trying to solve this without the use of DB triggers. – HartleySan Nov 30 '17 at 14:44

2 Answers2

1
$string="UPDATE users SET hair_color = 'brown' WHERE height > 180";
//Update Query String which will perform in update query

$update_query=mysqli_query($connection,$string);
//by this we are actually running our update queries.    

$total_affected_rows=mysqli_affected_rows($con); 
//By this line you will get total affected rows

//if you need to select and show all the affected rows then you can make a 
//query

$sql=mysqli_query($con,"SELECT * FROM users WHERE height > 180");
Fahad Bhuyian
  • 300
  • 2
  • 10
  • Marking this as the right answer because this was the only suggested way to handle this problem without the use of DB triggers. Should note that the SELECT statement with the same WHERE clause as the UPDATE can be execute before or after the UPDATE. Thanks. – HartleySan Nov 30 '17 at 14:47
1

you can use mysqli_stmt_affected_rows() to get the affected rows

check here http://php.net/manual/en/mysqli-stmt.affected-rows.php

Arun Kumaresh
  • 6,211
  • 6
  • 32
  • 50