Last 4 results only

[Hardbyte]

Hi (and hope Mark - Mr Guru Guy - is reading ;-) )

Iv got a script that loops though the entries, there can be loads, but I just want to list the last 4. Its based on time/action but thats basically irrelivant. I just want it to loop thou as it currently does but just display the last 4 entries. Code Iv got so far:-

[code]$query2  = "SELECT * FROM logs, actionid, statusid WHERE logs.statusid = statusid.statusid AND logs.actionid = actionid.actionid AND actionid.logref = {$row['logref']} ORDER BY actiontime ASC";

$result2 = mysql_query($query2);

echo "<td valign=top width=330><table border=0 width=328>";
while($row = mysql_fetch_array($result2, MYSQL_ASSOC))


    
        {
            echo"<tr><td width=70> {$row['actiontime']} </td> <td         width=260> {$row['actiondesc']} </td></tr>";
                 
        } [/code]

Iv taken some code out sorry, but it shows a table with all the results - how'd I get the last four? Im guessting its another array thingy?

Thanks

Hardbyte

[redbullmarky]

if youre only after 4 records, use LIMIT in mysql, unless you need the other records for some other use.

[code]$query2  = "SELECT * FROM logs, actionid, statusid WHERE logs.statusid = statusid.statusid AND logs.actionid = actionid.actionid AND actionid.logref = {$row['logref']} ORDER BY actiontime DESC LIMIT 4";
[/code]

notice also i changed ASC to DESC in your query - DESC will mean that the most recent records will come first.

hope that helps!

[Hardbyte]

Hi-ya, thanks for your rapid replay again.

This is where Im gonna be a pain in the bumcheeks...

I need it to be ASC as I need the latest/newest at the bottom of the table. LIMIT 4 seems to only be showing the first 4 entries not the last.

If somehow I could use LIMIT 4 but then reorder the entries when using DESC?

DESC + LIMIT 4 worked but the latest entry was at the top. I Sortof need this but putting the latest entries at the bottom?

Sorry for being a dumbass

Hardbyte

[craygo]

You could always put them into a temporary table the requery and sort them the way you want. Should be fast considering you are only getting 4 results.

[code]$temp = "CREATE TEMPORARY TABLE temp
SELECT * FROM logs, actionid, statusid WHERE logs.statusid = statusid.statusid AND logs.actionid = actionid.actionid AND actionid.logref = {$row['logref']} ORDER BY actiontime DESC LIMIT 4";
mysql_query($temp) or die (mysql_error());

$sql = "SELECT * FROM temp ORDER BY actiontime ASC";
  $res = mysql_query($sql) or die (mysql_error());
//blah blah blah[/code]

Ray

[Hardbyte]

Hi there, thanks for your help.

Im just checking the results, think it may have worked but Im unable to check as I get the first set of results then I get "Table 'temp' already exists" error message.

Is there a way of doing this procedure and displaying the first lot of results, then like drop/flush the temp table ready for the next load?

Thanks again.

Hardbyte

[craygo]

You don't want to clear the data because the next query you do may not have the same fields. you can add this to the end to drop the table

[code]$drop = "DROP TEMPORARY TABLE IF EXIST temp";
  mysql_query($drop) or die (mysql_error());[/code]

Ray

[Hardbyte]

Hi-ya.

I done:-

[code]$temp2 = "DROP TEMPORARY TABLE temp";

mysql_query($temp2) or die (mysql_error());[/code]

at the end of the loop and this seems to have worked.

Thanks for everyones help!! Nice one ;)

Hardbyte

[emehrkay]

would somethign like this work?

SELECT *, COUNT(id) AS total FROM table GROUP BY id ORDER BY id LIMIT total-4, total