CODEWITHSUNDEEP
phpmysqltimelimit
enloz
enloz

Reputation: 5824

PHP MySQL - limit query result by time difference

This would be an example of database table:

enter image description here

PHP script should limit query result between NOW and time before 30 seconds

To be clear:

In current time is 2012-03-23 03:28:00 and database is as descibed in example, resoult should be: "guest"

This is a part of PHP script.

<?php
    $con = mysql_connect("localhost", "user", "pass");
    if (!$con) {
        die('Could not connect: '.mysql_error());
    }

    mysql_select_db("db", $con);

    $result = mysql_query('SELECT * FROM table LIMIT BY ...???...... ');
        while ($row = mysql_fetch_array($result)){
         echo $row['username'];
        }

    mysql_close($con);
?>

Upvotes: 1

Views: 871

Answers (3)

heyanshukla
heyanshukla

Reputation: 669

    $result = mysql_query('SELECT * FROM table where date_sub(now(), timestamp ) < your_time_interval ');

Upvotes: 1

Konerak
Konerak

Reputation: 39763

You don't want a LIMIT, you want a WHERE

$result = mysql_query(
'SELECT * FROM table WHERE `timestamp` > NOW()- INTERVAL 30 SECOND '
);

In SQL, a LIMIT clause executes the whole query, and then only takes a certain "range" of consecutive rows. You can say "The first 50", or "From 20 to 30".

A WHERE clause limits the query on certain criteria, such as field contents. That is what you want here.

Upvotes: 4

Artjom Kurapov
Artjom Kurapov

Reputation: 6155

... WHERE (unix_timestamp(NOW()) - unix_timestamp(timestamp))<30 ..

OR

WHERE TIME_TO_SEC(TIMEDIFF(NOW(), timestamp))<30

Upvotes: 2

Related Questions