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 Link to comment https://forums.phpfreaks.com/topic/215758-multiple-select-statement-or-am-i-missing-something/ 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. Link to comment https://forums.phpfreaks.com/topic/215758-multiple-select-statement-or-am-i-missing-something/#findComment-1121789 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! Link to comment https://forums.phpfreaks.com/topic/215758-multiple-select-statement-or-am-i-missing-something/#findComment-1121810 Share on other sites More sharing options...
premiso Posted October 13, 2010 Share Posted October 13, 2010 I try...sometimes > Link to comment https://forums.phpfreaks.com/topic/215758-multiple-select-statement-or-am-i-missing-something/#findComment-1121814 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.