select last 100 records inserted

[cmor]

Why oh why does this not work:

 

@ $db = new mysqli('localhost', user', 'password', 'database');
$query = "select * from weather where timekey in (select * from weather ORDER BY timekey desc LIMIT 168) order by timekey asc";
$result = $db->query($query);

 

I need to select the last records in the database and order them in ascending order.

sigh....

 

[akitchin]

the reason it doesn't work is because you're grabbing all 168 of the records in descending order for the sub-query, which the overall select query is then just reshuffling.  you need to limit the subquery to 100, or you can just toss them in an array and resort using PHP:

 

while ($row = mysql_fetch_assoc($resource))
{
  $stuff[] = $row;
}
$new_order = array_reverse($stuff);

[cmor]

I actually want 168 records.....I guess I spaced on the post title.  There is currently a 1000 or so in the database

 

The currentlly produces: nothing

 

Thanks for the response

[akitchin]

it's possible that your version of MySQL doesn't support sub-queries.  have you tried adding an or die() statement to your mysql_query() line to see if it's a SQL syntax error?

[Barand]

if that is the case (versions below 4.1, I think) then

 

<?php

mysql_query ("CREATE TEMPORARY TABLE tmp SELECT timekey FROM WEATHER ORDER BY timekey DESC LIMIT 168");
$res = mysql_query ("SELECT w.* 
            FROM WEATHER w
            INNER JOIN tmp t ON w.timekey = t.timekey
            ORDER BY timekey")
?>