Jump to content

Archived

This topic is now archived and is closed to further replies.

Hardbyte

Last 4 results only

Recommended Posts

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

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
would somethign like this work?

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.