wrong mySQL record updated with PHP

[hontai]

I've run into something that seems like a glitch, because the code and the test database are so simple that I can't fathom what could be wrong. I'm running a simple update query on a mySQL database, using PHP version 4.4.2 and mySQL 4.1.21-standard (my web host's, not my own):
[php]

$resetkey = (...some randomized string...);
$query = "UPDATE weblogins SET veristring='".$ resetkey."' WHERE email='".$_POST['email']."'";
$result = mysql_query($qu ery) or die('Query failed: ' . mysql_error());

if ($result){ ... }

[/php]
The email variable is "41test@test.co m"

Simple right? The problem I'm having is that there is only one record in that table, it has the email address "42test@test.co m", and PHP is updating its veristring field instead of finding no matching record and returning a -1 result.

In debugging this I had the query printed to screen to verify it:

UPDATE weblogins SET veristring='NcY ' WHERE email='41test@t est.com'


Totally perplexed, I copied that query and pasted it into the phpMyAdmin interface to test it directly, and it came back with no fields affected, as it should be. I then had it generate the php code for that query:

$query = 'UPDATE weblogins SET veristring=\'Nc Y\' WHERE email=\'41test@ test.com\'';

... stuck it back into my php code, and still it updates that record even though the email fields don't match. It seems php thinks that they're close enough... but this causes a problem.

I created this test specifically to test the error messages given to the user, but I have yet to see those messages because PHP keeps incorrectly updating this record.

wtf? Any thoughts? Is this a known problem fixed with a newer version of either software?

[hontai]

Did some further testing, and the plot thickens.

I removed the one record from the database, leaving it complete empty, and the UPDATE query still returns a value of 1.

Performing a SELECT * FROM weblogins WHERE email='test@bla h.com' should also return -1, since there are no records in there, but instead returns "Resource id #6" as the result.

?????????

I say, ????

[AnthonyScaife]

Hello,

With regards to the plot thickening:

I believe it is correct that

SELECT * FROM weblogins WHERE email='test@blah.com'

should return a resource id.

And you should then test for returned rows using
[PHP]int mysql_num_rows ( resource $result )[/PHP]
PHP documentation

I'm still pondering the 'pre plot thickening' bit though.

Anthony

[AnthonyScaife]

Here is a possible answer,

Using mysql_query

resource mysql_query ( string $query [, resource $link_identifie r] )

I note you don't specify the 2nd argument: resource $link_identifie r

and php will assume the last link opened by mysql_connect() if the link identifier is not specified.

Have you done more than one mysql_connect() , and you're accidently on the wrong one ? and looking at the wrong db in phpMyAdmin ?

That is absolutely the only thing I can think of !

[hontai]

Thank you for the suggestions, Anthony.

There's another problem with UPDATE queries. As I understand it, all update and insert queries should return a boolean to indicate success or failure. For me, regardless of whether or not the update query changes anything, it's always returning "1". I worked around it by using mysql_affected_ rows() on the db resource, thanks to your suggestion of using mysql_num_rows( ) on the result set for SELECT statements.

Any thoughts? This creates additional testing that shouldn't have to be done, and is making me a bit paranoid about whether or not my inserts and updates are actually taking place.

As for the original problem... I still have no idea what was going on, but inexplicably it has stopped. I wasn't using the $link identifier, but I only had one link open at a time and always close it, even on errors. I have since put that identifier in, just as a safeguard if for no other reason. Oddly, I haven't found one example piece of code in all the forums I viewed after this that uses that, everyone is just using mysql_query([query string]). That's why I wasn't using it, I hadn't seen it before. Makes me wonder how stable everyone's db access is.

Thanks again!

[AnthonyScaife]

Hello,

[ I would have answered sooner, but I have been off ]

Regarding the mysql_query function:
resource mysql_query ( string $query [, resource $link_identifie r] )

The PHP manual

http://uk.php.net/manual/en/function.mysql-query.php

says 'For UPDATE SQL statements mysql_query() returns TRUE on success or FALSE on error.'

TRUE = mysql understood the sql command and was able to carry it out.

FALSE = mysql did not understand the sql command, or $link_identifie r is bad, or the database is having some other problem and could not execute the command .... etc

ie the return value is mysql saying 'I understood the command, and completed it', and NOT mysql saying 'Your command affected the database'.

So it is proper to use mysql_affected_ rows() :

http://uk.php.net/manual/en/function.mysql-affected-rows.php

after the update.

[ I think I'm right anyway ]