MysQL SELECT statement if empty

Question

I am struggling a bit with something that I think should be simple to solve.

I first check the database to see if it exists. I would like it to insert the date if it does not exist but I am not sure how to structure the IF statement part. Many thanks

$date='2017-05-13';
$select_date = mysqli_query($link, "SELECT * from `marker` WHERE `date`='$date' ");

$insert_date = mysqli_query($link, "INSERT INTO `marker` (`date`,`value`) VALUES ('$date','1') ");

Check for the number of rows returned after your first query, if that is 0, run the insert — Hanky Panky, May 19 '17 at 09:51
Use mysql [ON DUPLICATE KEY](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) syntax — Code4R7, May 19 '17 at 09:52
SO is not really a tutorial site. There are plenty of sites out theere for that. — RiggsFolly, May 19 '17 at 09:52
The dup target gives PDO code instead of mysqli. If you are just starting to learn, switch to PDO. — e4c5, May 19 '17 at 09:53
secondly as explained in that answer don't do SELECT followed up insert — e4c5, May 19 '17 at 09:53

score 1 · Accepted Answer · answered May 19 '17 at 09:54

1

In general, for this type of operation, you want to use on duplicate key update. This starts with a unique index:

CREATE UNIQUE INDEX unq_marker_date ON marker(date);

Then the database guarantees only one row per date. You can then do the insert as:

INSERT INTO `marker` (`date`, `value`)
    VALUES ('$date', '1')
    ON DUPLICATE KEY UPDATE `date` = VALUES(`date`);

The ON DUPLICATE KEY part does nothing except prevent an error for a duplicate date.

answered May 19 '17 at 09:54

Gordon Linoff

1,122,135
50
484
624

Thanks i've implemented this and it works as required. It is a shame that my question has been down voted because the others thought it was too simple. Thanks again for this code! – Jean-Claude May 19 '17 at 10:05

MysQL SELECT statement if empty

1 Answers1