update yields zero rows

[awingnut]

I have a PHP query that is suppsed to update a table. When I run it mysql_affected_rows returns a zero. Unfortunately there are no errors I can find to see why. If the query "update" fails like this how do I find out the reason? TIA.

 

[awingnut]

I forgot to mention, if it matters, the update is quite long (lots of fields). Is there some limit to the length of a query (I did not insert any new line characters in the string)?

 

[itech20059]

Where is the code? Version of php and mysql?

 

[awingnut]

Thanks for the reply. I'm not sure what you want me to post as far as code is concerned. Most of the data is personal. However, the form of the query is:

update mytable set column1='blah',...,column25='more blah' where username=myname and pwd=encrypt('mypassword')

PHP V 4.3.8 and MySQL v 8.40 dist 4.0.20

 

[spookie]

Hi,
Check if you get a matching result by running query on MySQL prompt like :

select count(*) from mytable where username='myname' and pwd=encrypt('mypassword')

If that return NonZero record, then there should be problem with the Syntax of your update query(May be missing comma etc).
Also have you set your PHP errors ON by puting

error_reporting(E_ALL)

at the top of your PHP application file.



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[awingnut]

Thanks again for the reply. The 'select' yeilds a count of 0 with 1 row selected. I'm not sure what that means but I take it form your suggestion that means something other then syntax is wrong.

I was not aware of the error_reporting function so I added it. However, I'm not seeing any additional information anywhere (I'm not using '@' on any of my calls).

 

[spookie]

The 'select' yeilds a count of 0 with 1 row selected

Not sure what it means exactly..
If the count value you are getting is zero, then there is no record matching your criteria (ie where username=myname and pwd=encrypt('mypassword') ) and hence no record to update. That in turn will return you mysql_affected_rows equals zero.


--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[Ghodmode]

There's no syntax error. The one row selected was the count. Try the select query without the password at all i.e.:

select count(*) from mytable where username='myname';

If you get 0 again, your username is not in the table at all. If you get 1, then your password is wrong.

I suspect that the encryption used when the record was inserted is not the same as it is when you use the encrypt function now. Could have been put there unencrypted by accident? Could it be accidentally blank?

Forget the count, it's probably only one row anyway. Try

select username, pwd from mytable where username='myname';

If you see your password instead of some random characters, that's your problem.

--
-- Ghodmode

 

[awingnut]

Using your command I get exactly what I expected. The username and random characters for the password. However, I think you are on to something. I next tried the same thing adding the password to the selection criteria and got the empty set. My thought immeditely was that the password was indeed encrypted wrong. So I updated the table:

update mytable set pwd=encrypt('mypassword') where username='myname';

The result was matched 1 and changed 1.

I immediately did the select again using the above 'pwd=' for the selection criteria and the result was the empty set. What is interesting is that when I authenticate via Apache using that same username and password, it works which is why I never suspected a password problem. Now, the question is, why does the password work via Apache auth_myslq but not with a select?

 

[spookie]

Use MySQL PASSWORD() function if you want to Encrypt the password.

e.g.

update mytable set pwd=PASSWORD('mypassword') where username='myname';
select username, pwd from mytable where username='myname' and pwd=PASSWORD('mypassword');

For more information on Password Encryption have a look at

http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[awingnut]

Thanks for the reply but 'password()' does not work for auth_mysql authentication via apache. Only 'encrypt()' works.

 

[awingnut]

Finally, got it. It turns out the text of one field contained a '/'. Apparently, MySQL doesn't like that. I guess I need to use 'addslashes()'.