Jump to content

What is wrong with this code?

aquatradehub

By aquatradehub
in MySQL Help

 Share

Recommended Posts

aquatradehub Member

aquatradehub

Posted
Posted

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

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

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));
}
Link to comment
Share on other sites
aquatradehub Member

aquatradehub

Posted
  • Author
Posted

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

Link to comment
Share on other sites
aquatradehub Member

aquatradehub

Posted
  • Author
Posted

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

Link to comment
Share on other sites
DavidAM Member

DavidAM

Posted
Posted

... 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
Link to comment
Share on other sites
aquatradehub Member

aquatradehub

Posted
  • Author
Posted

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

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.