PHP PDO insert if not exists last 15 minute

Question

I'm trying to make a very simple statistics script.

I have a table data ( ip, date, page ). I want to insert ip and current page to this table only if there isn't the same ip and page since the last 15 minutes.

Here is the last SQL I tried :

INSERT INTO data ( `ip` , `date` , `page` )
SELECT ( :ip , NULL , :page )
WHERE NOT EXISTS (
    SELECT * FROM data
    WHERE `ip` = :ip AND `page` = :page AND date BETWEEN timestamp(DATE_SUB(NOW(), INTERVAL 15 MINUTE)) AND timestamp(NOW())
)

The SELECT inside "not exists" works when used alone. Any help appreciated !

Do you have a primary key? Hint: you should always have a primary key (MySQL will create a surrogate primary key for you if you don't have one). — Marcus Adams, Oct 24 '13 at 12:34

score 0 · Answer 1 · answered Oct 24 '13 at 10:19

0

"where not exists" is not valid

You have to do 2 sql queries:

$sql = 'SELECT * FROM data
    WHERE `ip` = :ip AND `page` = :page AND date BETWEEN timestamp(DATE_SUB(NOW(), INTERVAL 15 MINUTE)) AND timestamp(NOW())';
$sth = $pdo->prepare($sql);
$sth->execute(array(':ip' => 'that ip', ':page' => 'that page'));
if ($sth->rowCount() === 0)
{
   $sql = 'INSERT INTO data ( `ip` , `date` , `page` )
VALUES (:ip , NULL , :page)';
   // etc.
 }

answered Oct 24 '13 at 10:19

Asenar

6,732
3
36
49

1

If you need to make an update to a table in more than one query, don't forget to start a transaction, otherwise race conditions may occur. – aspyct Oct 24 '13 at 10:21
this code will be a subject of race condition and result in many duplicates. and transactions will scarcely help – Your Common Sense Oct 24 '13 at 10:31

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

I managed to get it working, thanks to this question : PHP PDO INSERT WHERE NOT EXISTS statement not working

Here is the working SQL :

INSERT INTO data (ip, date, page)
SELECT * FROM (SELECT :ip , NULL, :page ) as tmp
WHERE NOT EXISTS (
    SELECT ip
    FROM data
    WHERE ip = :ip
        AND page = :page
        AND date >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
    LIMIT 1
)

Problem is, i'm not sure to undertand everything about it and i don't like that so I'll try to find more infos !

edited May 23 '17 at 12:05

Community

1
1

answered Oct 24 '13 at 12:24

Aurélien Grimpard

918
10
24

If there isn't tmp, it return this error `Every derived table must have its own alias`. I insert NULL because date is an auto current_timestamp in my table. – Aurélien Grimpard Oct 24 '13 at 12:49
If this works you can validate it. I understand better what you wanted to do now, with the "select * from" before ! – Asenar Oct 24 '13 at 13:15
1

This should work, but consider improving the `WHERE` subquery to `SELECT ip FROM data WHERE ip = :ip AND page = :page AND \`date\` > DATE_SUB(NOW(), INTERVAL 15 MINUTE) LIMIT 1`. Also, consider adding some indexes, especially to date. – Marcus Adams Oct 24 '13 at 13:23
Thanks for the improvement, i edited the answer and i also changed my date row as primary key. Everything works like a charm :) – Aurélien Grimpard Oct 24 '13 at 13:33

PHP PDO insert if not exists last 15 minute

2 Answers2