Page 1 of 1

update query updating when it shouldn't :-S

Posted: Fri Feb 09, 2007 4:26 pm
by Dave2000

Code: Select all

$result = mysql_query("UPDATE ".USER_TABLE." SET activated = '1', ra_key = '0' WHERE userid = '$userid' AND ra_key = '$ra_key'");
if ( $result == true ) { Echo 'Updated'; }
Why does this query always return true? - even when it doesn't actually perform the update :?

As i have found in all previous experience and php.net says...
UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
Well... an error = false, right? What n00bish mistake am i making?

Thanks,

Posted: Fri Feb 09, 2007 4:34 pm
by RobertGonzalez
the manual on [url=http://php.net/function.mysql_query]mysql_query()[/url] wrote:For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

Posted: Fri Feb 09, 2007 5:09 pm
by Dave2000
Thanks. At about the 20th time of reading, :oops: i understood it.

A separate point: strange that mysql_affected_rows() takes no resource $result parameter, mysql_num_rows() does. Why is this? It say mysql_affected_rows() acts on the last query. Is this referring to the last query to mysql within the specific script, or the last query to mysql from any script?

Posted: Fri Feb 09, 2007 5:15 pm
by volka
It's not strange. mysql_query does not return a result resource for the sql statements [INSERT,UPDATE,REPLACE,DELETE] mysql_affected_rows is meant for -> you can't pass a result resource you mysql_affected_rows.
http://de3.php.net/mysql_affected_rows wrote:int mysql_affected_rows ( [resource $link_identifier] )
mysql_affected_rows works per mysql connection.

Posted: Fri Feb 09, 2007 5:30 pm
by Dave2000
OK. So it's possible, if you have more than one user using you site, that when mysql_affected_rows() is called, it may return a value not for the current script, but for another user's running of the script? For example, if the SQL UPDATE etc happens for user "a", then before that user gets a chance to call mysql_affected_rows(), user "b" comes along and also does a SQL statement. User "a" will then be using the mysql_affected_rows() from user "b". It sucks if there is even the remotest possibility of this happening...

Posted: Fri Feb 09, 2007 5:37 pm
by RobertGonzalez
No, it is dependent on the database link identifier.

If you handle an update and I load a page that calls mysql_affected_rows() it will not return your last query. It would return mine, if I had one.

Posted: Fri Feb 09, 2007 5:45 pm
by Dave2000
Thank you