error in SQL syntax!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xiao_dolp
Forum Newbie
Posts: 15
Joined: Sun Jul 01, 2007 7:00 am

error in SQL syntax!!!

Post by xiao_dolp »

Hey!!
I have error to add information for one of my database. I try to echo out the $sql to see whether it will echo out i try, and it display.After i try to find the mysql_error and it display this
INSERT INTO place ( id, from, place, to, description ) VALUES ( '55', 'D', 'D', 'D', 'D')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 'from, 'place', 'to', 'description' ) VALUES ( '55', 'D', 'D', 'D', 'D')' at line 1
i not sure what is the error, as for other page, i aso use about the same code, the only different is the name of the database and variable name and i able to insert into database. but i not sure why this is not working??? please help!!!

This is my php code

Code: Select all

<?php
require('db.php');
//retrieve the information from html form

$d = $_POST['id'];
$from = $_POST['from'];
$place = $_POST['place'];
$to = $_POST['to'];
$description = $_POST['description'];

//connect to DB server


mysql_connect(MACHINE, USER, '');
mysql_select_db(DBNAME);

//prepare the sql query
$sql = "INSERT INTO place ( id, from, place, to, description ) VALUES ( '".$id."', '".$from."', '".$place."', '".$to."', '".$description."')";
echo $sql;
// mysql_query($sql);
mysql_query($sql) or die (mysql_error());

echo "<a href = 'try.php'>Go back to the  page</a>";
?>
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

"FROM" is a reserved word. Surround it in backticks (`).
xiao_dolp
Forum Newbie
Posts: 15
Joined: Sun Jul 01, 2007 7:00 am

Post by xiao_dolp »

u mean like that???
$sql = "INSERT INTO place ( id, from', place, to, description ) VALUES ( '".$id."', '".$from."', '".$place."', '".$to."', '".$description."')";

it still show the same error no matter how i place the ' '
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Backticks (`) and single quotes (') are different.

The backtick (`) is to the left of the '1' on your keyboard.
xiao_dolp
Forum Newbie
Posts: 15
Joined: Sun Jul 01, 2007 7:00 am

Post by xiao_dolp »

ok!!thank!!it able to added into my database!!!

but my update php aso have this problem and i put ` ` to the code but it echo failed

Code: Select all

<html>
<head>
</head>

<body>
<?
//connect to DB server
require('db.php');
//retrieve the information from html form

$id = $_POST['id'];
$from = $_POST['from'];
$place = $_POST['place'];
$to = $_POST['to'];
$description = $_POST['description'];

echo $id;
echo $from;
echo $place;
echo $to;
echo $description;


mysql_connect(MACHINE, USER, '');
mysql_select_db(DBNAME);


//prepare the sql query



$sql = "UPDATE place SET place = $place, `from` = $from, `to` = $to, description = $description WHERE id = $id";
$result= mysql_query($sql);
if(!$result){
echo "Cannot.. failed";
}else{
//echo $sql;
// Show confirmation
echo "Your place is added to your database.<br>";
}
// Show link to index.html
echo "<br><a href = 'tryroute.php'>Go back to the page</a>";
?>
</body>
what is the problem with this???
Last edited by xiao_dolp on Wed Jul 18, 2007 3:05 pm, edited 2 times in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Firstly, you have HUGE security holes (hint: mysql_real_escape_string).

Secondly, what is the mysql_error from the query?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I would imagine it has something to do with $route, $from, $to and $description. Something tells me some of them aren't numbers.
xiao_dolp
Forum Newbie
Posts: 15
Joined: Sun Jul 01, 2007 7:00 am

Post by xiao_dolp »

what you mean by HUGE security holes??

the error is
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 `route_id` = 55' at line 1
yup, only $id is int, other is all varchar.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

echo $sql.
xiao_dolp
Forum Newbie
Posts: 15
Joined: Sun Jul 01, 2007 7:00 am

Post by xiao_dolp »

This is what it show when i echo $sql and mysql_error.
UPDATE place SET place = testing, `from` = testing, `to` = testing, description = , WHERE `id` = 55You 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 `id` = 55' at line 1
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Notice anything odd about your query?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

string literals have to be quoted for mysql.

Code: Select all

UPDATE
  tablename
SET
  x=3, /* a number, no quotes needed */
  y=`z`, /* mysql assigns the value of the field z (of the same records), backticks optinal in this case */
  z='foo bar' /* mysql assigns the string foo bar to z, quotes are mandatory */
Post Reply