Error in SQL Syntax?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Error in SQL Syntax?

Post 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!'; 
} 
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

echo the query strings as they would be passed to MySQL.
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Post by 4Boredom »

im confused as to what you mean
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Post 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?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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...
User avatar
bmcewan
Forum Commoner
Posts: 55
Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

much more readable
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply