Error: You have an error in your SQL syntax.

[buk01]

Hi there,

I have created a registration php and it seems to have a problem. It connects to the MySQL server fine but doesnt inserts the information i typed in the form. Im planning on placing different information in the same form to different tables.

Here is my code:

<?PHP
$hostname="localhost";
$database="My Database";
$username="My User Name";
$dbpassword="My Password";

$title=$_POST['title'];
$firstname=$_POST['firstname'];
$lastname= $_POST['lastname'];
$dob=$_POST['dob'];
$nationality = $_POST['nationality'];
$gender=$_POST['gender'];
$addressline1=$_POST['addressline1'];
$addressline2=$_POST['addressline2'];
$city=$_POST['city'];
$state=$_POST['state'];
$postalcode=$_POST['postalcode'];
$country=$_POST['country'];
$mobile =$_POST['mobile'];
$email=$_POST['email'];
$presentuni=$_POST['presentuni'];
$levelcourse1=$_POST['levelcourse1'];
$titlecourse1=$_POST['titlecourse1'];
$previousuni=$_POST['previousuni'];
$levelcourse2=$_POST['levelcourse2'];
$titlecourse2=$_POST['titlecourse2'];
$experience=$_POST['experience'];

mysql_connect($hostname,$username,$dbpassword);
@mysql_select_db($database)or die("Unable to select database");

$query= "INSERT INTO Personal Information VALUES('','$title', '$firstname', '$lastname', '$dob', '$nationality', '$gender')";
mysql_query($query) or die( "Error: " . mysql_error() );

$query2= "INSERT INTO Contacts VALUES('LAST_INSERT_ID()', '$addressline1', '$addressline2', '$city', '$state', '$postalcode', '$country', '$mobile', '$email')";
mysql_query($query2) or die( "Error: " . mysql_error() );

$query3= "INSERT INTO Academic Records VALUES('LAST_INSERT_ID()', '$presentuni', '$levelcourse1', '$titlecourse1', '$previousuni', '$levelcourse2', '$titlecourse2')";
mysql_query($query3) or die( "Error: " . mysql_error() );

$query4 = "INSERT INTO Work Experience VALUES('LAST_INSERT_ID()', '$experience')";
mysql_query($query4) or die( "Error: " . mysql_error() );

mysql_close();
?>

This is the error i get:
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 'Information VALUES('','', '', '', '', '', '')' at line 1

Can you find what i'm doing wrong? PLEASE HELP... I HAVE TRIED EVERYTHING... being working on it for days now.

[jatar_k]

Welcome to WebmasterWorld buk01,

all of your values are empty and therefore empty values are ggetting used in your insert

are you filling everything in on your form?

you are in desperate need of verifying user submitted data

[buk01]

Thanks for your reply...
When I fill the form it displays:

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 'Information VALUES('','Mr', 'mark', 'moses', '04/11/1989', 'British', 'Male')' at line 1

In the database, All the values were set to VARCHAR.

[jatar_k]

I imagine your first value that is blank is the id

change your insert to use the col names

insert into tablename (col1, col2) values (val1, val2)

and omit the id, which I imagine is auto increment, it will take care of itself

[buk01]

Thank you! I would try that now and get back to you!...

[buk01]

I have done what you suggested but it is still giving the same error. This is what i have done:

$query= "INSERT INTO Personal Information ('title', 'firstname', 'lastname', 'dob', 'nationality', 'gender')
VALUES('$title', '$firstname', '$lastname', '$dob', '$nationality', '$gender')";

mysql_query($query) or die( "Error: " . mysql_error() );

$query2= "INSERT INTO Contacts ('addressline1', 'addressline2', 'city', 'state', 'postalcode', 'country', 'mobile', 'email')
VALUES('LAST_INSERT_ID()', '$addressline1', '$addressline2', '$city', '$state', '$postalcode', '$country', '$mobile', '$email')";
mysql_query($query2) or die( "Error: " . mysql_error() );

$query3= "INSERT INTO Academic Records ('presentuni', 'levelcourse1', 'titlecourse1', 'previousuni', 'levelcourse2', 'titlecourse2' ) VALUES('LAST_INSERT_ID()', '$presentuni', '$levelcourse1', '$titlecourse1', '$previousuni', '$levelcourse2', '$titlecourse2')";
mysql_query($query3) or die( "Error: " . mysql_error() );

$query4 = "INSERT INTO Work Experience ('experience') VALUES('LAST_INSERT_ID()', '$experience')";
mysql_query($query4) or die( "Error: " . mysql_error() );

[jatar_k]

then you still have an error, try echoing the whole query and see if you can see it

$query= "INSERT INTO Personal Information ('title', 'firstname', 'lastname', 'dob', 'nationality', 'gender')
VALUES('$title', '$firstname', '$lastname', '$dob', '$nationality', '$gender')";

echo '<p>',$query;
die;

[LifeinAsia]

My guess would be the space between Personal and Information. Maybe MySQL/PHP are more forgiving than most, but in general it's bad form to put spaces in table/field names, precisely for reasons like this. You'll probably need to use 'Personal Information' or some other way to denote that there is a space in the table name.

The better solution would be to change the table name to Personal_Information or PersonalInformation.

[buk01]

I have changed the name and used the echo you guys suggested. It brings up a different message this time. I doesnt state if its error or not.

INSERT INTO Personal_Information ('title', 'firstname', 'lastname', 'dob', 'nationality', 'gender') VALUES('Mr', 'Buki', 'moses', '04/11/1989', 'British', 'Male')

Here is my code:

