MysQL SELECT statement if empty [duplicate]

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
– Hanky Panky, Commented May 19, 2017 at 9:51
SO is not really a tutorial site. There are plenty of sites out theere for that. — RiggsFolly
– RiggsFolly, Commented May 19, 2017 at 9:52
The dup target gives PDO code instead of mysqli. If you are just starting to learn, switch to PDO. — e4c5
– e4c5, Commented May 19, 2017 at 9:53
secondly as explained in that answer don't do SELECT followed up insert — e4c5
– e4c5, Commented May 19, 2017 at 9:53

Gordon Linoff · Accepted Answer · 2017-05-19 09:54:32Z

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, 2017 at 9:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeanclaude Over a year ago

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!

Collectives™ on Stack Overflow

MysQL SELECT statement if empty [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related