Re : [PHP-DB] MySQL Update Quandry

>Message-ID: <9e6f1c320511270949jca5a7dq710c4be8c017dc46@mail.gmail.com>
>Date: Sun, 27 Nov 2005 12:49:17 -0500
>From: Glenn Desch=EAnes
>To: php-db@lists.php.net
>MIME-Version: 1.0
>Content-Type: multipart/alternative;
> boundary=3D"----=3D_Part_11492_6161784.1133113757293"
>Subject: Re: [PHP-DB] MySQL Update Quandry
>
>This is not the problem.
>The query fails when there is, as examples, an invalid syntax in the query
>or the table is not found.

That's correct, and there's a distinct lack of=20
understanding going around the list.

A query may or may not return rows, and that is=20
not a sign of "failure". You might class it as=20
failing, but returning no rows is a sign that=20
there's no data matching your query - in which=20
case you may have no matching data, or you may=20
have written a valid query but one which doesn't ask the right question ;-)

So, checking mysql_error() return string will=20
only tell you if the query resulted in a database error.
It will not tell you if there were no results or changes to the database.

>However, if the query executes but updates nothing. How can I know when the
>update has changed a row or not? Not changing a row does not return a
>failure.

PHP has functions for this, which you should read=20
in conjunction with the MySQL3.24 documentation=20
to understand when the DB considers a row inserted, deleted or updated.

mysql_affected_rows : http://us2.php.net/mysql-affected-rows

MySQL 4 and 5 have additional SQL statements to=20
get these values after the insert / update, using=20
a second query: SQL_CALC_FOUND_ROWS /=20
FOUND_ROWS(), LAST_INSERT_ID() LAST_INSERT_ID, ROW_COUNT()

For example, updating a row and giving it the=20
exact same values will result in an update row count of zero.

Inserting should always (unless the query fails)=20
give an affected_rows value more than zero.

Inserting *may* also result in a insert_id value=20
: *If* the table has a primary key, *and* it's an=20
autoincrement primary key, it'll return the=20
autoincrement value) : http://us2.php.net/mysql_insert_id


>Matt's solutions works well. Since the application is hosted and I have
>limited choices... the mysql_info() does the trick.

Although it's not the best use for flow control=20
for this task, because you have to parse the=20
string to get values from it (it's only available=20
PHP 4.3.0 onwards, which shouldn't be a problem=20
on this list), and it's not giving enough information unlike mysql_error()

HTH
Cheers - Neil =20

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php