[Resolved] INSERT INTO syntax error

[Wendy_B]

Here is the code that I am using to insert data into a db, named FeatProp, that was entered in a form on another page. The form is using the Post method.

$sql = "INSERT INTO FeatProperty ('MLSNum', 'Address', 'Desc', 'Price', 'Photo')
VALUES ('$POST[MLSNum]', '$POST[Address]', '$POST[Desc]', '$POST[Price]', '$_POST[Ph1]')";

$sql_result = mysql_query($sql, $conn) or die ('This was not successful because ' . mysql_error());

the rest of the code goes here!

I get this error:
This was not successful because 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 ''MLSNum', 'Address', 'Desc', 'Price', 'Photo') VALUES ('', ''

I have been workin on this for hours and cannot find the error!

Thanks in advance.

WendyB :confused

[eerok]

Try removing the quotes around your field list, as in:

$sql = "INSERT INTO FeatProperty (MLSNum, Address, Desc, Price, Photo)
VALUES ('$_POST[MLSNum]', '$_POST[Address]', '$_POST[Desc]', '$_POST[Price]', '$_POST[Ph1]')";

[Wendy_B]

errok,

That did not work, I still go the error:

This was not successful because 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 'Desc, Price, Photo) VALUES ('', '', '', '', '')' at line 1

Server is Linux

Apache version 1.3.33 (Unix)

WendyB

[pescott]

How about stripping the spaces from between the fieldnames... like so, and trim (htmlspecialchars($_POST[Value])).

$MLSNum = trim (htmlspecialchars($_POST[MLSNum]));
$Address = trim (htmlspecialchars($_POST[Address]));
$Desc = trim (htmlspecialchars($_POST[Desc]));
$Price = trim (htmlspecialchars($_POST[Price]));
$Ph1 = trim (htmlspecialchars($_POST[Ph1]));

$sql = "INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo) 
VALUES ('$MLSNum', '$Address', '$Desc', '$Price', '$Ph1')";

And make sure that the field names are spelled exactly the same, including CAPS and lowercase symbols. In my opinion it is best to use lowercase for fieldnames, less chance of case errors. Let me know what happens

[searain]

What is the data type of the field "price"?

If it is a float etc., you should use, no '' wrap up $Price

VALUES ('$MLSNum', '$Address', '$Desc', $Price, '$Ph1')";

[bradgrafelman]

First of all, the data isn't being inserted because there is no data.

Notice the error? "VALUES ('', '', '', '', '')' at line 1 "

Does '' look like your data?

Double check the field names. Also, are you sure you are POST'ing these variables from a form?

Also, without quotes (i.e. $POST['test']), you are telling PHP to look for a constant. It will usually fail and fall back to using it as a string, but still, you should use quotes in your $POST[] statements, such as:

if(!get_magic_quotes_gpc()) {
$MLSNum = mysql_real_escape_string($_POST['MLSNum'], $connection);
$Address = mysql_real_escape_string($_POST['Address'], $connection);
$Desc = mysql_real_escape_string($_POST['Desc'], $connection);
$Price = mysql_real_escape_string($_POST['Price'], $connection);
$Ph1 = mysql_real_escape_string($_POST['Ph1'], $connection);
} else {
$MLSNum = mysql_real_escape_string(stripslashes($_POST['MLSNum']), $connection);
$Address = mysql_real_escape_string(stripslashes($_POST['Address']), $connection);
$Desc = mysql_real_escape_string(stripslashes($_POST['Desc']), $connection);
$Price = mysql_real_escape_string(stripslashes($_POST['Price']), $connection);
$Ph1 = mysql_real_escape_string(stripslashes($_POST['Ph1']), $connection);
}

$sql = "INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo) 
VALUES ('".$MLSNum."', '".$Address."', '".$Desc."', '".$Price."', '".$Ph1."')";

NOTE: I also added some escape functions to help protect against SQL injections. Also take sarain's post about column types into consideration.

EDIT: Also note that indeces of the superglobals ($REQUEST, $GET, $_POST, etc.) are CASE SENSITIVE:

$POST['foo'] is not the same as $POST['FOO']

[Wendy_B]

bradgrafelman

Well, that worked ... kindof

At least it does not fail until the next line:

$sql_result = mysql_query($query, $conn) or die ('This was not successful because ' . mysql_error());
if ($sql_result) {
echo'Success!';
}
else {
echo 'there were some problems';
}

I get the error message:

This was not successful because 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 'Desc,Price,Photo) VALUES ('576098', '123 e lake rd', 'great l

If I change any thing in the above code, it totally fails!

Could it be because the Ph1 field is a file field (It holds a file name such as 576098.jpg)?😕

[bradgrafelman]

Time to debug your MySQL query.

Echo the $sql variable after you set it and paste the contents here.

[Wendy_B]

here is the code:

<?php
//create the connection
$dbh=mysql_connect ("localhost", "bristolr", "breal5") or die ('I cannot connect to the database because: ' . mysql_error());
echo "connection1";

//connect to the database 
$conn = mysql_connect("localhost","bristolr_wendy","blackstone") or die('I cannot connect because1: ' .mysql_error());

echo "connection2";

//insert the data

