Prev: A cleaner way to do this?
Next: Top vs. Bottom Posting
From: James Colannino on 25 Mar 2010 17:10
Hey everyone,

I have a question. If I do a mysql query that updates a column in a row
to the same value, I get 0 rows affected. However, I also get 1 or more
matched rows. Is there a way that I can return the number of matched
rows, rather than the number of rows affected?

I'm trying to get something done with as few SQL queries as possible.
Thanks!

James
From: Floyd Resler on 25 Mar 2010 17:16

On Mar 25, 2010, at 5:10 PM, James Colannino wrote:

> Hey everyone,
>
> I have a question. If I do a mysql query that updates a column in a row
> to the same value, I get 0 rows affected. However, I also get 1 or more
> matched rows. Is there a way that I can return the number of matched
> rows, rather than the number of rows affected?
>
> I'm trying to get something done with as few SQL queries as possible.
> Thanks!
>
> James
>

As for as I know, MySQL simply just doesn't report a row as being affected if nothing has changed in it. To get the number of matched rows, try doing a SELECT query before you do the UPDATE query. I don't know if that will produce the results you're looking for, but it might.

Take care,
Floyd

From: James Colannino on 25 Mar 2010 17:19
Floyd Resler wrote:
> As for as I know, MySQL simply just doesn't report a row as being affected if nothing has changed in it. To get the number of matched rows, try doing a SELECT query before you do the UPDATE query. I don't know if that will produce the results you're looking for, but it might.
>

Yeah, the extra select is what I was hoping to avoid :-P The MySQL
client will return both the number of rows matched and the number of
rows affected by the query; I was hoping perhaps the PHP API offered a
way for me to do the same. Ah well... Thanks!

James

From: Yousif Masoud on 25 Mar 2010 17:54
On Thu, Mar 25, 2010 at 9:19 PM, James Colannino <james(a)colannino.org>wrote:

> Yeah, the extra select is what I was hoping to avoid :-P The MySQL
> client will return both the number of rows matched and the number of
> rows affected by the query; I was hoping perhaps the PHP API offered a
> way for me to do the same. Ah well... Thanks!
>

You can access data about the latest query via mysql_info()

http://php.net/manual/en/function.mysql-info.php

You can parse the string for the values you need.

There may be a more elegant solution.
From: Shawn McKenzie on 26 Mar 2010 11:15
James Colannino wrote:
> Hey everyone,
>
> I have a question. If I do a mysql query that updates a column in a row
> to the same value, I get 0 rows affected. However, I also get 1 or more
> matched rows. Is there a way that I can return the number of matched
> rows, rather than the number of rows affected?
>
> I'm trying to get something done with as few SQL queries as possible.
> Thanks!
>
> James

If it works in your situation you can use REPLACE instead of UPDATE and
the mysql_affected_rows() should work. You may need to divide this
number by 2 as I think it counts the deletes and inserts.

--
Thanks!
-Shawn
http://www.spidean.com
 | 
Pages: 1
Prev: A cleaner way to do this?
Next: Top vs. Bottom Posting