0

I have this query

 mysql_query("INSERT into reviews VALUES(0,$pid,$id,'new')") or die(mysql_error());

It seems to give an 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 ''new')' at line 1"

Though it seems perfectly fine, the database table structure is:

 `id` int(11) NOT NULL AUTO_INCREMENT,
  `proposalid` int(11) NOT NULL,
  `reviewerid` int(11) NOT NULL,
  `status` enum('approved','declined','noresponse','new') NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`);

There seems to be nothing wrong, but why the error?

tkanzakic
  • 5,499
  • 16
  • 34
  • 41
Jamie Anacleto
  • 55
  • 6
  • what do $pid and $id resolve to? – Kai Qing May 22 '13 at 03:05
  • 1
    The problem may not be with new but tha $id may be null? – av501 May 22 '13 at 03:05
  • it worked fine in the fiddle http://www.sqlfiddle.com/#!2/67749/1 – John Woo May 22 '13 at 03:06
  • its from the function call. public function assign_reviewer($pid,$id) – Jamie Anacleto May 22 '13 at 03:07
  • Aside that, you know this query will fail as soon as you have 1 row, right? you should define the fields AND values `INSERT into reviews (proposalid, reviewerid) VALUES($pid,$id)` - leave auto increment off and don't bother with status since default is new – Kai Qing May 22 '13 at 03:08
  • Please, don't use mysql_* functions. Your code is a SQL Injection vulnerability waiting to happen. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Cfreak May 22 '13 at 03:09

3 Answers3

0

Have you tried either using DEFAULT for the auto-increment instead of 0, or explicitly declaring the columns INSERT into reviews (proposalid, reviewerid, status) VALUES($pid, $id, 'new')?

TJennings
  • 432
  • 3
  • 14
  • tried that just now. now it gives the error "....right syntax to use near ')' at line 1" – Jamie Anacleto May 22 '13 at 03:15
  • What are the current values of `$pid` and `$id`? I'm inclined to agree with an above answer that there may be an erroneous quote hovering around in one of them breaking the formatting. – TJennings May 22 '13 at 03:20
0

The value of $id could be causing an erroneous quote to throw the error.

For example,

$pid = "6";
$id = "5'";

Would cause:

INSERT into reviews VALUES(0,6,5','new')

You should also use a column list when making an INSERT statement. Since new is the default for the status column, you can exclude it altogether (as well as excluding the auto increment primary key):

INSERT into reviews (proposalid, reviewerid) VALUES($id, $pid)

If $id and $pid are user inputs, then you need to use prepared statements. Also, the API you're using (mysql_) is deprecated. PDO or mysqli_ are the replacements and both allow prepared statements to sanitize inputs.

Kermit
  • 33,827
  • 13
  • 85
  • 121
  • adding backticks and single quotes seemed to have done the trick, heres the query that worked. thanks! ("INSERT into reviews (`proposalid`, `reviewerid`) VALUES('$id', '$pid')") – Jamie Anacleto May 22 '13 at 03:21
  • @JamieAnacleto Just so you know, that's not the correct way of doing it. – Kermit May 22 '13 at 03:26
0

Since your default value of ENUM status is set to 'new', can you try your INSERT such as :

mysql_query("INSERT into reviews (`proposalid`, `reviewrid`) VALUES($pid, $id);") or die(mysql_error());

Maybe the error will be more talking after this.

André Jacques
  • 41
  • 1