Colton.Wagner Posted November 1, 2012 Share Posted November 1, 2012 So I have a form that I am making submit itself to database so that it can be pulled up at a later time. Basically the problem I am having is that Mysql sends me an error of: MYSQL 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 '1', '2', '3', '2', '1', '0', '1', '2', '3', '2', '1', '0', '1', '2', '3', '2', '' at line 1 The numbers you see above are the actual data that needs to be entered. The code that is causing this issue can be seen here: $query = mysql_query("INSERT INTO vanderbilt_adhd_diagnostic (id, Patient_Name, Date_Recieved, Date_Of_Birth, Age, Grade, Attention_To_Detail, Sustaining_Activites, Listening, Following_Instructions, Organization, Mental_Effort, Loses_Items,Easily_Distracted, Forgetful, Fidgets, Leaves_Seat, Runs_About, Difficulty_Engaging, On_The_Go, Talks_Excessivly, Blurts_Answers, Difficulty_Waiting, Interrupts, Argues, Loses_Temper, Refuses_To_Comply, Deliberately_Annoys, Blames_Others, Touchy, Angry, Spiteful, Bullies, Starts_Fights, Cons_Others, Skips_School, Physically_Cruel, Stolen_Items, Destroys_Property, Uses_Weapons, Cruel_Animals, Arson, Forced_Entry, Stays_Out, Run_Away, Rape, Fearful, Afraid_Of_New_Things, Worthless, Guilty, Lonely, Depressed, Self_Conscious) VALUES ('', '$Patient_Name', '$Date_Recieved', '$Date_Of_Birth', '$Age', '$Grade', '$Attention_To_Detail', '$Sustaining_Activites', '$Listening', '$Following_Instructions', '$Organization', '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', '$Forgetful', '$Fidgets', '$Leaves_Seat', '$Runs_About', '$Difficulty_Engaging', '$On_The_Go', '$Talks_Excessivly', '$Blurts_Answers', '$Difficulty_Waiting', '$Interrupts', '$Argues', '$Loses_Temper', '$Refuses_To_Comply', '$Deliberately_Annoys', '$Blames_Others', '$Touchy', '$Angry', '$Spiteful', '$Bullies', '$Starts_Fights', '$Cons_Others', '$Skips_School', '$Physically_Cruel', '$Stolen_Items', '$Destroys_Property', '$Uses_Weapons', '$Cruel_Animals', '$Arson', '$Forced_Entry', '$Stays_Out', '$Run_Away', '$Rape', '$Fearful', '$Afraid_Of_New_Things', '$Worthless', '$Guilty', '$Lonely', '$Depressed', '$Self_Conscious')"); Obviously I have something syntaxually wrong. If someone could give me a little assistance that would be wonderful! Thank you for your time in advanced. Quote Link to comment Share on other sites More sharing options...
Psycho Posted November 1, 2012 Share Posted November 1, 2012 1. If ID is an auto-increment field then just leave it out of your insert query 2. Create the query as a string variable first. So, if there is an error you can echo the whole query to the page. The part of the query displayed int he error really doesn't give any valuable information. 3. Format your query so it is easy to "read". Put in line breaks and spacing to give context to the data Having said that I don't see a syntax error that stands out. The last value in the error seems to be an empty string. That may not be what you intended, but I don't think it would cause a syntax error. Try this. $query = "INSERT INTO vanderbilt_adhd_diagnostic (Patient_Name, Date_Recieved, Date_Of_Birth, Age, Grade, Attention_To_Detail, Sustaining_Activites, Listening, Following_Instructions, Organization, Mental_Effort, Loses_Items, Easily_Distracted, Forgetful, Fidgets, Leaves_Seat, Runs_About, Difficulty_Engaging, On_The_Go, Talks_Excessivly, Blurts_Answers, Difficulty_Waiting, Interrupts, Argues, Loses_Temper, Refuses_To_Comply, Deliberately_Annoys, Blames_Others, Touchy, Angry, Spiteful, Bullies, Starts_Fights, Cons_Others, Skips_School, Physically_Cruel, Stolen_Items, Destroys_Property, Uses_Weapons, Cruel_Animals, Arson, Forced_Entry, Stays_Out, Run_Away, Rape, Fearful, Afraid_Of_New_Things, Worthless, Guilty, Lonely, Depressed, Self_Conscious) VALUES ('$Patient_Name', '$Date_Recieved', '$Date_Of_Birth', '$Age', '$Grade', '$Attention_To_Detail', '$Sustaining_Activites', '$Listening', '$Following_Instructions', '$Organization', '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', '$Forgetful', '$Fidgets', '$Leaves_Seat', '$Runs_About', '$Difficulty_Engaging', '$On_The_Go', '$Talks_Excessivly', '$Blurts_Answers', '$Difficulty_Waiting', '$Interrupts', '$Argues', '$Loses_Temper', '$Refuses_To_Comply', '$Deliberately_Annoys', '$Blames_Others', '$Touchy', '$Angry', '$Spiteful', '$Bullies', '$Starts_Fights', '$Cons_Others', '$Skips_School', '$Physically_Cruel', '$Stolen_Items', '$Destroys_Property', '$Uses_Weapons', '$Cruel_Animals', '$Arson', '$Forced_Entry', '$Stays_Out', '$Run_Away', '$Rape', '$Fearful', '$Afraid_Of_New_Things', '$Worthless', '$Guilty', '$Lonely', '$Depressed', '$Self_Conscious')"; $result = mysql_query($query) or die("Query: $query<br>Error: " . mysql_error()); Quote Link to comment Share on other sites More sharing options...
DavidAM Posted November 1, 2012 Share Posted November 1, 2012 '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', You are missing a quote mark after $Loses_Items. There may be others, it is difficult to read the line. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 You know, if people don't stop trying to insert nothing into auto_inc fields, I'm going to cry! if you were to change your line of code that starts $query to this : $sql = <<<QRY INSERT INTO vanderbilt_adhd_diagnostic ( id, Patient_Name, Date_Recieved, Date_Of_Birth, Age, Grade, Attention_To_Detail, Sustaining_Activites, Listening, Following_Instructions, Organization, Mental_Effort, Loses_Items, Easily_Distracted, Forgetful, Fidgets, Leaves_Seat, Runs_About, Difficulty_Engaging, On_The_Go, Talks_Excessivly, Blurts_Answers, Difficulty_Waiting, Interrupts, Argues, Loses_Temper, Refuses_To_Comply, Deliberately_Annoys, Blames_Others, Touchy, Angry, Spiteful, Bullies, Starts_Fights, Cons_Others, Skips_School, Physically_Cruel, Stolen_Items, Destroys_Property, Uses_Weapons, Cruel_Animals, Arson, Forced_Entry, Stays_Out, Run_Away, Rape, Fearful, Afraid_Of_New_Things, Worthless, Guilty, Lonely, Depressed, Self_Conscious ) VALUES ( '', '$Patient_Name', '$Date_Recieved', '$Date_Of_Birth', '$Age', '$Grade', '$Attention_To_Detail', '$Sustaining_Activites', '$Listening', '$Following_Instructions', '$Organization', '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', '$Forgetful', '$Fidgets', '$Leaves_Seat', '$Runs_About', '$Difficulty_Engaging', '$On_The_Go', '$Talks_Excessivly', '$Blurts_Answers', '$Difficulty_Waiting', '$Interrupts', '$Argues', '$Loses_Temper', '$Refuses_To_Comply', '$Deliberately_Annoys', '$Blames_Others', '$Touchy', '$Angry', '$Spiteful', '$Bullies', '$Starts_Fights', '$Cons_Others', '$Skips_School', '$Physically_Cruel', '$Stolen_Items', '$Destroys_Property', '$Uses_Weapons', '$Cruel_Animals', '$Arson', '$Forced_Entry', '$Stays_Out', '$Run_Away', '$Rape', '$Fearful', '$Afraid_Of_New_Things', '$Worthless', '$Guilty', '$Lonely', '$Depressed', '$Self_Conscious' ) QRY; $query = mysql_query($sql) or die(mysql_error().",<br /><br />Occured when trying to run <br />$sql"); then not only will the error have a more practical line number refference (showing you that the $Loses_Items line is the one that is a problem as you have not closed the string wrapper - as DavidAM has pointed out) but you will also see the query exactly as it is being sent to the database, making it easier again to find what the problem is. You do seriously need to look into normalising your structure though. This is not even close to being well designed. Quote Link to comment Share on other sites More sharing options...
Colton.Wagner Posted November 1, 2012 Author Share Posted November 1, 2012 1. If ID is an auto-increment field then just leave it out of your insert query 2. Create the query as a string variable first. So, if there is an error you can echo the whole query to the page. The part of the query displayed int he error really doesn't give any valuable information. 3. Format your query so it is easy to "read". Put in line breaks and spacing to give context to the data Having said that I don't see a syntax error that stands out. The last value in the error seems to be an empty string. That may not be what you intended, but I don't think it would cause a syntax error. Try this. $query = "INSERT INTO vanderbilt_adhd_diagnostic (Patient_Name, Date_Recieved, Date_Of_Birth, Age, Grade, Attention_To_Detail, Sustaining_Activites, Listening, Following_Instructions, Organization, Mental_Effort, Loses_Items, Easily_Distracted, Forgetful, Fidgets, Leaves_Seat, Runs_About, Difficulty_Engaging, On_The_Go, Talks_Excessivly, Blurts_Answers, Difficulty_Waiting, Interrupts, Argues, Loses_Temper, Refuses_To_Comply, Deliberately_Annoys, Blames_Others, Touchy, Angry, Spiteful, Bullies, Starts_Fights, Cons_Others, Skips_School, Physically_Cruel, Stolen_Items, Destroys_Property, Uses_Weapons, Cruel_Animals, Arson, Forced_Entry, Stays_Out, Run_Away, Rape, Fearful, Afraid_Of_New_Things, Worthless, Guilty, Lonely, Depressed, Self_Conscious) VALUES ('$Patient_Name', '$Date_Recieved', '$Date_Of_Birth', '$Age', '$Grade', '$Attention_To_Detail', '$Sustaining_Activites', '$Listening', '$Following_Instructions', '$Organization', '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', '$Forgetful', '$Fidgets', '$Leaves_Seat', '$Runs_About', '$Difficulty_Engaging', '$On_The_Go', '$Talks_Excessivly', '$Blurts_Answers', '$Difficulty_Waiting', '$Interrupts', '$Argues', '$Loses_Temper', '$Refuses_To_Comply', '$Deliberately_Annoys', '$Blames_Others', '$Touchy', '$Angry', '$Spiteful', '$Bullies', '$Starts_Fights', '$Cons_Others', '$Skips_School', '$Physically_Cruel', '$Stolen_Items', '$Destroys_Property', '$Uses_Weapons', '$Cruel_Animals', '$Arson', '$Forced_Entry', '$Stays_Out', '$Run_Away', '$Rape', '$Fearful', '$Afraid_Of_New_Things', '$Worthless', '$Guilty', '$Lonely', '$Depressed', '$Self_Conscious')"; $result = mysql_query($query) or die("Query: $query<br>Error: " . mysql_error()); Thank you for the advice on the structure of my code. I will take this into account when writing in the future as well. You are missing a quote mark after $Loses_Items. There may be others, it is difficult to read the line. Thank you this solved my problem. You know, if people don't stop trying to insert nothing into auto_inc fields, I'm going to cry! if you were to change your line of code that starts $query to this : $sql = <<<QRY INSERT INTO vanderbilt_adhd_diagnostic ( id, Patient_Name, Date_Recieved, Date_Of_Birth, Age, Grade, Attention_To_Detail, Sustaining_Activites, Listening, Following_Instructions, Organization, Mental_Effort, Loses_Items, Easily_Distracted, Forgetful, Fidgets, Leaves_Seat, Runs_About, Difficulty_Engaging, On_The_Go, Talks_Excessivly, Blurts_Answers, Difficulty_Waiting, Interrupts, Argues, Loses_Temper, Refuses_To_Comply, Deliberately_Annoys, Blames_Others, Touchy, Angry, Spiteful, Bullies, Starts_Fights, Cons_Others, Skips_School, Physically_Cruel, Stolen_Items, Destroys_Property, Uses_Weapons, Cruel_Animals, Arson, Forced_Entry, Stays_Out, Run_Away, Rape, Fearful, Afraid_Of_New_Things, Worthless, Guilty, Lonely, Depressed, Self_Conscious ) VALUES ( '', '$Patient_Name', '$Date_Recieved', '$Date_Of_Birth', '$Age', '$Grade', '$Attention_To_Detail', '$Sustaining_Activites', '$Listening', '$Following_Instructions', '$Organization', '$Mental_Effort', '$Loses_Items, '$Easily_Distracted', '$Forgetful', '$Fidgets', '$Leaves_Seat', '$Runs_About', '$Difficulty_Engaging', '$On_The_Go', '$Talks_Excessivly', '$Blurts_Answers', '$Difficulty_Waiting', '$Interrupts', '$Argues', '$Loses_Temper', '$Refuses_To_Comply', '$Deliberately_Annoys', '$Blames_Others', '$Touchy', '$Angry', '$Spiteful', '$Bullies', '$Starts_Fights', '$Cons_Others', '$Skips_School', '$Physically_Cruel', '$Stolen_Items', '$Destroys_Property', '$Uses_Weapons', '$Cruel_Animals', '$Arson', '$Forced_Entry', '$Stays_Out', '$Run_Away', '$Rape', '$Fearful', '$Afraid_Of_New_Things', '$Worthless', '$Guilty', '$Lonely', '$Depressed', '$Self_Conscious' ) QRY; $query = mysql_query($sql) or die(mysql_error().",<br /><br />Occured when trying to run <br />$sql"); then not only will the error have a more practical line number refference (showing you that the $Loses_Items line is the one that is a problem as you have not closed the string wrapper - as DavidAM has pointed out) but you will also see the query exactly as it is being sent to the database, making it easier again to find what the problem is. You do seriously need to look into normalising your structure though. This is not even close to being well designed. I will be sure to remove the blank AI id field as it is pointless to be included. As for the structure of my coding I will be sure to break it down in to multiple lines so that finding errors and all around readability will be easy. Thank you for the advice. Quote Link to comment Share on other sites More sharing options...
akphidelt2007 Posted November 1, 2012 Share Posted November 1, 2012 Just for future reference if you ever want to put a value in to the auto-increment field without rewriting the auto-number or causing a duplication error, use NULL with out quotation marks as your value. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 Just for future reference if you ever want to put a value in to the auto-increment field without rewriting the auto-number or causing a duplication error, use NULL with out quotation marks as your value. No. you never "put" a value into an auto_inc field - ever. (just for future refference). @ OP - I was talking about your table structure, not you code. Quote Link to comment Share on other sites More sharing options...
Christian F. Posted November 1, 2012 Share Posted November 1, 2012 (edited) Here's a little trick to make such easier in the future: // Set up the fields to be retrieved and type cast as integers. $fields = array ('Attention_To_Detail', 'Sustaining_Activites', 'Listening', 'Following_Instructions', 'Organization', 'Mental_Effort', ... ETC); // Ready the array to hold the input values. $values = array (); // Loop through all of the symptoms to fetch and typecast the associated input value. foreach ($field as $item) { $values[] = intval ($_GET[$item]); } // Make SQL-compatible strings out of the arrays. $fields = '`'.implode ('`, `', $fields).'`'; $values = implode (',', $values); // Create the query, and escape any non-numerical data. $query = "INSERT INTO table (`name`, ...., $Fields) VALUES ('%s', ..., $Values)"; $query = sprintf ($query, mysql_real_escape_string ($name), ...); This assumes that you've named all of the input fields the same as the database fields, and that everything after age is a numerical value. Edited November 1, 2012 by Christian F. Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 2, 2012 Share Posted November 2, 2012 Better yet, normalize that data!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.