开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