php sql UPDATE problem

[veledrom]

Hi,

In PHP page, Code below always returns TRUE even no record is updated. How can i solve this problem?

Thanks

$q="UPDATE login SET password=SHA1('$newPassword') WHERE id='$loginID' AND password=SHA1('$oldPassword')";
$run=mysql_query($q);	

if ($run===TRUE) {						echo "SUCCESSFUL";			
} else {						
	echo "ERROR: Old password is wrong. Please try again.";
}

[nav33n]

password column is of datatype varchar (I think). Try this.

$q="UPDATE login SET password='".SHA1('$newPassword')."' WHERE id='$loginID' AND password='".SHA1('$oldPassword')."'";

[veledrom]

Still returns TRUE or 1 (always).
Also, yes it is Varchar(40)

[nav33n]

Dang! Stupid me.. A variable inside ' ' is considered as string.. :icon_rolleyes:

This should fix it.

$q="UPDATE login SET password='".SHA1($newPassword)."' WHERE id='$loginID' AND password='".SHA1($oldPassword)."'";

[veledrom]

No still same. Very strange.

[nav33n]

:-S Check if all the variables are set properly, echo the query and execute it in phpmyadmin/mysql console ! Tell us if it still updates in phpmyadmin/mysql console.
I executed the first 2 queries and I noticed the difference.
The query

$q="UPDATE login SET password='".SHA1($newPassword)."' WHERE id='$loginID' AND password='".SHA1($oldPassword)."'";

prints this.UPDATE login SET password='9b2f1fe6da132ed153cd613cb453dca9285af4b9' WHERE id='10' AND password='2fc535a9fd9760b71e76f92dff31ea719625b652'
Whereas, this query,

$q="UPDATE login SET password='".SHA1('$newPassword')."' WHERE id='$loginID' AND password='".SHA1('$oldPassword')."'";

prints this.UPDATE login SET password='a4c20b3df57a6a1b409d16274aafd91b35447cee' WHERE id='10' AND password='6ce6bf140f3611ce3133244a69b10a76bb412a47'
In both the cases, I have declared the variables,$newPassword = "thisismynewpassword";
$oldPassword = "thisismyoldpassword";

Edit: You could also try using mysql_affected_rows to see how many rows were really affected!

[veledrom]

Although 1st one is what i need (i have compared SHA1 codes):
Your first one returns TRUE even it is FALSE:
Your second one returns FALSE even it is TRUE:
for

if ($run===TRUE) {	//or FALSE					echo "SUCCESSFUL";			
} else {						
	echo "ERROR: Old password is wrong. Please try again.";
}

[nav33n]

1st query is correct and the 2nd is wrong. What does mysql_affected_rows print ? Did you also check in phpmyadmin ? Does it update any record ? Umm.. for a change, try using == instead of === . Lets see how it goes..

[veledrom]

When password is right then affected rows 1 and returns TRUE.
When password is wrong then affected rows 0 and returns still TRUE.

Or FALSE version.

Also == didn't change the result.

[veledrom]

Everything is same in php and SQL phadmin.

[nav33n]

Okay ! Found it.
Even though mysql_query returns a boolean value, you can't compare its value as

if($run === TRUE)

It has to be

if($run === "TRUE")  //or in lower case. It doesnt matter!

I am surethis will fix it.
Its 3.40 in the morning and I need to get my sleep :D

[veledrom]

Let's say password is 111111 as SHA1 style in the table. if i want to update 111111 with again 111111, no rows are affected. Is this normal? If i want to update with different 222222 password then affects.
!!!

[veledrom]

Sorry, Still no result. Anyway, have a nice sleep. I hope until you wake up, i'll find an answer

[nav33n]

Yep. Since it doesn't update anything, no rows are affected.

http://in.php.net/mysql_affected_rows

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

[veledrom]

I used if (@mysql_affected_rows($sql)==1) { but no result for same value or different value

[nav33n]

Dude!
For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error. http://in.php.net/function.mysql-query
It will return false on error. There is no error in your query. Since there is no error, it will always return TRUE !!! Its better you use mysql_affected_rows.

God! Why did it take so long to realise this :D Yeah alright! I can sleep peacefully now.

Edit:
(@mysql_affected_rows($sql)==1) {
Thats not the correct syntax. mysql_affected_row takes only 1 parameter and thats the connection link identifier).

if(mysql_affected_row() == 1) { // if you are sure it affects only 1 row or else if(mysql_affected_rows() > 0 ) {
   echo "Updated...";
} else {
  echo "Not updated..";
}

[veledrom]

OHHHHHOOOOOO. It works now. Thank yo for all your help. Have a nice dream.

[nav33n]

Yay! Finally..

Thanks.. I will try.. :)