JRowan JRowan - 3 months ago 18
MySQL Question

query where timestamp < timestamp not working?

2016-08-18 04:52:14 is the timestamp that I'm getting from the database to keep track from where I want to load more records which are less than that

this is the code

foreach($explode as $stat){
if($statement == ""){
$statement .= "`userid` = '$stat'";
}else{
$statement .= "OR `userid` = '$stat'";
}
}
}
$result = $link->query("SELECT * FROM `things` WHERE $statement AND `times` < '$time' ORDER BY `times` DESC LIMIT 20");


times
is the timestamp field I'm trying to query against but it is returning dates greater than that

example

id--------------times
63 8/18/2016 1:25:43 AM
---
---
---
---
---
93 8/18/2016 6:41:12 AM


so the $time variable is 2016-08-18 04:52:14 but it is returning rows 93 down limit 20, I could really use some advice thank you for your time

Answer

It might be because of the conditions in your query where you are using OR and AND.

SELECT * FROM `things`
WHERE ($statement)
  AND `times` < '$time'
ORDER BY `times` DESC
LIMIT 20"

By putting brackets around your $statement you might avoid the OR being attached to the time comparison.


Explanation

I am fairly certain what is happening, if you debug the actual query which is built by your PHP, is that it looks something like this:

WHERE `user_id` = 1 OR `user_id` = 2 AND `times` < '2016-08-18 04:52:14'

So the second user_id in this example is considered part of the times condition like this:

WHERE
  (`user_id` = 1)
     OR 
  (`user_id` = 2 AND `times` < '2016-08-18 04:52:14')

What you actually want is:

WHERE
  (`user_id` = 1 OR `user_id` = 2)
     AND
  (`times` < '2016-08-18 04:52:14')

Additional Improvement

You could probably use the SQL IN operator as an alternative anyway, which makes your code a bit more elegant and easier to read.

So if your user IDs are in a comma-delimited string or something already, don't bother exploding them (as I am assuming you have done from your code). Just pass them in to the query:

$userIds = "1, 2, 3, 4";

$sql = "SELECT *
        FROM `things`
        WHERE `user_id` IN($userIds)
          AND `times` < '$time'
        ORDER BY `times` DESC
        LIMIT 20";