Chapter 13

bopjo1 · October 12th, 2010, 09:39 PM

If I login as administrator and try to modify a user's account I get this error:

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 user_id = 3' at line 5

Now the code it's referring to is this, and I can't see anything wrong with it:

Code:

case 'Modify Account':
		 $user_id = (isset($_POST['user_id'])) ? $_POST['user_id'] : ' ';
		 $email = (isset($_POST['email'])) ? $_POST['email'] : ' ';
		 $name = (isset($_POST['name'])) ? $_POST['name'] : ' ';
		 $access_level = (isset($_POST['access_level'])) ? $_POST['access_level'] : ' ';
		 if (!empty($user_id) && !empty($name) && !empty($email) && !empty($access_level)) {
		     $sql = 'UPDATE cms_users SET
	             email = "' . mysql_real_escape_string($email, $db) . '",
		     name = "' . mysql_real_escape_string($name, $db) . '",
		     access_level = "' . mysql_real_escape_string($access_level, $db) . '",
		     WHERE user_id = ' . $user_id;
		     mysql_query($sql, $db) or die(mysql_error($db));
		 }
		 redirect('cms_admin.php');
		 break;

Anyone else have this problem?

bopjo1 · October 13th, 2010, 05:30 PM

Ok, I got it to work using this code:

Code:

case 'Modify Account':
		 if (isset($_POST['user_id']) && isset($_POST['email']) && isset($_POST['access_level']) && isset($_POST['name'])) {
			 $user_id = mysql_real_escape_string($_POST['user_id']);
			 $email = mysql_real_escape_string($_POST['email']);
			 $name = mysql_real_escape_string($_POST['name']);
			 $access_level = mysql_real_escape_string($_POST['access_level']);		 
		 }
         if (!empty($user_id) && !empty($name) && !empty($email) && !empty($access_level)) {
            $sql = "UPDATE cms_users SET
                    email = '$email',
                    name = '$name',
                    access_level = $access_level
                WHERE
                    user_id = $user_id";
            mysql_query($sql, $db) or die(mysql_error($db));
         }
         redirect('cms_admin.php');
         break;

One obvious wrong thing in the code from the book is this line:

Code:

if (!empty($user_id) && !empty($name) && !empty($email) && !empty($access_level) && !empty($user_id)) {

If you'll notice, !empty($user_id) is in there twice. At the beginning and the end. One them should be deleted. Still, this does not fix the broken functionality of the SQL query.

Notice in my version of the query how I use double quotes to start the query, and single quotes for the variables with string values. Variables with numerical values ($access_level & $user_id) don't get quoted in SQL queries. Also, I moved all the mysql_real_escape_string's out of the query.

I hope this helps somebody.

staco · February 22nd, 2013, 01:14 PM

Its Works,

Thanks ,You save my time