Page 1 of 1

Error in SQL Syntax?

Posted: Tue Aug 01, 2006 10:53 pm
by 4Boredom
There has been an error creating your account. Please contact the webmaster.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 '' at line 3

The problem is the error shows in this register.php but the line in this is really blank? Think I messed up my MySQL?

Code: Select all

<? 

include 'db.php'; 
# grab the POST variables from the HTML form
$first_name = $_POST['first_name']; 
$last_name = $_POST['last_name']; 
$email_address = $_POST['email_address']; 
$username = $_POST['username']; 
$password = $_POST['password']; 
$gender = $_POST['gender']; 
$month = $_POST['month']; 
$day =  $_POST['day']; 
$year = $_POST['year']; 

# Any escaped characters?
$first_name = stripslashes($first_name); 
$last_name = stripslashes($last_name); 
$email_address = stripslashes($email_address); 
$username = stripslashes($username); 
$password = stripslashes($password); 
$gender = stripslashes($gender);
$month = stripslashes($month);
$day = stripslashes($day);
$year = stripslashes($year);

# Any errors in the posted fields? 
if((!$first_name) || (!$last_name) || (!$email_address) || (!$username) || (!$gender) || (!$month) || (!$day) || (!$year)){ 
    echo 'You did not submit the following required information! <br />'; 
    if(!$first_name){ 
        echo "First Name is a required field. Please enter it below.<br />"; 
    } 
    if(!$last_name){ 
        echo "Last Name is a required field. Please enter it below.<br />"; 
    } 
    if(!$email_address){ 
        echo "Email Address is a required field. Please enter it below.<br />"; 
    } 
    if(!$username){ 
        echo "Display Name is a required field. Please enter it below.<br />"; 
    } 
	  if(!$gender){ 
        echo "Gender is a required field. Please enter it below.<br />"; 
    } 
      if(!$month){ 
        echo "Month is a required field. Please enter it below.<br />"; 
    } 
	  if(!$day){ 
        echo "Day is a required field. Please enter it below.<br />"; 
    } 
	  if(!$year){ 
        echo "Year is a required field. Please enter it below.<br />"; 
    } 
    include 'signup.php'; 
    exit(); 
} 
     
# does this user already exist in the database?  
 $sql_email_check = mysql_query("SELECT email_address FROM users WHERE email_address='$email_address'");
 $email_check = mysql_num_rows($sql_email_check);
  
 if(($email_check > 0) || ($username_check > 0)){ 
     echo "Please fix the following errors: <br />"; 
     if($email_check > 0){ 
         echo "<strong>Your email address has already been used by another member in our database. Please use a different email address!<br />"; 
         unset($email_address); 
     } 
     include 'signup.php'; // Shows the form again!
     exit(); 
 } 
  

# Enter info into the Database. 

$info2 = htmlspecialchars($info); 