$query= "INSERT INTO Personal_Information ('title', 'firstname', 'lastname', 'dob', 'nationality', 'gender')
VALUES('$title', '$firstname', '$lastname', '$dob', '$nationality', '$gender')";

echo '<p>',$query;
die;
$query2= "INSERT INTO Contacts ('addressline1', 'addressline2', 'city', 'state', 'postalcode', 'country', 'mobile', 'email')
VALUES('LAST_INSERT_ID()', '$addressline1', '$addressline2', '$city', '$state', '$postalcode', '$country', '$mobile', '$email')";
mysql_query($query2) or die( "Error: " . mysql_error() );

$query3= "INSERT INTO 'Academic_Records' ('presentuni', 'levelcourse1', 'titlecourse1', 'previousuni', 'levelcourse2', 'titlecourse2' ) VALUES('LAST_INSERT_ID()', '$presentuni', '$levelcourse1', '$titlecourse1', '$previousuni', '$levelcourse2', '$titlecourse2')";
mysql_query($query3) or die( "Error: " . mysql_error() );

$query4 = "INSERT INTO 'Work_Experience' ('experience') VALUES('LAST_INSERT_ID()', '$experience')";
mysql_query($query4) or die( "Error: " . mysql_error() );

The information i typed into the form isnt going to the MySQL server.

[LifeinAsia]

What's the error message?

[buk01]

INSERT INTO Personal_Information ('title', 'firstname', 'lastname', 'dob', 'nationality', 'gender') VALUES('Mr', 'Buki', 'moses', '04/11/1989', 'British', 'Male')

[LifeinAsia]

That's not an error message. That's the echo of what you told it to echo.

[jatar_k]

it should prolly look like this
INSERT INTO Personal_Information (title, firstname, lastname, dob, nationality, gender) VALUES('Mr', 'Buki', 'moses', '04/11/1989', 'British', 'Male')

no single quotes around fieldnames

then you can comment out the echo and the die

nice catch by the way LifeinAsia

[LifeinAsia]

Well, sometimes you just need a fresh set of eyes from someone who doesn't know anything about PHP. :)

[buk01]

I have fix that now... lol

Its now giving another error message:

Error: Column count doesn't match value count at row 1

Here is the new code:

$query= "INSERT INTO Personal_Information (user_id, title, firstname, lastname, dob, nationality, gender)
VALUES('', '$title', '$firstname', '$lastname', '$dob', '$nationality', '$gender')";
mysql_query($query) or die( "Error: " . mysql_error() );

$query2= "INSERT INTO Contacts (addressline1, addressline2, city, state, postalcode, country, mobile, email)
VALUES('LAST_INSERT_ID()', '$addressline1', '$addressline2', '$city', '$state', '$postalcode', '$country', '$mobile', '$email')";
mysql_query($query2) or die( "Error: " . mysql_error() );

$query3= "INSERT INTO Academic_Records (presentuni, levelcourse1, titlecourse1, previousuni, levelcourse2, titlecourse2 ) VALUES('LAST_INSERT_ID()', '$presentuni', '$levelcourse1', '$titlecourse1', '$previousuni', '$levelcourse2', '$titlecourse2')";
mysql_query($query3) or die( "Error: " . mysql_error() );

$query4 = "INSERT INTO Work_Experience (experience) VALUES('LAST_INSERT_ID()', '$experience')";
mysql_query($query4) or die( "Error: " . mysql_error() );

mysql_close();

i have tried it with and without the "user_id" which is a primary key and set to auto-increment. It still gives the same error stated above....

Please hellppp... Sorry for being a pain guys

[jatar_k]

a couple pointers

expand you or die statements to give you something you can use. If you have many or die statements on the same page and they all spit out i the same format you will waste a lot of time

mysql_query($query) or die ("<p>died: $q<br>" . mysql_error());

if you include the query in each or die you will get the actual query that was sent and the error it caused, very useful

you are using LAST_INSERT_ID() all over, do any of those other tables have ids? if so you would be using that id and if you meant to carry the user_id through out you could be causing a bug.

directly after your first insert, why not use something like

$myawesomeid = mysql_insert_id();

then use that var though out

do you have any idea which query is dying?

[buk01]

Yes i'm using the user_id through out. I used it as a primary key in the personal_information table and used it (with the same name) in other tables as the index key. It is set to auto-increment. Type = INT.

Are you now saying i should:
*change the name of the user_id in other tables?
*use $id = mysql_insert_id(); after the INSERT INTO personal_information?

I have no idea which query is dying... it looks fine with me... i have even Googled it and I have done everything in the tutorials.

[buk01]

IT WORKS!
LOOOOL YOU GUYS ARE THE BEST!
I LOVE YOU!

[buk01]

Just one last question:

Is it possible to link the user to a new page after they filled out the form?

[jatar_k]

>> I have no idea which query is dying

which is why I mentioned you should expand your 'or die's so you can figure it out

>> change the name of the user_id in other tables?
no

>> use $id = mysql_insert_id(); after the INSERT INTO personal_information?

yes and then use that variable in all your subsequent queries

>> Is it possible to link the user to a new page after they filled out the form?

yes, you could forward them using the header function as long as there is no output from your script

[buk01]

THANK YOU JATAR_K YOU HAVE BEEN WONDERFULL... I LOVE YOU MAN... I HAVE BEEN WORKING ON THIS FOR AGESS! LOOOL

YOU KNOW THAT FEELING WHEN YOU GOT SOMETHING AFTER SOO LONG YOU HAVE WORKED HARD FOR IT?

yep i'm having that feeling now... Probably go for a night out now loool ... Thanks to you wonderfull people...

llool i actually feel proud of myself now... after 2 days of feeling like an idoit lool...

Thank you!