9 Replies - 5718 Views - Last Post: 18 November 2011 - 10:44 AM

#1 StephenIsienyi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 10-November 08

1064: You have an error in your SQL syntax

Posted 22 October 2011 - 11:33 PM

Hello,

In the wampserver's mysql version 5.1.53, the following PHP code:

$sqlStr = "INSERT INTO " . $table_name . " ('Pub_Date', 'Topic', 'Source') VALUES ('" . $date . "','" . $topic . "','WSJ')";
$sqlStr = mysql_escape_string($sqlStr);
$results = mysql_query($sqlStr, $link);



Produces:

1064: 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 '\'Pub_Date\', \'Topic\', \'Source\') VALUES (\'2011-10-17\',\'Deal to Create Pipeline Giant' at line 1

...any takers?

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: 1064: You have an error in your SQL syntax

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: 1064: You have an error in your SQL syntax

Posted 23 October 2011 - 03:57 AM

Columns names should not have single quotes ('). However, in MySql, there's often the convention to use their field quote, which looks real close (`), whether you need it or not.

You don't need it unless your names have spaces. And you shouldn't have spaces in you columns names, because it's annoying. ;)
Was This Post Helpful? 1
  • +
  • -

#3 StephenIsienyi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 10-November 08

Re: 1064: You have an error in your SQL syntax

Posted 23 October 2011 - 05:00 PM

According to the mysql reference manual, column names and values are not to be enclosed. Going this route produces:

1064: 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 'Deal to Create Pipeline Giant, NEW)' at line 1
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: 1064: You have an error in your SQL syntax

Posted 23 October 2011 - 05:32 PM

This is from the reverence manual.

Quote

8.2. Schema Object Names
Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.
...
An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
...
The identifier quote character is the backtick ("`"):
-- http://dev.mysql.com...dentifiers.html


Quote

8.1.1. Strings
A string is a sequence of bytes or characters, enclosed within either single quote ("'") or double quote (""") characters.
-- http://dev.mysql.com...ing-syntax.html


I do not know this other reference to which you are referring.
Was This Post Helpful? 0
  • +
  • -

#5 StephenIsienyi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 10-November 08

Re: 1064: You have an error in your SQL syntax

Posted 25 October 2011 - 04:46 AM

Thank you baavgai. The backtick have fixed the 1064 problem.

At the moment, I am beginning to get this error:
1054: Unknown column '2011-10-17' in 'field list'.
I currently have the query string like this:
$sqlStr = "INSERT INTO $table_name (Pub_Date, Topic, Source) VALUES (`$date`,`$topic`,`WSJ`)";

I will be creating a different discussion for it later
on, if I cannot find any prior discussions on it.

Thanks a lot for your assistance.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: 1064: You have an error in your SQL syntax

Posted 25 October 2011 - 06:47 AM

Print out the string that variable inclusion resolves to and run it directly in MySql to get better error feedback. The backtick is for fields, not data, so that's the error.
INSERT INTO $table_name (Pub_Date, Topic, Source) VALUES ('$date','$topic','WSJ')



Note that including raw values in SQL strings is a bad idea. Please make sure you use http://www.php.net/m...cape-string.php before you do that.
Was This Post Helpful? 0
  • +
  • -

#7 StephenIsienyi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 10-November 08

Re: 1064: You have an error in your SQL syntax

Posted 25 October 2011 - 06:48 PM

I have now assigned 'WSJ' to a variable named `$source`, but still getting:
Diagnosis 1054: Unknown column '2011-10-26' in 'field list'.
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: 1064: You have an error in your SQL syntax

Posted 26 October 2011 - 06:07 AM

Try an insert statement with no variables.
Was This Post Helpful? 0
  • +
  • -

#9 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 170
  • View blog
  • Posts: 608
  • Joined: 12-October 09

Re: 1064: You have an error in your SQL syntax

Posted 27 October 2011 - 01:58 PM

YOu are using the backticks on the wrong part of the query.

It should be

INSERT INTO $table_name (`Pub_Date`, `Topic`, `Source`) VALUES ('$date','$topic','WSJ')



The backticks around the table fields, not around the data you are inserting
Was This Post Helpful? 1
  • +
  • -

#10 Stephen Isienyi  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 16
  • Joined: 11-November 08

Re: 1064: You have an error in your SQL syntax

Posted 18 November 2011 - 10:44 AM

Thanks Duckington.
Really appreciate.
Will test drive it soon.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1