$sql = mysql_query("INSERT INTO `users` 
		(`first_name`, `last_name`, `email_address`, `username`, `password`, `signup_date`) VALUES 
		(\"" . $first_name . "\",\"" . $last_name . "\",\"" . $email_address . "\",\"" . $username . "\",\"" . $password . "\",\"" . time() . "\")"); 

		$sql = mysql_query("INSERT INTO `basic` 
		(`gender`, `month`, `day`, `year`) VALUES 
		(\"" . $gender . "\",\"" . $month . "\",\"" . $day . "\",\"" . $year . "\""); 


if(!$sql){ 
    echo 'There has been an error creating your account. Please contact the webmaster.'; 
    echo mysql_error();
} else { 
    $userid = mysql_insert_id();
    // Let's mail the user! 
    $subject = "Your 4Boredom.com Membership";
    $message = "Dear $first_name $last_name, 
    You are now registered at our website, http://www.4boredom.com! 
     
    You are now able to login with the following information: 
    EMail Addresss: $email_address
    Password: $password 
    Please keep this username and password in a location that is easily accessible by you. 
     
    Thanks! 
    Derek Lemire
	4boredom
     
    This is an automated response, please do not reply!"; 
     
    mail($email_address, $subject, $message, "From: MyWebSite<derek@4boredom.com>\nX-Mailer: PHP/" . phpversion()); 
    echo 'Your membership information has been mailed to your email address! Please check it and follow the directions!'; 
} 
?>

Posted: Tue Aug 01, 2006 11:09 pm
by feyd
echo the query strings as they would be passed to MySQL.

Posted: Tue Aug 01, 2006 11:11 pm
by 4Boredom
im confused as to what you mean

Posted: Tue Aug 01, 2006 11:13 pm
by Luke
he means echo out all your sql queries so

Code: Select all

echo $sql;
That way you know what is being sent to mysql and you can fix it.

Posted: Tue Aug 01, 2006 11:57 pm
by 4Boredom
$sql = mysql_query("INSERT INTO `users` (`first_name`, `last_name`, `email_address`, `username`, `password`, `signup_date`) VALUES (\"" . $first_name . "\",\"" . $last_name . "\",\"" . $email_address . "\",\"" . $username . "\",\"" . $password . "\",\"" . time() . "\")") $sql = mysql_query("INSERT INTO `basic` (`gender`, `month`, `day`, `year`) VALUES (\"" . $gender . "\",\"" . $month . "\",\"" . $day . "\",\"" . $year . "\"")There has been an error creating your account. Please contact the webmaster.

I think its the fact that I have 2 SQL inserts into db?

the insert into users and basic? Is that it?

Posted: Tue Aug 01, 2006 11:59 pm
by feyd
That is not what I was talking about.

I want to see what MySQL is getting. The exact query, after all the variables have been processed into the string.

Posted: Wed Aug 02, 2006 12:28 am
by 4Boredom
I guess I dont know how to output a mysql query? If I just echo the sql lines it just pasted my code on the page?

Posted: Wed Aug 02, 2006 12:54 am
by Luke
Do this for all of your queries:
Change all of this

Code: Select all

$sql = mysql_query( "INSERT INTO `users`
                (`first_name`, `last_name`, `email_address`, `username`, `password`, `signup_date`) VALUES
                (\"" . $first_name . "\",\"" . $last_name . "\",\"" . $email_address . "\",\"" . $username . "\",\"" . $password . "\",\"" . time() . "\")" );
To this

Code: Select all

echo ("INSERT INTO `users`
                (`first_name`, `last_name`, `email_address`, `username`, `password`, `signup_date`) VALUES
                (\"" . $first_name . "\",\"" . $last_name . "\",\"" . $email_address . "\",\"" . $username . "\",\"" . $password . "\",\"" . time() . "\")" );
Now you see what is being sent to mysql and you can fix it...

Posted: Wed Aug 02, 2006 2:21 am
by bmcewan
Hi,

there is an error in the sql syntax, you have a missing ) in your second insert,

Code: Select all

(\"" . $gender . "\",\"" . $month . "\",\"" . $day . "\",\"" . $year . "\"");
should read

Code: Select all

(\"" . $gender . "\",\"" . $month . "\",\"" . $day . "\",\"" . $year . "\" ) ");
As a far as query readability goes, i tend not to use escaped double quotes, opting to use singles quotes instead, as shown below.


Code: Select all

$sql = mysql_query("INSERT INTO `users`
                (`first_name`, `last_name`, `email_address`, `username`, `password`, `signup_date`) VALUES
                ('" . $first_name . "', '" . $last_name . "','" . $email_address . "', '" . $username . "', '" . $password . "', '" . time() . "')");

		$sql = mysql_query("INSERT INTO `basic`
                (`gender`, `month`, `day`, `year`) VALUES
                ('" . $gender . "', '" . $month . "','" . $day . "', '" . $year . "')");

cheers.

Posted: Wed Aug 02, 2006 2:35 am
by Luke
much more readable

Posted: Wed Aug 02, 2006 9:00 am
by RobertGonzalez
4boredom, when you get a SQL error, the line it references is the line in the SQL statement, not the PHP file. Just an FYI for future syntax issues.