MySQL "IF (SELECT is empty) THEN INSERT" - solution

Question

I am developing a tool to log come-and-go - times of workers. These times are logged into a single mysql-table.

The table has a come and a gone field to register when a worker is coming or leaving. New entries should only be made, if all "old" entries are closed, means the worker has checked-out before.

I am thinking it should look like:

   IF EXISTS (  SELECT * FROM tbl_card
            WHERE time_gone IS NULL
            AND user_id=".$user_id." 
            )
            THEN
            BEGIN

            END
            ELSE BEGIN
              INSERT 
              INTO tbl_card (`user_id`, `time_come`) 
              VALUES ('2', '2018-01-01 14:00')
             END

Does anyone have an idea how this should work?

I am using php to query mysql. Php allowed to define "if-conditions", so I seperated the queries in SELECT * FROM tbl_card... and tested if the rowcount is '0' and then I executed the insert.

BUT: One user managed to use the microseconds between first and second query to check-in twice. :(

It is used in a LAMP-environment. Before it was like:

  $sql = "SELECT * FROM tbl_card WHERE time_gone IS NULL
                  AND user_id=".$user_id;
    $result = $db_conn->query($sql);

    if ($result->num_rows <> 0)
    { 
      return 'You already checked in!'; 
    }
    else
    {
    $sql = "INSERT 
              INTO tbl_card(`user_id`, `time_come`) 
              VALUES ('".$user_id."', '".$timestamp."')";

    if ($db_conn->query($sql)){
    return 'Check in: '.$timestamp;} 
    else
      { 
        return 'Something strange happend!';
      }
    }

Tables are:

   tbl_card -> id, user_id, time_come, time_gone
   tbl_user -> id, name

Where this code will be used? You use php tag but can't see php? Is it a trigger you try to build? — Mickaël Leger, Mar 27 '18 at 14:49
Possible duplicate of [MySQL: Insert record if not exists in table](https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table) — Cave Johnson, Mar 27 '18 at 16:57

score 4 · Answer 1 · answered Mar 27 '18 at 14:52

4

Try a single Insert/Select:

INSERT INTO card (`user_id`, `time_come`) 
SELECT '2', '2018-01-01 14:00'
WHERE NOT EXISTS
 ( SELECT * FROM punchclock
   WHERE time_gone IS NULL
     AND user_id=".$user_id." 
 )

answered Mar 27 '18 at 14:52

dnoeth

54,996
3
29
45

Worked fine! :) Thanks a lot. – John Pixel Mar 27 '18 at 15:42
Well, MySQL can't use the same table in both Insert & Select, but there are two different tables (or is one a view based on the other). – dnoeth Mar 27 '18 at 15:43
It seems MySQL accepts INSERT and SELECT on just one table: INSERT INTO tbl_card (`user_id`, `time_come`) SELECT '2', '2018-01-01 14:00' FROM dual WHERE NOT EXISTS ( SELECT * FROM tbl_card WHERE time_gone IS NULL AND user_id=".$user_id." ) – John Pixel Mar 27 '18 at 15:52
Why don't I have to write "VALUES" anywhere? – John Pixel Mar 27 '18 at 16:00
@JohnPixel: `VALUES` is for inserting a single row, but can't be used with `WHERE`. To be able to add `NOT EXISTS` you must switch to Insert/Select, but it's strange that MySQL needs a dummy table for that. – dnoeth Mar 27 '18 at 16:14

sunsetsurf · Answer 2 · 2018-03-27T16:45:38.567

1

INSERT INTO tbl_card (`user_id`, `time_come`)
SELECT * FROM (SELECT '2', '2018-03-27 15:15') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM tbl_card
        WHERE `time_gone` IS NULL
        AND `user_id`=`2`
) LIMIT 1;

See related post: MySQL: Insert record if not exists in table

edited Mar 27 '18 at 16:45

answered Mar 27 '18 at 15:07

sunsetsurf

560
4
7

can I get the structure of your two tbls and i'll try it out here. – sunsetsurf Mar 27 '18 at 15:20
Tables are: tbl_card -> id, user_id, time_come, time_gone tbl_user -> id, name – John Pixel Mar 27 '18 at 15:25
While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Fernando Molina Mar 27 '18 at 15:31

John Pixel · Answer 3 · 2018-03-27T16:09:34.327

Thanks to dnoeth who guided me to the final solution:

INSERT INTO tbl_card (`user_id`, `time_come`) 

  SELECT '2', '2018-01-01 14:00' FROM dual
  WHERE NOT EXISTS
   ( 
     SELECT * FROM tbl_card
     WHERE time_gone IS NULL
     AND user_id=".$user_id."
   )

dual is a dummy-table which is implemented in mysql, where no table is needed.

This solution simulates an insert of data from another table, which is not really existend and only has the static VALUES '2' and '2018-01-01 14:00' for each row. The ammount of rows is given by the WHERE-part of the query.

Sometime mysql and it's users surprise me. :D I wondered why I don't need a "VALUES"-command, but it worked fine.

Let's see if the user can "override" that query too :D

Btw, a simple solution would be a Unique Index on `user_id, time_gone` which fails when you insert a 2nd row with NULL (but I don't know if MySQL treats NULLs equal in indexes). — dnoeth, Mar 27 '18 at 16:26

MySQL "IF (SELECT is empty) THEN INSERT" - solution

3 Answers3