1

Hopefully someone can help me out. All I am trying to do is insert a record into a database, but I keep getting the message

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 'order (pid,uid,projecttitle,username,amount,odate) values (,,'','',,'6-2' at line 1` on my page.

Here is the main part of the code. I would be grateful for anyone that can help me out.

<?
extract($_REQUEST);
//print_r($_REQUEST);
//query fetch user & project info
//$queryorder="select * from project p where p.pid='".$id."'";
$queryorder="select * from  users u,project p where p.pid='".$id."' and u.uid='".$_SESSION['key']."'";
$resultorder=executequery($queryorder,$link);
$rowo=mysql_fetch_assoc($resultorder);

//print_r($rowo);

//get today date
$createddate=date("n-j-Y");

//order
$order="insert into order (pid,uid,projecttitle,username,amount,odate) 
       values (".$rowo['pid'].",".$rowo['uid'].",'".$rowo['projectname']."','".$rowo['username']."',".$rowo['price'].",'".$createddate."')"; 
mysql_query($order) or die(mysql_error());

//end of insert order query
?>
<? //headtag.php conatain all javascript & css files
 include('headtag.php'); 
?>
<body>
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
JBock
  • 21
  • 1
  • 1
    Could you provide the output of print_r($rowo). – Hyperboreus Jun 28 '11 at 15:47
  • Supply the output of `print_r($rowo);`. That variable is the root of your problem. – Jason McCreary Jun 28 '11 at 15:47
  • I don't know what your problem is, but I did notice that the values in your query are all empty: `values (,,'','',,'6-2'...)`. You may want to look into that. – eykanal Jun 28 '11 at 15:49
  • For more on delimited identifiers see http://stackoverflow.com/questions/214309/do-different-databases-use-different-name-quote/214344#214344 – Bill Karwin Jun 28 '11 at 16:10
  • I would supply the output, but it has some personal info in it that I can't release. I can say that the fields are populated. – JBock Jun 28 '11 at 16:12

4 Answers4

4

ORDER is a mysql keyword. Try this:

INSERT INTO `order` ...
soulmerge
  • 73,842
  • 19
  • 118
  • 155
  • 2
    And you should rename your table, if it is within your authority. Having keywords as table names is not a good idea. – soulmerge Jun 28 '11 at 15:50
  • 1
    +1: Besides the fact it's a reserved word, it's too vague to be of any benefit -- order of what?! – OMG Ponies Jun 28 '11 at 16:03
2

order is a keyword. You need to escape it with backticks.

insert into `order` (pid,uid,projecttitle,username,amount,odate)...
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • okay, the backticks seemed to do the trick. I hadn't noticed that before, nor realized there was such a way to escape the keyword issue. – JBock Jun 28 '11 at 16:02
0

You must supply a value for each column.

(,,'','',,'6-2'

You obviously have no values for pid, uid and amount.

Hyperboreus
  • 31,997
  • 9
  • 47
  • 87
  • I don't see why would anyone downvote this answer (without posting a comment why). @Hyperboreous's answer is right. (aside from that he has a table named order which needs to be quoted as well) – cypher Jun 28 '11 at 15:54
  • It supposed to be pulling down values from the $rowo=mysql_fetch_assoc($resultorder); section, but maybe it's not working. – JBock Jun 28 '11 at 15:54
-2

Try this: (you are supplying empty values to that query)

$order="insert into order (pid,uid,projecttitle,username,amount,odate) 
       values (".(int)$rowo['pid'].",". (int)$rowo['uid'].",'".$rowo['projectname']."','".$rowo['username']."',". (float)$rowo['price'].",'".$createddate."')"; 
mysql_query($order) or die(mysql_error());

The second thing that will cause mysql syntax error is the table name - order is a reserved keyword in mysql, so you need to quote it. The beginning of the query will then be:

INSERT INTO `order` (...

cypher
  • 6,822
  • 4
  • 31
  • 48
  • Removed your cynicism, this should decrease your downvotes (the answer is still not helpfull, though). – soulmerge Jun 28 '11 at 15:53
  • Thank you for removing the sentence where I stated that he's supplying empty values to the query. The answer was helpful before, now it's not. – cypher Jun 28 '11 at 15:54
  • That is getting me a little closer. Here is the results: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 'order (pid,uid,projecttitle,username,amount,odate) values (0,0,'','',0,'6-28-201' at line 1 – JBock Jun 28 '11 at 15:55
  • This part should be right now, the second error is that you are using a reserved word as table name. I'll update the answer. – cypher Jun 28 '11 at 15:56
  • Thank you for all the quick responses. The fact that order was a keyword was the issue. I unfortunately inherited this code from the former webmaster, and I've been finding lots of issues/errors that I've been trying to resolve. – JBock Jun 28 '11 at 16:05
  • Okay, I misspoke a bit. It still isn't pulling down my values to be placed into the table. It is inserting a record into the table, but with out any values except the data. – JBock Jun 28 '11 at 16:07
  • Fyi, a good way to validate SQL syntax is to be using [MySQL workbench](http://wb.mysql.com/), print out the sql query from your php code (in your case - `echo $order;`) and then paste it into workbench's query editor. It will underline the syntax errors. Even though it doesn't explain them (will give you the same error message as here), you always know where to start (in the example from your previous comment, it underlined the word "order"). – cypher Jun 28 '11 at 16:08
  • @JBock then we'll need to know what does `$resultorder=executequery($queryorder,$link);` return (the executequery function). – cypher Jun 28 '11 at 16:10