if(!get_magic_quotes_gpc()) { 
$MLSNum = mysql_real_escape_string($_POST['MLSNum'], $conn); 
$Address = mysql_real_escape_string($_POST['Address'], $conn); 
$Desc = mysql_real_escape_string($_POST['Desc'], $conn); 
$Price = mysql_real_escape_string($_POST['Price'], $conn); 
$Ph1 = mysql_real_escape_string($_POST['Ph1'], $conn); 
} else { 
$MLSNum =  mysql_real_escape_string(stripslashes($_POST['MLSNum']), $conn); 
$Address =  mysql_real_escape_string(stripslashes($_POST['Address']), $conn); 
$Desc =  mysql_real_escape_string(stripslashes($_POST['Desc']), $conn); 
$Price =  mysql_real_escape_string(stripslashes($_POST['Price']), $conn); 
$Ph1 =  mysql_real_escape_string(stripslashes($_POST['Ph1']), $conn); 
} 

echo "$MLSNum, $Address, $Desc, $Price, $Ph1 <br>";
$sql = "INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo) 
VALUES ('".$MLSNum."', '".$Address."', '".$Desc."', '".$Price."', '".$Ph1."')";

echo "<br>$sql<br>";
echo "$MLSNum, $Price";
$sql_result = mysql_query($query, $conn) or die ('This was not successful because ' . mysql_error());
if ($sql_result) {
echo'Success!';
}
else {
echo 'there were some problems';
}
?>

here are the results of the code:

connection1connection2576098, 123 e lake rd, great lake access, 225000,

INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo) VALUES ('576098', '123 e lake rd', 'great lake access', '225000', '')
576098, 225000This was not successful because Query was empty

[bradgrafelman]

Two things:

1. This code:

$sql_result = mysql_query($query, $conn) or die ('This was not successful because ' . mysql_error()); 

uses a variable not set ($query). Shouldn't that be $sql ?

2. Look at the output: "'225000', '')". That means that $Ph1 is not set or is empty. Should it be?

[Wendy_B]

when I change $query to $sql I get this:

This was not successful because 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 'Desc,Price,Photo) VALUES ('576098', '123 e lake rd', 'great

right now I am more concerned about getting the INSERT INTO command to work then I will work on getting the Ph1 field to echo.

Do you want the entire code (both pages?)

[bradgrafelman]

INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo) VALUES ('576098', '123 e lake rd', 'great lake access', '225000', '')

Either I'm really tired or I'm missing something, because that query looks fine to me... can we fill in a dummy value for Ph1 to see if that's it? Maybe the field type is DEFAULT NOT NULL or something. Change $Ph1 to 'test' in your query, can ya?

[eerok]

If the troublesome field is a filename, I'd suspect the dot is messing it up.

However, that field is not even necessary: if the mls num is 576098 and the filename is 576098.jpg then storing it seems redundant.

[Wendy_B]

took PH1 totally out of form and then out of the code, then changed the
$sql_result = mysql_query($sql, $conn)
to:
$sql_result = mysql_query($query, $conn)

Resaved everything and uploaded. Restarted browser so that nothing was being taken from cache and thi sis what was returned:

connection1
connection2
123456, 45 Avenue St, Victorian, 125000,

INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,) VALUES ('123456', '45 Avenue St', 'Victorian', '125000')
123456, 125000
This was not successful because Query was empty

The table is already made is is waiting to receive the data. The only field that is set to NOT NULL is the MLSNum field.

[bradgrafelman]

Originally posted by Wendy B
then changed the
$sql_result = mysql_query($sql, $conn)
to:
$sql_result = mysql_query($query, $conn)

Why?? I JUST told you that, in your code, $query is NOT DEFINED, $sql IS. The mysql_query() should be run on $sql ...

[ashishsharma]

hi!
i have read ur query.
try it out

open ur php.ini file and set "register_globals = on" if it is not there
and then use directly name of input type(controls) of ur form .no need to use $_post[$name].if ur ist textbox name is =nm then use
"INSERT INTO FeatProperty (MLSNum,Address,Desc,Price,Photo)
VALUES ('$MLSNum','$Address', '$Desc', '$Price', '$Ph1')";

just use the correct names in values.i have had the same pb and i got it solved thru this.hope it will give u a sigh of relief.

do let me know if it works at [e-mail]

[thorpe]

open ur php.ini file and set "register_globals = on"

dont turn register globals on... this is a security issue and should be avoided.

[Wendy_B]

bradgrafelman said:

quote:

Originally posted by Wendy B
then changed the
$sql_result = mysql_query($sql, $conn)
to:
$sql_result = mysql_query($query, $conn)

---

Why?? I JUST told you that, in your code, $query is NOT DEFINED, $sql IS. The mysql_query() should be run on $sql ...

When I used the $sql the code broke! and said there was a syntax error - and if I change the line that identifies the $sql to $query it also breaks! IN fact if I change that variable to anything else it breaks.........:bemused:

[eerok]

Hmm, you posted this code earlier:

//create the connection
$dbh=mysql_connect ("localhost", "bristolr", "breal5") or die ('I cannot connect to the database because: ' . mysql_error());
echo "connection1";

//connect to the database
$conn = mysql_connect("localhost","bristolr_wendy","blackstone") or die('I cannot connect because1: ' .mysql_error());

You're connecting to the mysql server twice here but this doesn't connect you to your database. Replace that second mysql_connect() with something like this:

mysql_select_db( 'the_database' ) or die( 'Failed to access database' );

[Wendy_B]

The word that I was using for one of my fields, DESC, is a reserved word ----- and was throwing everything arye.

I changed the name to INFO and all went smoothly.......

Thanks for all the time you spent trying to help me on this one.