Jump to content


Photo

Last 4 results only


  • Please log in to reply
7 replies to this topic

#1 Hardbyte

Hardbyte
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMidlands, UK

Posted 23 March 2006 - 01:28 PM

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:-

$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>";
                 
        }

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

#2 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 23 March 2006 - 01:50 PM

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

$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";

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

hope that helps!
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#3 Hardbyte

Hardbyte
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMidlands, UK

Posted 23 March 2006 - 02:06 PM

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

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 March 2006 - 02:24 PM

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.

$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

Ray

#5 Hardbyte

Hardbyte
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMidlands, UK

Posted 23 March 2006 - 02:41 PM

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

#6 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 March 2006 - 03:01 PM

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

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

Ray

#7 Hardbyte

Hardbyte
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMidlands, UK

Posted 23 March 2006 - 03:03 PM

Hi-ya.

I done:-

$temp2 = "DROP TEMPORARY TABLE temp";

mysql_query($temp2) or die (mysql_error());

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

Thanks for everyones help!! Nice one ;)

Hardbyte

#8 emehrkay

emehrkay
  • Staff Alumni
  • Advanced Member
  • 1,214 posts

Posted 23 March 2006 - 03:15 PM

would somethign like this work?

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users