UPDATE WHERE???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

UPDATE WHERE???

Post by Mister_Bob »

Hopefully somebody can help me with this piece of code.

Code: Select all

$qry = "UPDATE Users WHERE `username` = '".$_SESSION['SESS_Username']."' SET
( PaypalEmail, Email, Password) VALUES('$pemail','$email','".md5($_POST['password'])."')";
	$result = @mysql_query($qry);
	
	//Check whether the query was successful or not
	if($result) {
		unset($_SESSION['eemail']);
		unset($_SESSION['ecemail']);
		header("location: index.php");
		exit();
	}else {
		die("Query failed");
	}
I am trying to have it find a the logged in user in the database and update their email and/or password with new details if they put any in.

Thanks in advance if anyone can help.

Bob
wurdup
Forum Commoner
Posts: 39
Joined: Thu Apr 01, 2010 11:36 am

Re: UPDATE WHERE???

Post by wurdup »

what's the error it's showing?
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

I am getting no server error or error_log just the "Query failed" return.
lunarnet76
Forum Commoner
Posts: 67
Joined: Sun Apr 04, 2010 2:07 pm
Location: Edinburgh

Re: UPDATE WHERE???

Post by lunarnet76 »

instead of

Code: Select all

die('Query failed');
use

Code: Select all

die($qry.'<br>'.mysql_error());
so it will tell you what is the error,

but it seems that you simply misusing the UPDATE statement!

Code: Select all

$qry = "UPDATE Users SET PaypalEmail='$pemail', Email='$email', Password=md5($_POST['password']) WHERE `username` = '".$_SESSION['SESS_Username']."' ";
and use mysql_real_escape_string($pemail); !!!
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

the above code that was just provided gives erro_log message
syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING
my code gave responses
UPDATE Users WHERE `username` = 'MisterBob' SET ( PaypalEmail, Email, Password) VALUES('','','d41d8cd98f00b204e9800998ecf8427e')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `username` = 'MisterBob' SET ( PaypalEmail, Email, Password) VALUES('',''' at line 1
Thanks again for your help with this issue.

Bob
lunarnet76
Forum Commoner
Posts: 67
Joined: Sun Apr 04, 2010 2:07 pm
Location: Edinburgh

Re: UPDATE WHERE???

Post by lunarnet76 »

try

Code: Select all

$qry = '
	UPDATE 
		Users 
	SET 
		PaypalEmail="'.mysql_real_escape_string($pemail).'", 
		Email="'.mysql_real_escape_string($pemail).'", 
		Password=md5('.$_POST['password'].') 
	WHERE 
		`username` = "'.$_SESSION['SESS_Username'].'"';
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

still no luck it appears to be in the where command error as follows
UPDATE Users SET PaypalEmail="", Email="", Password=md5() WHERE username = "MisterBob"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE username = "MisterBob"' at line 6
I think it hates me :(
lunarnet76
Forum Commoner
Posts: 67
Joined: Sun Apr 04, 2010 2:07 pm
Location: Edinburgh

Re: UPDATE WHERE???

Post by lunarnet76 »

you are supposed to post the password from a form, if you don't do it then the SQL fails as $_POST['password'] will not be set
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

Sorry but I am kinda new to this and I am not too sure what you mean, but I went ahead and removed the password part and still getting error
UPDATE Users WHERE `username` = 'MisterBob' SET ( PaypalEmail, Email) VALUES('test@test.com','test@test.com')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `username` = 'MisterBob' SET ( PaypalEmail, Email) VALUES('test@test.com'' at line 1
Thanks

Bob
cpetercarter
Forum Contributor
Posts: 474
Joined: Sat Jul 25, 2009 2:00 am

Re: UPDATE WHERE???

Post by cpetercarter »

Perhaps you need a space between VALUES and the opening bracket which follows it.
lunarnet76
Forum Commoner
Posts: 67
Joined: Sun Apr 04, 2010 2:07 pm
Location: Edinburgh

Re: UPDATE WHERE???

Post by lunarnet76 »

you are not using the correct order in SQL :
the order is UPDATE WHERE SET, and the SET part DOES NOT use VALUES you need to do SET field=value, field2=value2 !!!

http://dev.mysql.com/doc/refman/5.1/en/update.html
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

Ok code =
$qry = '
UPDATE
Users
SET
PaypalEmail="'.mysql_real_escape_string($pemail).'",
Email="'.mysql_real_escape_string($pemail).'",
Password=md5('.$_POST['password'].')
WHERE
`username` = "'.$_SESSION['SESS_Username'].'"';
error =
UPDATE Users SET PaypalEmail="test@test.com", Email="test@test.com", Password=md5() WHERE `username` = "MisterBob"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE `username` = "MisterBob"' at line 6
The form is going to be there for users to update their details if neccessary so they will need to be able to leave the field blank if they do not want to change it.

I went ahead and tested it with a password anyway with error =
UPDATE Users SET PaypalEmail="test@test.com", Email="test@test.com", Password=md5(testpassword) WHERE `username` = "MisterBob"
Unknown column 'testpassword' in 'field list'

Thanks

Bob
cpetercarter
Forum Contributor
Posts: 474
Joined: Sat Jul 25, 2009 2:00 am

Re: UPDATE WHERE???

Post by cpetercarter »

Put 'testpassword' in quotes.
Mister_Bob
Forum Newbie
Posts: 15
Joined: Thu Apr 08, 2010 6:59 pm

Re: UPDATE WHERE???

Post by Mister_Bob »

OK that appears to be working fine now and is updating the database. BUT if I leave fields empty I get blank fields in the database. How can I make it so that it doesn't do that?

Thankyou for all your help

Bob
cpetercarter
Forum Contributor
Posts: 474
Joined: Sat Jul 25, 2009 2:00 am

Re: UPDATE WHERE???

Post by cpetercarter »

What do you want instead of the blank fields?
Post Reply