Hardbyte Posted March 23, 2006 Share Posted March 23, 2006 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?ThanksHardbyte Quote Link to comment Share on other sites More sharing options...
redbullmarky Posted March 23, 2006 Share Posted March 23, 2006 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! Quote Link to comment Share on other sites More sharing options...
Hardbyte Posted March 23, 2006 Author Share Posted March 23, 2006 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 dumbassHardbyte Quote Link to comment Share on other sites More sharing options...
craygo Posted March 23, 2006 Share Posted March 23, 2006 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 tempSELECT * 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 Quote Link to comment Share on other sites More sharing options...
Hardbyte Posted March 23, 2006 Author Share Posted March 23, 2006 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 Quote Link to comment Share on other sites More sharing options...
craygo Posted March 23, 2006 Share Posted March 23, 2006 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 Quote Link to comment Share on other sites More sharing options...
Hardbyte Posted March 23, 2006 Author Share Posted March 23, 2006 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 Quote Link to comment Share on other sites More sharing options...
emehrkay Posted March 23, 2006 Share Posted March 23, 2006 would somethign like this work?SELECT *, COUNT(id) AS total FROM table GROUP BY id ORDER BY id LIMIT total-4, total Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.