Streamline SQL query

[erme]

Hi,

 

I currently have one table with a column called box, which will have values of 'one', 'two', 'three' etc. There will be 8 boxes to display on the site. The site needs to display the newest row according to column added (which will have the date added as a value). I am thinking of writing the code below to populate the 8 boxes:

while($row1 = mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE box = 'one' ORDER BY added DESC LIMIT 1"))) {
    
    //echo box 1
    
}

while($row2 = mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE box = 'two' ORDER BY added DESC LIMIT 1"))) {
    
    //echo box 2
    
}

while($row3 = mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE box = 'three' ORDER BY added DESC LIMIT 1"))) {
    
    //echo box 3
    
}

while($row4 = mysql_fetch_assoc(mysql_query("SELECT * FROM table WHERE box = 'four' ORDER BY added DESC LIMIT 1"))) {
    
    //echo box 4
    
}

etc....

 

Is there a better way to streamline this?

 

Many thanks

Edited May 9, 2013 by erme

[Barand]

use a subquery to find latest date for each box then match against that
 

SELECT table.*
FROM table
    INNER JOIN 
        (
        SELECT box, MAX(added) as added
        FROM table
        GROUP BY box
        ) as latest USING (box, added)
ORDER BY table.box

PS Don't nest mysql statements.

Edited May 9, 2013 by Barand

[mac_gyver]

there's a whole section for this in the mysql documentation - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html
 
does the box column actually the number name, one, two, ...? if it was the numerical number 1,2,3... it would be easier to order the output. however you cause use ORDER BY FIELD(box, 'one', 'two', ...) to order by the number name.

[Barand]

if it was the numerical number 1,2,3... it would be easier to order the output. however you cause use ORDER BY FIELD(box, 'one', 'two', ...) to order by the number name.

 

Good point

[erme]

Perfect! Thanks both of you. This worked!