How to get affected rows after update statement in mysql?

Question

How to get affected rows (not just the number of rows affected) after update statement in mysql.

Answer 1

mysql_info — Get information about the most recent query

$recent = mysql_info();

Useful implementation Example

Answer 2

If you want the actual rows and not the amount of affected rows, simply fetch them before doing the update.

Afterwards you can compare update values with selected values and filter them by difference.

Example with CodeIgniter:

$arr_where = array('date >=' => date('Y-m-d H:i:s'));

$query = $this->db->get_where('table', $arr_where);

$arr_update = array('status' => 'TRUE');

if ($this->db->update('table', $arr_update, $arr_where)) {

    foreach($query->result() as $row)
        foreach(array_keys($arr_update) as $h)
            if ($row->$h != $arr_update[$h])
                echo "This row ({$row->id}) had it's {$h} changed!<br />";

}

Sorry for supplying solution in CodeIgniter, but I found it to be the simpliest example.

To see what the $this->db functions do, see Active Records.

Answer 3

You could try using:

SET @uids := '';
UPDATE <table>
 SET <column1> = <value>
 WHERE <column2> = <value> 
   AND ( SELECT @uids := CONCAT_WS(',', @uids, id) );
SELECT TRIM(LEADING ',' FROM @uids);