From: "DL Neil" <PHPml@DandE.HomeChoice.co.uk>
Reply-To: "DL Neil" <PHPml@DandE.HomeChoice.co.uk>
To: "John Paulsson" <etique@hotmail.com>, <php-db@lists.php.net>
Subject: Re: [PHP-DB] Retrieving rows matched when using UPDATE in PHP/MySQL
Date: Sat, 24 Nov 2001 13:16:32 -0000
How do I retrieve these values using PHP and the MySQL lib? (I'm > especially interested in the "Rows matched" value since the Rows > affected function isn't enough to determine why an update resulted > in 0 rows changed).
John,
It is common practice to check the db BEFORE performing the UPDATE. >You want to do it afterwards... Hey it takes
all sorts right!?
Issue a "SELECT * FROM tables WHERE clause" instruction. Limit the columns retrieved instead of using *, if you
prefer; substitute your UPDATE's "Foo" into the "tables" clause; replace the WHERE clause with the UPDATE's
WHERE clause.
Thanks for your reply.
Well, common practice or not. It seems quite ridicoulous to have to issue an extra query just to see if any rows matches my criterias when MySQL does this by default when I do an UPDATE (see the example). In my case, it would add an overhead that could be avoided.
It is possible to retrieve these values using MySQL's own C-library, so I don't understand why these values aren't exposed in the PHP implementation of MySQL.
This is the scenario.
First I do an UPDATE, and if the UPDATE was successfull AND one or more rows matched the criteria, then I'll have to do an INSERT.
But in the current implementation, I can't determine why mysql_affected_rows() returns 0. It may be because no matches were made, but it could also be because the UPDATE values were the same as the values already stored in the DB. So no write-operation was needed.
Have a look at the following quote from the mysql-doc:
"UPDATE returns the number of rows that were actually changed. In MySQL
Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE."
Why doesn't PHP expose all these values, or perhaps its just undocumented?
Cheers
John
_________________________________________________________________
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp