Bricktop Posted October 13, 2010 Share Posted October 13, 2010 Hi all, I have the following query, as simple as it gets: $sql = mysql_query("SELECT * FROM news ORDER BY category, id DESC"); This will output: WORLD NEWS story 1 story 2 story 3 story 4 LOCAL NEWS story 1 story 2 story 3 story 4 story 5 TECH NEWS story 1 story 2 story 3 What I want is to limit the output of EACH category by 3 so I get: WORLD NEWS story 1 story 2 story 3 LOCAL NEWS story 1 story 2 story 3 TECH NEWS story 1 story 2 story 3 Obviously: $sql = mysql_query("SELECT * FROM news ORDER BY category, id DESC LIMIT 3"); Just gives: WORLD NEWS story 1 story 2 story 3 Hope this makes sense, any ideas how I can achieve this? Thanks Quote Link to comment Share on other sites More sharing options...
premiso Posted October 13, 2010 Share Posted October 13, 2010 I think you will have to do multiple queries for each section, you can do this all in one go if you like using UNION ALL: (SELECT * FROM news WHERE category = 'World News' LIMIT 3) UNION ALL (SELECT * FROM news WHERE category = 'Local News' LIMIT 3) And if you want it to be dynamic: $result = mysql_query("SELECT category FROM news GROUP BY category") or trigger_error("Error fetching the categories: " . mysql_error()); if ($result) { $newsQuery = array(); while ($row = mysql_fetch_assoc($result)) { $newsQuery[] = "(SELECT * FROM news WHERE category = '" . $row['category'] . "' LIMIT 3)"; } $newsQuery = implode(' UNION ALL ', $newsQuery); // glue it back together $result = mysql_query($newsQuery) or trigger_error("News query failed with error: " . mysql_error()); $prevCat = ''; $display = ''; while ($row = mysql_fetch_assoc($result)) { if ($prevCat != $row['category']) { $display .= $row['category'] . '<br>'; $prevCat = $row['category']; } $display .= $row['title'] . '<br>'; // change title to whatever it should be } }else { $display = 'Failed fetching categories.'; } echo $display; Should get you the desired display. I just roughed this together so may be some minor issues, if you cannot work them out let me know and I will help you out. Quote Link to comment Share on other sites More sharing options...
Bricktop Posted October 13, 2010 Author Share Posted October 13, 2010 Perfect! Thanks premiso - the UNION option worked a treat! Quote Link to comment Share on other sites More sharing options...
premiso Posted October 13, 2010 Share Posted October 13, 2010 I try...sometimes > 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.