Error 1064(Mysql Mismatch)

[schwallie]

Alright guys, it looks as though I have a problem with maybe a reserved name in mysql? I have this code:

 

<?php include('header.php');
include('connect.php');
$firstname1 = $_POST['firstname1'];
$lastname1 = $_POST['lastname1'];
$sex1 = $_POST['sex1'];
$grade1 = $_POST['grade1'];
$sql=mysql_query("select * from information WHERE grade=$grade1 AND
sex='".$_POST['sex1']."' AND
firstname='".$_POST['firstname1']."' AND
lastname='".$_POST['lastname1']."'");
  $numrows=mysql_num_rows($sql);
if($numrows > 0) {
$sql = "UPDATE information SET
ques1='".$_POST['1']."',
ques2='".$_POST['2']."',
ques3='".$_POST['3']."',
ques4='".$_POST['4']."',
ques5='".$_POST['5']."',
ques6='".$_POST['6']."',
ques7='".$_POST['7']."',
ques8='".$_POST['8']."',
ques9='".$_POST['9']."',
ques10='".$_POST['10']."',
ques11='".$_POST['11']."',
ques12='".$_POST['12']."',
ques13='".$_POST['13']."',
ques14='".$_POST['14']."',
ques15='".$_POST['15']."',
ques16='".$_POST['16']."',
ques17='".$_POST['17']."',
ques18='".$_POST['18']."',
ques19='".$_POST['19']."',
ques20='".$_POST['20']."',
ques21='".$_POST['21']."',
ques22='".$_POST['22']."',
ques23='".$_POST['23']."',
ques24='".$_POST['24']."',
ques25='".$_POST['25']."',
ques26='".$_POST['26']."',
ques27='".$_POST['27']."',
ques28='".$_POST['28']."',
ques29='".$_POST['29']."',
ques30='".$_POST['30']."' WHERE
grade='$grade1' AND
sex='$sex1' AND
firstname='$firstname1' AND
lastname='$lastname1'";
mysql_query($sql) or die("<b>1.A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
} else {
$sql2= "INSERT INTO information SET
grade='".$grade1."',
sex='".$sex1."',
firstname='".$firstname1."',
lastname='".$lastname1."',
ques1='".$_POST['1']."',
ques2='".$_POST['2']."',
ques3='".$_POST['3']."',
ques4='".$_POST['4']."',
ques5='".$_POST['5']."',
ques6='".$_POST['6']."',
ques7='".$_POST['7']."',
ques8='".$_POST['8']."',
ques9='".$_POST['9']."',
ques10='".$_POST['10']."',
ques11='".$_POST['11']."',
ques12='".$_POST['12']."',
ques13='".$_POST['13']."',
ques14='".$_POST['14']."',
ques15='".$_POST['15']."',
ques16='".$_POST['16']."',
ques17='".$_POST['17']."',
ques18='".$_POST['18']."',
ques19='".$_POST['19']."',
ques20='".$_POST['20']."',
ques21='".$_POST['21']."',
ques22='".$_POST['22']."',
ques23='".$_POST['23']."',
ques24='".$_POST['24']."',
ques25='".$_POST['25']."',
ques26='".$_POST['26']."',
ques27='".$_POST['27']."',
ques28='".$_POST['28']."',
ques29='".$_POST['29']."',
ques30='".$_POST['30']."'";
mysql_query($sql2) or die("<b>2.A fatal MySQL error occured</b>.\n<br />Query: " . $query . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
echo('<br><br><br><br><center><font size=3>Thank you for submitting a new student</font><font size=2><br>
You will now be returned to the enter information page.
</font>
<META HTTP-EQUIV="refresh" content="1; URL=enter.php">
');
}
?>

 

and I am getting this error:

2.A fatal MySQL error occured.

Query:

Error: (1064) 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 'grade1']; ?>', sex='', firstname='

 

So can anyone maybe help out and tell me why I am getting this?

 

Thank you guys so much!

[trq]

Firstly. Your database wreaks of poor design. Look up some tutorials on database normalization.

 

Secondly, print out your query so you can see the resultant string. Makes it much easier for debugging. eg;

 

echo $sql; exit();

[monk.e.boy]

Firstly. Your database wreaks of poor design. Look up some tutorials on database normalization.

 

 

;D

 

Hahaha, thats funny.

 

You need to split your table into two, a students table and a answers table, the answers will have a foreign key which is the student id.

 

To get the data out of the two tables you do:

 

SELECT name, address, question1
FROM students
JOIN answers ON answers.fk_student_id = student.id
WHERE blah.. blah...

 

You need to think about student Miss Smith, who gets married and is now called Mrs Jones. If your DB is good you need to update one record, if it is crap (see the above example) you need to update hundreds or records 

 

This is called normalization.

 

monk.e.boy

 

 

 

[schwallie]

Hey,

 

I only need to update one record for Miss Smith

 

The table has a row for each (first,last,grade,sex,answers) and if I wanted to change miss smith then I would simply change miss smith's name. The PHP will sort through everything fine still in the coding for what I am doing.

 

Anyways, Thank you guys for the help and I will look into normalization(actually, I am trying to keep this very simple for I am graduating and need to have someone be able to take care of the program when I am gone) and see if that can simplify things anymore.

 

The problem was that my values in my form were not getting sent at all.

 

Thanks!

[monk.e.boy]

Hm.

 

You may be shafted if they change the text of a question, or add a question. What happens to all the people who are in the database and who never answered question 31?

 

Normalization is normal, best to assume the person who takes over from you actually knows how to code  ;) ;D

 

monk.e.boy

[skali]

Change your insert query to....

 

$sql2= "INSERT INTO information (grade,sex,firstname,lastname,......and list all the fields comma seperated) values('$grade1','$sex1','$firstname1',....and all the field values comma separated)";