Page 1 of 1

Get actual rows affected after UPDATE

Posted: Sat Apr 03, 2010 7:48 am
by rupam_jaiswal
Hi,
I am using the following mysql query statement:

mysql_query("UPDATE inventory SET username='$username' WHERE username='$target->username' ORDER BY RAND() LIMIT 15");

now I want to also display to the user which rows were affected by the above query and list all those rows in an echo or something.

I know i can use mysql_affected_rows() to find HOW MANY rows were affected but can you make a script that will list the rows that were affected? or is it not possible?

Regards

Re: Get actual rows affected after UPDATE

Posted: Sat Apr 03, 2010 11:39 am
by Christopher
The first question is why you are doing "UPDATE ... ORDER BY RAND() LIMIT 15" at all? If there are other records that have that username set, then you would probably need to set an additional column and then SELECT on it to find the rows.

Re: Get actual rows affected after UPDATE

Posted: Sat Apr 03, 2010 12:18 pm
by rupam_jaiswal
Christopher wrote:The first question is why you are doing "UPDATE ... ORDER BY RAND() LIMIT 15" at all? If there are other records that have that username set, then you would probably need to set an additional column and then SELECT on it to find the rows.
Not getting, can you please elaborate it more.
UPDATE ... ORDER BY RAND() LIMIT 15 is just an example.Say me query is UPDATE records where condition.
Now I want only the affected rows with the UPDATE command.
Please note that can be a records with condition even before running the UPDATE query, so If use SELECT * FROM inventory WHERE condition then it will fetch all the records which have not been modified by UPDATE command.

Re: Get actual rows affected after UPDATE

Posted: Sat Apr 03, 2010 12:42 pm
by Christopher
I was saying that you could do it directly by doing:

UPDATE inventory SET changed=0;
UPDATE inventory SET username='$username',changed=1 WHERE username='$target->username' ORDER BY RAND() LIMIT 15";
SELECT * FROM inventory WHERE changed=1;