CODEWITHSUNDEEP
phpmysql
Amit Jha
Amit Jha

Reputation: 116

How do you get the actual, specific changed rows after an update statement in MySQL using PHP?

How do I get the number changed rows (rather than just the number of rows affected) after an update statement in MySQL using PHP?

Upvotes: 6

Views: 14280

Answers (4)

Slipstream
Slipstream

Reputation: 14742

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);

Upvotes: 1

Gustav
Gustav

Reputation: 1341

mysql_info — Get information about the most recent query

$recent = mysql_info();

Useful implementation Example

Upvotes: 3

Robin Castlin
Robin Castlin

Reputation: 10996

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.

Upvotes: 1

nischayn22
nischayn22

Reputation: 447

Since you are using php you can use mysql_affected_rows() to get the number of affected rows.

Upvotes: -1

Related Questions