CODEWITHSUNDEEP
mysqlsql
Yunus Eren Güzel
Yunus Eren Güzel

Reputation: 3088

SQL syntax error

UPDATE `comment` 

SET `agree`=`agree`+1,(INSERT INTO `reacted_on` VALUES (10,197))
WHERE `id`=197 and 0 = (SELECT COUNT(*) FROM `reacted_on` WHERE `id_user`=10 and `id_comment`=197)

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
'(INSERT INTO `reacted_on` VALUES (10,197)) WHERE `id`=197 and 0 = (SELECT COUNT(' at line 1

I want to icrement agree in comment but i want to prevent same user to agree on a comment more than one. Therefore, first I check if user reacted on the same comment; I increment agree. Then I insert a row to prevent user from reacting on the same comment anymore.

What am I missing? why is it not true? What should I do to fix the problem?

How can I use INSERT statement in UPDATE query?

This is where I use the query:

        $con = mysql_connect($my_sql_servername,$my_sql_username,$my_sql_password);
        mysql_select_db($my_sql_database, $con);
        if($validation != $_SESSION['validation'])
            die('-1');
        $sql = "IF NOT EXISTS (SELECT * FROM `reacted_on` WHERE `id_user`=".$id_user." and `id_comment`=".$id_comment."  ) THEN INSERT INTO `reacted_on` VALUES (10,197); UPDATE `comment` SET `agree`=`agree`+1 WHERE `id`=".$id_comment." END IF;";
        //echo $sql;
        if(!mysql_query($sql)){die(mysql_error());}

Upvotes: 2

Views: 904

Answers (4)

Andriy M
Andriy M

Reputation: 77717

Basically, something like this, probably:

IF NOT EXISTS (
              SELECT *
              FROM `reacted_on`
              WHERE `id_user`=10 and `id_comment`=197
              )
THEN
  INSERT INTO `reacted_on` VALUES (10,197);
  UPDATE `comment`
  SET `agree`=`agree`+1
  WHERE `id`=197;
END IF;

You'll have to do that using separate statements, but you can wrap them into a single IF instruction.

EDIT

Another way to check for the existence of rows is to use SELECT COUNT(*)... (just like you did in your attempted script). So the beginning of the above query would change to:

IF 0 = (
       SELECT COUNT(*)
       FROM `reacted_on`
       WHERE `id_user`=10 and `id_comment`=197
       )
THEN
  ... /* same as above */

Upvotes: 1

xkeshav
xkeshav

Reputation: 54060

if u insert

  INSERT INTO `reacted_on` (id_user,id_comment) VALUES (10,197); ( first correct your insert statement) 
  then
  SELECT COUNT(*) FROM `reacted_on` WHERE `id_user`=10 and `id_comment`=197`

will always returns greater than 1

please explain what exactly u want to do?? u can not update two table together in mysql (AFAIK)

update

i dont know what is your relation between two tables but i suggest below way

$count_comment = mysql_query("SELECT COUNT(*) FROM `reacted_on` WHERE `id_user`=10 and `id_comment`=197"); 
if(!$count_comment)
{
    $sql= " UPDATE `comment` SET `agree`=`agree`+1 WHERE `id`=197";
    mysql_query($sql);
    $sql2= " INSERT INTO `reacted_on`(id_user,id_comment) VALUES (10,197) ";
    mysql_query($sql2);     
}
else
{ 
    // whatever u want to display
}

Upvotes: 0

Hammerite
Hammerite

Reputation: 22350

What are you trying to do here? Describe it in terms of the business logic of your application.

It looks like you might be trying to insert a row in table reacted_on, and then update the corresponding row in table comment. The best way to do this is to issue two queries, one after the other. Better still, wrap the two queries in a transaction; that way if anything goes wrong, your tables will not be left inconsistent.

Upvotes: 0

krtek
krtek

Reputation: 26607

It's just plain wrong ;)

You're doing an UPDATE query and then, out of nowhere, there's an INSERT subquery where you can't have on. I'll even say there is a closing parenthesis which have nothing to do here.

Upvotes: 2

Related Questions