How can I do a conditional SQL insert statement?
I'm completely new to MySQL. I'm attempting to write a query that inserts a row into the table, but only if the previous row was inserted over 5 minutes ago. Additionally, I'd like my PHP code to have some way of knowing whether the data was inserted or not. This is my attempt:
IF (SELECT max(EntryDate) from MyTable) < DATE_SUB(CURRENT_DATE() INTERVAL 5 MINUTE) THEN
INSERT INTO MyTable (...) values (...)
ELSE
Select false
END IF
Unfortunately, but not unsurprisingly, that gives a syntax error (开发者_运维知识库the error is being very vague about the location of the problem).
Am I doing something stupid?
Use WHERE NOT EXISTS and check for a row with >
time-5 minutes.
INSERT INTO MyTable (column1, column2)
SELECT value1, value2 FROM DUAL
WHERE NOT EXISTS (SELECT *
FROM MyTable
WHERE EntryDate > DATE_SUB(NOW(), INTERVAL 5 MINUTE))
You run the SELECT query, look at the result in your program, then decide whether to run the INSERT query.
//Ask for how many rows are less than 5 minutes old
$count = mysql_result(mysql_query("SELECT COUNT(*) FROM MyTable WHERE MAX(EntryDate) > CURRENT_TIMESTAMP - INTERVAL 5 MINUTE"),0);
if ($count == 0) {
mysql_query("INSERT INTO...");
//I know I just INSERTed a row because I'm inside the conditional!
}
I'm not sure about MySQL syntax, but you might need to wrap parentheses around this part:
(SELECT max(EntryDate) from MyTable) < DATE_SUB(CURRENT_DATE() INTERVAL 5 MINUTE
精彩评论