mysql syntax error

[el-sid]

i get this error when executing the code. still cant figure out what the problem is.any help please

 

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 1

 

<?php
ini_set("display_errors","1");
error_reporting("E_ALL");

include 'include.inc';

set_error_handler("errorHandler");

// Initialize a session
session_start();


// Clear any errors that might have been 
// found previously
$errors = array();



foreach($_POST as $varname => $value)
$formVars[$varname] = trim(clean($value, 50));

$_SESSION["formVars"] = $formVars;

// Validate the surname
if (empty($formVars["surname"])) 
// surname cannot be a null string
$errors["surname"] = 
          "You must enter your surname.";

elseif (strlen($formVars["surname"]) > 50)
$errors["surname"] = 
      "The surname cannot be longer than 50 " .
         "characters";


// Validate the Other names
if (empty($formVars["other_names"]))
// the user's other name cannot be a null string
$errors["other_names"] = 
          "You must enter your other names.";

elseif (strlen($formVars["other_names"]) > 50)
$errors["other_names"] = 
          "The other names cannot be longer than 50 " .
          "characters";

// Validate the Address
if (empty($formVars["address"]))
$errors["address"] = 
          "You must enter your address";

elseif (strlen($formVars["address"]) > 50)
$errors["address"] = 
          "The address cannot be longer than 50 " .
          "characters";

//Validate the phone number
if (empty($formVars["phone_no"]))
$errors["phone_no"] = 
        "You must enter your phone number";

elseif (strlen($formVars["phone_no"]) > 30)
$errors["phone_no"] = 
          "The address cannot be longer than 30 " .
          "characters";

//Validate the email
if (empty($formVars["email"]))
$errors["email"] = 
        "You must enter your email address";


// Validate the date of birth
if (empty($formVars["dob"]))
$errors["dob"] = 
          "You must provide a date of birth.";

elseif (!ereg("^([0-9]{4})-([0-9]{2})-([0-9]{2})$",
        $formVars["dob"], $parts))
// Check the format
$errors["dob"] = 
        "The date of birth should be in the" .
        "format DD/MM/YYYY";


// Validate the gender
if (empty($formVars["gender"]))
$errors["gender"] = 
        "You must specify your gender";

// Validate the date of employment
if (empty($formVars["date_of_emp"]))
$errors["date_of_emp"] = 
        "You must enter your employment date";

elseif (!ereg("^([0-9]{4})-([0-9]{2})-([0-9]{2})$",
        $formVars["date_of_emp"], $parts))
// Check the format
$errors["date_of_emp"] = 
        "The date of employment should be in the" .
        "format DD/MM/YYYY";

if (empty($formVars["level"]))
$errors["level"] = 
        "The level field cannot be empty";

// Only validate username if this is an INSERT
if (!isset($_SESSION["loginUsername"]))
{
    if (empty($formVars["username"]))
    $errors["username"] =
            "You must enter the username";

    elseif (strlen($formVars["username"]) > 10)
    $errors["username"] =
            "The username can be no longer than 10 " .
            "characters";

    else
    {
        // Check if the email address is already in use in
        //  the winestore
        if (!($connection = @ mysql_pconnect($hostName,
                    $username,
                    $password)))
        showerror();

        if (!mysql_select_db($databaseName, $connection))
        showerror();

        $query = "SELECT * FROM login
                  WHERE username = '" .
        $formVars["username"] . "'";

        if (!($result = @ mysql_query ($query,
                    $connection)))
        showerror();

        // Is it taken?
        if (mysql_num_rows($result) == 1)
        $errors["username"] =
              "A salesman already exists with this " .
              "login name.";
              
             
    }
}


