Get actual rows affected after UPDATE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
rupam_jaiswal
Forum Newbie
Posts: 22
Joined: Thu Jun 05, 2008 12:54 am

Get actual rows affected after UPDATE

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Get actual rows affected after UPDATE

Post 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.
(#10850)
User avatar
rupam_jaiswal
Forum Newbie
Posts: 22
Joined: Thu Jun 05, 2008 12:54 am

Re: Get actual rows affected after UPDATE

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Get actual rows affected after UPDATE

Post 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;
(#10850)
Post Reply