aquatradehub Posted July 3, 2013 Share Posted July 3, 2013 Hi, I got this script which gets the form results and then writes them to the mysql db. But everytime I try to run it, I get the following error. 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 ''')' at line 3 Here is the script <?php $con=mysqli_connect('localhost', 'username', 'password','database'); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $speciestype = $_POST['fishtype']; $sql="INSERT INTO $speciestype (username, email, speciesName, commonName, scientificName, synonym1, origin, size1, environment, waterChemistry, temperature, feeding, sexing, compatability, temperament, breeding, comments, photo, rewards) VALUES ('$_POST[username]','$_POST[email]','$_POST[fishtype]','$_POST[speciesCommon]','$_POST[speciesScientific]','$_POST[speciesSynonym]','$_POST[origin]','$_POST[size]','$_POST[environment]','$_POST[waterChemistry]','$_POST[temperature]','$_POST[feeding]','$_POST[sexing]','$_POST[compatability]','$_POST[temperament]','$_POST[breeding]','$_POST[comments]','$_POST[photo]', ,'$_POST[rewards]')"; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo " <!DOCTYPE HTML> <html> <head> <meta charset=\"UTF-8\"> <title>Welcome to e-aquatics</title> <!--[if lt IE 9]> <script src=\"http://html5shiv.googlecode.com/svn/trunk/html5.js\"></script> <![endif]--> <!--[if lt IE 9]> <script src=\"_scripts/respond.min.js\"></script> <![endif]--> <link rel=\"stylesheet\" href=\"_css/normalize.css\" /> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no\"> <link rel=\"stylesheet\" href=\"_css/careGuideSubmitForm.css\" /> <!--[if lt IE 8]> <style> legend { display: block; padding: 0; padding-top: 30px; font-weight: bold; font-size: 1.25em; color: #FFD98D; margin: 0 auto; } </style> <![endif]--> <script type=\"text/javascript\"> function open_new_window(URL) { NewWindow = window.open(URL,\"_blank\",\"toolbar=no,menubar=0,status=0,copyhistory=0,scrollbars=no,resizable=0,location=0,Width=500,Height=600\") ; NewWindow.location = URL; } </script> </head> <body> <p>Thank you for submitting a care guide. Your chosen reward will be credited to your account within 24 hours</p> </body> </html> "; mysqli_close($con); ?> Any help is always appreciated Paul - e-quatics.com Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/ Share on other sites More sharing options...
Psycho Posted July 3, 2013 Share Posted July 3, 2013 1. Never, ever use data directly from users in a query without using some process to sanitize it. 2. You are dynamically defining the table based on $speciestype = $_POST['fishtype']; That makes no sense. You are already using fishtype as one of the data values, so why would you have different tables? 3. Assuming the dynamic table name is not a problem, you have two consecutive commas at the end of the query. Don't be afraid to format the query for readability - makes debugging so much easier. 4. For debugging, echo the query out to the page so you can check the query against the error message $sql="INSERT INTO $speciestype (username, email, speciesName, commonName, scientificName, synonym1, origin, size1, environment, waterChemistry, temperature, feeding, sexing, compatability, temperament, breeding, comments, photo, rewards) VALUES ('$_POST[username]', '$_POST[email]', '$_POST[fishtype]', '$_POST[speciesCommon]', '$_POST[speciesScientific]', '$_POST[speciesSynonym]', '$_POST[origin]', '$_POST[size]', '$_POST[environment]', '$_POST[waterChemistry]', '$_POST[temperature]', '$_POST[feeding]', '$_POST[sexing]', '$_POST[compatability]', '$_POST[temperament]', '$_POST[breeding]', '$_POST[comments]', '$_POST[photo]', '$_POST[rewards]')"; if (!mysqli_query($con,$sql)) { die("Query: {$query}<br>Error: " . mysqli_error($con)); } Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439338 Share on other sites More sharing options...
aquatradehub Posted July 3, 2013 Author Share Posted July 3, 2013 Hi Psycho, thanks for your reply. I fixed this, but now have another problem. I already have a list of all the species in the database, and I need for people to be able to select a fish from the menu, and then the rest of the form information written to the correct fish that is already listed in the database. I dont want to use update as it will allow other people to update or overwrite what is already there. So I was thinking along the lines of $sql="UPDATE $speciestype (username, email, speciesName, commonName, scientificName, synonym1, origin, size1, environment, waterChemistry, temperature, feeding, sexing, compatability, temperament, breeding, comments, photo, rewards) VALUES ('$_POST[username]','$_POST[email]','$_POST[fishtype]','$_POST[speciesCommon]', '$_POST[speciesScientific]','$_POST[speciesSynonym]','$_POST[origin]','$_POST[size]', '$_POST[environment]','$_POST[waterChemistry]','$_POST[temperature]','$_POST[feeding]', '$_POST[sexing]','$_POST[compatability]','$_POST[temperament]','$_POST[breeding]','$_POST[comments]', '$_POST[photo]', '$_POST[rewards]') WHERE `commonName`='$_POST[speciesCommon]'"; but now I get this error 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 '(username, email, speciesName, commonName, scientificName, synonym1, origin, siz' at line 1 Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439343 Share on other sites More sharing options...
aquatradehub Posted July 3, 2013 Author Share Posted July 3, 2013 I changed the query to $sql="INSERT INTO $speciestype (username, email, speciesName, commonName, scientificName, synonym1, origin, size1, environment, waterChemistry, temperature, feeding, sexing, compatability, temperament, breeding, comments, photo, rewards) VALUES ('$_POST[username]','$_POST[email]','$_POST[fishtype]','$_POST[speciesCommon]','$_POST[speciesScientific]','$_POST[speciesSynonym]','$_POST[origin]','$_POST[size]','$_POST[environment]','$_POST[waterChemistry]','$_POST[temperature]','$_POST[feeding]','$_POST[sexing]','$_POST[compatability]','$_POST[temperament]','$_POST[breeding]','$_POST[comments]','$_POST[photo]', '$_POST[rewards]') WHERE commonName='$_POST[speciesCommon]'"; and now get this error mysqli_queryError: 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 'WHERE commonName='Floating Frog'' at line 4 can someone pleae help me on this? Many Thanks Paul Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439350 Share on other sites More sharing options...
DavidAM Posted July 4, 2013 Share Posted July 4, 2013 ... I dont want to use update ...You say you don't want to use UPDATE but then ask for help with an UPDATE statement? The syntax for an UPDATE statement is different from the syntax for an INSERT statement I changed the query to $sql="INSERT INTO $speciestype (username, email, speciesName, commonName, scientificName, synonym1, origin, size1, environment, waterChemistry, temperature, feeding, sexing, compatability, temperament, breeding, comments, photo, rewards) VALUES ('$_POST[username]','$_POST[email]','$_POST[fishtype]','$_POST[speciesCommon]','$_POST[speciesScientific]','$_POST[speciesSynonym]','$_POST[origin]','$_POST[size]','$_POST[environment]','$_POST[waterChemistry]','$_POST[temperature]','$_POST[feeding]','$_POST[sexing]','$_POST[compatability]','$_POST[temperament]','$_POST[breeding]','$_POST[comments]','$_POST[photo]', '$_POST[rewards]') WHERE commonName='$_POST[speciesCommon]'"; and now get this error mysqli_queryError: 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 'WHERE commonName='Floating Frog'' at line 4 An INSERT statement does not have a WHERE clause. By the way: 1. Never, ever use data directly from users in a query without using some process to sanitize it. You seem to have completely ignored this VERY IMPORTANT piece of advice Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439362 Share on other sites More sharing options...
aquatradehub Posted July 4, 2013 Author Share Posted July 4, 2013 I havent ignored the advice, I just havent got round to it yet, I was trying to get the other problems sorted first. I know I made a post with the UPDATE statement which was a mistake. If you look at what I posted after, I used an INSERT INTO statement. What I want to know is if there is a way to insert the data into the row, where commonName='$_POST[speciesCommon]'";, if I cant use the WHERE statement, is there another way to do it? Thanks Paul Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439450 Share on other sites More sharing options...
aquatradehub Posted July 4, 2013 Author Share Posted July 4, 2013 k, its cool. I just placed all species into one table then set common Name as Primary Quote Link to comment https://forums.phpfreaks.com/topic/279841-what-is-wrong-with-this-code/#findComment-1439462 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.