CODEWITHSUNDEEP
phpmysqlif-statementcount
Timm
Timm

Reputation: 12753

Count rows in one table within a IF statement

Is it possible to check if num rows in a table is 0 then perform an insert, all in ONE sql statement?

Here's my query that I tried but it says I have a syntax error:

$query = 
    "IF (SELECT COUNT(ID) FROM votes WHERE userid = $userid AND itemid = $itemid AND itemtype=1) = 0
        INSERT INTO votes (itemtype, itemid, userid) VALUES (1, $itemid, $userid) 
        SELECT 1 AS result
    ELSE
        SELECT 0 AS result
    END IF";

I know the SELECT COUNT bit works successfully on its own.

Upvotes: 1

Views: 278

Answers (2)

Frank Riccobono
Frank Riccobono

Reputation: 1063

I don't have access to MySQL to test this right now, but would this work?

INSERT INTO votes (itemtype, itemid, userid)
(SELECT  1,$itemid, $userid 
WHERE NOT EXISTS (
   SELECT * 
   FROM votes 
   WHERE itemtype=1 
   AND itemid=$itemid
   AND userid=$userid))

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 82058

NO IDEA if this is the best way of solving this, but it will work. Basically, it simply causes an error if the condition is false, and so it prevents insert:

-- make itemtype not nullable then simply insert
INSERT INTO votes SELECT
   CASE
      WHEN 
        (SELECT COUNT(ID) 
         FROM votes 
         WHERE userid = $userid AND itemid = $itemid AND itemtype=1) = 0 THEN 1
      ELSE NULL
    END CASE,
    $itemid, $userid;

Upvotes: 1

Related Questions