Page 1 of 1

sql UPDATE

Posted: Mon Jul 30, 2007 10:08 pm
by krraleigh
Can you tell me why this sql UPDATE does absolutely nothing?

Code: Select all

$insert = "UPDATE user SET username = $mod_userName, pass = $mod_myPass 
		        WHERE email = $myEmail";
		$changePassWord = mysql_query($insert);
		echo "successfully updated";
		exit;
Kevin

Posted: Mon Jul 30, 2007 10:10 pm
by Benjamin
because your strings aren't in single quotes.

Posted: Mon Jul 30, 2007 10:19 pm
by krraleigh
I made the changes but I still have no results.
Any other ideas?

Code: Select all

$insert = "UPDATE user SET 'username' = '$mod_userName', 'pass' = '$mod_myPass' 
		        WHERE 'email' = '$myEmail'";
		$changePassWord = mysql_query('$insert');
		echo "successfully updated";
		exit;
Kevin :?

Posted: Mon Jul 30, 2007 10:20 pm
by Benjamin
because your field names have quotes around them. They should have nothing or backticks..

`

I think user is a reserved MySQL keyword, so that should have `backticks` around it.

Posted: Mon Jul 30, 2007 10:25 pm
by feyd
mysql_error() should tell you more...

Posted: Mon Jul 30, 2007 10:40 pm
by krraleigh
mysql error shows me this:

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 'uP9sDN'', password = ''7938bd99b60972f9c4732a2e6d6e4ff4'' WHERE emai' at line 1

Code: Select all

$userName = generatePassword (6, 5);
$myPass = generatePassword (6, 5);
		
$mod_myPass = md5($myPass);
		
$mod_userName = quote($userName);
$mod_myPass = quote($mod_myPass);
		
unset($_SESSION['SES_loginErr']);
		
$userName = mysql_real_escape_string($userName);
$myPass = mysql_real_escape_string($myPass);
						
$insert = "UPDATE user SET username = '$mod_userName', pass = '$mod_myPass' 
WHERE email = '$myEmail'";
$changePassWord = mysql_query($insert);
echo mysql_error();
echo "successfully updated";
exit;
thank you
Kevin

Posted: Mon Jul 30, 2007 10:45 pm
by John Cartwright
You'll notice in your error the values have two quotes.. It looks like your quote() function is already adding quotes around your values so you can drop them in the sql statement.

Posted: Mon Jul 30, 2007 11:10 pm
by krraleigh
I appreciate the help.
I found the problem.

I need to put /" around each $value for the update to work
Tried it in every fashion possible, but this is the only one that works.

Code: Select all

my quote function:
function quote($value) 
        { 
            // Stripslashes 
            if (get_magic_quotes_gpc()) { 
                $value = stripslashes($value); 
            } 
            // Quote if not a number or a numeric string 
            if (!is_numeric($value)) { 
                $value = "'" . mysql_real_escape_string($value) . "'"; 
            } 
            return $value; 
        } 


		// mysql_real_escape_string() is called in the quote() function
		$mod_userName = quote($userName);
		$mod_myPass = quote($mod_myPass);
		
		unset($_SESSION['SES_loginErr']);
						
		$insert = "UPDATE user SET username = \"$mod_userName\", password = \"$mod_myPass\" 
		        WHERE email = \"$myEmail\"";
		$changePassWord = mysql_query($insert);
		echo mysql_error();
		echo "successfully updated";
		exit;
Thank You
Kevin

Posted: Mon Jul 30, 2007 11:46 pm
by John Cartwright
I meant

Code: Select all

$insert = "UPDATE `user` SET `username` = $mod_userName, `password` = $mod_myPass WHERE `email` = $myEmail";

Posted: Tue Jul 31, 2007 3:13 am
by onion2k

Code: Select all

$insert = "UPDATE user SET username = \"$mod_userName\", password = \"$mod_myPass\"
                        WHERE email = \"$myEmail\"";
                $changePassWord = mysql_query($insert);
                echo mysql_error();
                echo "successfully updated"; 
That's going to echo "successfully updated" whether the query was successful or not. That seems a bit silly.