Mysql Insert date in a database

Question

I am trying to execute the following query:

INSERT INTO table_timesheet (name, datein, dateout)
VALUES ('Rupert', 'NOW()', '')
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE datein='NOW()'
);

But this returns an error.

Basically I don't want to insert a record if the 'datein' field of the record already exists in another record - how to check if the new datein is unique?

And how can i insert in the same way the date out on the same row something like an update the datein row?

insert into .. values doesnt support a where clause.. need to use insert into .. select — amdixon, Dec 05 '15 at 09:15
`'NOW()'` is a function, not a string literal; drop the quotes. — Funk Forty Niner, Dec 05 '15 at 14:18

score 3 · Accepted Answer · answered Dec 05 '15 at 09:30

This INSERT query will do exactly what you want:

INSERT INTO table_timesheet (name, datein, dateout)
SELECT 'Rupert', NOW(), null
FROM DUAL
WHERE NOT EXISTS (
    SELECT * FROM table_listnames WHERE datein=NOW()
);

Please see it here. But a proper solution would be to set a table constraint. How are table_timesheet and table_listnames related? I would use a unique constraint on the datein column, but this depends on what are your requirements.

score 0 · Answer 2 · answered Dec 05 '15 at 09:29

first check this query (using MYSQLI or PDO ) if this query return false than you insert record

IF... this return false

SELECT name FROM table_listnames WHERE datein='NOW()'

FALSE

INSERT INTO table_timesheet (name, datein, dateout)VALUES ('Rupert', 'NOW()', '')

Mysql Insert date in a database

2 Answers2