// Only validate password if this is an INSERT
// Validate password - between 6 and 8 characters
if (!isset($_SESSION["loginUsername"]) &&
    (strlen($formVars["loginPassword"]) < 6 ||
        strlen($formVars["loginPassword"] > ))
$errors["loginPassword"] = 
         "The password must be between 6 and 8 " .
         "characters in length";




// Now the script has finished the validation, 
// check if there were any errors
if (!empty($errors))
{
    // Store the errors in the session variable
    $_SESSION["errors"] = $errors;
    
    //echo "<script>document.location.href='salesman.register.php'</script>";
    
header('Location: salesman.register.php');
    		exit();
}

// If we made it here, then the data is valid

if (!isset($connection))
{
    if (!($connection = @ mysql_pconnect($hostName,
                $username,
                $password)))
    showerror();

    if (!mysql_select_db($databaseName, $connection))
    showerror();
}


// Is this an update?
if (!isset($_SESSION["loginUsername"]))
{
    $salesman_code = getSalesmanID($_SESSION["loginUsername"], $connection);

    $query = "UPDATE salesman SET ".
     "surname = \"" . $formVars["surname"] . "\", " .
     "other_names = \"" . $formVars["other_names"] . "\", " .
     "address = \"" . $formVars["address"] . "\", " .
     "phone_no = \"" . $formVars["phone_no"] . "\", " .
     "email = \"" . $formVars["email"] . "\", " .
     "dob = \"" . $formVars["dob"] . "\", " .
     "gender = \"" . $formVars["gender"] . "\", " .
     "date_of_emp = \"" . $formVars["date_of_emp"]. "\" " .
     " WHERE salesman_code =" . $salesman_code;
}
else
$query = "INSERT INTO salesman VALUES (NULL, " .
              "\"" . $formVars["surname"] . "\", " .
              "\"" . $formVars["other_names"] . "\", " .
              "\"" . $formVars["address"] . "\", " .
              "\"" . $formVars["phone_no"] . "\", " .
              "\"" . $formVars["email"] . "\", " .
              "\"" . $formVars["dob"] . "\", " .
              "\"" . $formVars["gender"] . "\", " .
              "\"" . $formVars["date_of_emp"] . "\" "
. ")";

// Run the query on the customer table
if (!(@ mysql_query ($query, $connection)))
showerror();


// If this was an INSERT, we need to INSERT
// also into the users table
if (!isset($_SESSION["loginUsername"]))
{
    // Get the customer id that was created
    $salesman_code = @ mysql_insert_id($connection);

    // Use the first two characters of the
    // username as a salt for the password
    $salt = substr($formVars["username"], 0, 2);

    // Create the encrypted password
    $stored_password =
    crypt($formVars["loginPassword"],$salt);

    // Insert a new user into the user table
    $query = "INSERT INTO login
               SET salesman_code = $salesman_code,
                   password = '$stored_password',
                   username = '" . $formVars["username"] . "'".","."
                   level = '" . $formVars["level"] . "'";

    if (!($result = @ mysql_query ($query, $connection)))
    showerror();

    // Log the user into their new account
    //session_register("loginUsername");

    $_SESSION["loginUsername"] = $formVars["username"];
}

// Clear the formVars so a future <form> is blank
unset($_SESSION["formVars"]);
unset($_SESSION["errors"]);

//echo "<script>document.location.href='salesman.mainform.php?salesman_code=$salesman_code'</script>";
header('Location: salesman.mainform.php?salesman_code=$salesman_code');

?>

[phpSensei]

$query = "INSERT INTO salesman VALUES (NULL, " .
              "\"" . $formVars["surname"] . "\", " .
              "\"" . $formVars["other_names"] . "\", " .
              "\"" . $formVars["address"] . "\", " .
              "\"" . $formVars["phone_no"] . "\", " .
              "\"" . $formVars["email"] . "\", " .
              "\"" . $formVars["dob"] . "\", " .
              "\"" . $formVars["gender"] . "\", " .
              "\"" . $formVars["date_of_emp"] . "\" "
. ")";

 

 

You didnt specify the column names

 

INSERT INTO table (column1,column2) values ('sasd','sdasd')..etc

 

or SET

[PFMaBiSmAd]

Since an INSERT query without listing the fields is valid, it is likely phpSensei's reply has nothing to do with the syntax error.

 

You are using an ungainly mess of escaped double-quotes and concatenation in your query that makes it about impossible to tell what the resulting syntax actually is and often results in syntax errors.

 

I recommend echoing the $query so that you can see exactly what is in it and so you can tell which one it is (though you will likely get an error for both of them.)

 

I would use simpler syntax (fewest different elements/changes.) For example, your UPDATE query can be written like this -

 

$query = "UPDATE salesman SET surname = '{$formVars['surname']}',
other_names = '{$formVars['other_names']}',
address = '{$formVars['address']}',
phone_no = '{$formVars['phone_no']}',
email = '{$formVars['email']}',
dob = '{$formVars['dob']}',
gender = '{$formVars['gender']}',
date_of_emp = '{$formVars['date_of_emp']}'
WHERE salesman_code = $salesman_code";

(if $salesman_code is a character data type, add single-quotes around the value in the query)

[phpSensei]

Ah, I did not know that was valid sql actually, I learn something new everyday. I went through the mysql documentation for INSERT and yes it is valid.