CODEWITHSUNDEEP
phpmysqlsql
John Pixel
John Pixel

Reputation: 57

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

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

Upvotes: 2

Views: 3705

Answers (3)

sunsetsurf
sunsetsurf

Reputation: 592

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

Upvotes: 1

John Pixel
John Pixel

Reputation: 57

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

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

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." 
 )

Upvotes: 5

Related Questions