Using INSERT WHERE with variables

Question

I am trying to make an SQL query that :

IF the $post_id exists then it updates the records,
IF NOT then then it creates the record

Here is my code

$vote_new_total = $vote_total + $vote;
$vote_count = $vote_count + 1;

$query = "  INSERT INTO cute_review_vote (vote_total, vote_count) 
                VALUES ('$vote_new_total', '$vote_count') 
                ON DUPLICATE KEY UPDATE vote_total = $vote_new_total, vote_count = $vote_count 
                WHERE post_id = $post_id";
mysql_query($query) or trigger_error(mysql_error()." in ".$sql);

However, I keep getting the following error:

Notice: 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 post_id = 214748364' at line 4 in in C:\xampp\htdocs\xbm-vote\do_vote.php on line 68

Is this an issue with my syntax or am I missing something more obvious than that?

Any help/advice would be greatly appreciated. Thanks.

Fabiano · Accepted Answer · 2014-06-04 02:32:44Z

2

There is no where with ON DUPLICATE statement. Always check syntax: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Also, that is not a secure query. Turn that into a prepared statement. http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

EDIT: Updating answer since OP didn't understand usage of ON DUPLICATE statement:

ON DUPLICATE will consider that you're trying to insert a KEY value. Consider the example table:

CREATE TABLE `user` (
  `email` varchar(50) NOT NULL,
  `name` varchar(20) NOT NULL,
  `is_active` tinyint(4) NOT NULL,
  `datecreated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`email`)
);

And the query:

INSERT INTO user (email, name, is_active) VALUES ("[email protected]", "User", 1) ON DUPLICATE KEY UPDATE name = "User Edited", is_active = 0

Since we're setting PRIMARY KEY as email, and the insert statement is inserting a key, then, at the second time you run the query, ON DUPLICATE would run once a email is already on the table, because it is the KEY and you are trying to insert it again, but you defined a ON DUPLICATE KEY ....

If a table runs with a AUTO_INCREMENT column, is most likely you have to SELECT post_id applying some filter with WHERE statement.

edited Jun 4, 2014 at 2:32

answered Jun 3, 2014 at 0:36

Fabiano

9326 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lee Ginger-Ninja McCarthy Over a year ago

how would i go about achieving what I'm trying to achieve?

Fabiano Over a year ago

ON DUPLICATE wont work well if you have only a AUTO_INCREMENT. Consider to use another field as KEY as well, or change the structure of database to another KEY. ON DUPLICATE will consider that you are inserting a key, so it can identify its existence, then either update or complete the insert process. Maybe you could provide more details about how is your table, so we can consider alternatives about your problem.

Lee Ginger-Ninja McCarthy · Accepted Answer · 2014-06-03 01:33:29Z

0

Ok, I found my problem.

INSERT INTO cute_review_vote (post_id, vote_total, vote_count) 
                VALUES ('$post_id', '$vote_new_total', '$vote_count') 
                ON DUPLICATE KEY UPDATE vote_total = $vote_new_total, vote_count = $vote_count

I realized, thanks to Fabiano, that the WHERE clause is not required.

WHERE post_id = $post_id"

Is simply replaced by defining the database entry within the INSERT INTO command.

answered Jun 3, 2014 at 1:33

Lee Ginger-Ninja McCarthy

1843 gold badges4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Using INSERT WHERE with variables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related