请给我解决这个问题的任何想法。
我有两张桌子
类别：
id。。。。类别母
1。。。。全体的0
2。。。。消息0
新闻：id。。。。大字标题category_id
1。。。。标题1。。。。1
2。。。。标题2。。。。2
3。。。。标题3。。。。2
4。。。。标题4。。。。1
我有这个MYSQL
代码：
SELECT * FROM news INNER JOIN categories ON news.category_id=categories.id group by category_id,date
现在我想根据类别获取行
所以我得到这样的结果：
一般类别：
标题1
标题4
新闻类别：
标题2
标题3
请帮我处理这些家伙
$getcategories = mysql_query("SELECT DISTINCT id FROM categories");
$categories = mysql_num_rows($getcategories);
if($categories > 0){
echo "<table width='100%' cellspacing='0' cellpadding='0'>";
while ($rowcategories = mysql_fetch_assoc($getcategories)) {
$category_id = $rowcategories['id'];
$category_title = $rowcategories['category'];
$getnews = mysql_query("SELECT * FROM news WHERE category_id = '$category_id'");
$news = mysql_num_rows($getnews);
if($news > 0){
echo "<tr><td>$category_title</td></tr>";
while ($rownews = mysql_fetch_assoc($getnews)) {
$news_headline = $rownews['headline'];
// all other info you want to pull here
echo "<tr><td>ROW WITH NEWS INFO HERE</td></tr>";
} // end news loop
} // end if news > 0
echo "</table>";
} // end if categories > 0
} // end categories loop
You dont need group by. You need **order by**.
请参阅我的mysql日志。
mysql> create table categories ( id int , category varchar(20), parent int);
Query OK, 0 rows affected (0.06 sec)
mysql> create table headlines ( id int, headline varchar(30), category_id int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into categories values (1, 'general', 0) , (2, 'news', 0);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into headlines values (1, 'headline1', 1), (2, 'headline2', 2), (3, 'headline3', 2), (4, 'headline4', 1);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select category , headline from headlines as h join categories as c on (c.id = h.category_id) order by category, headline ;
+----------+-----------+
| category | headline |
+----------+-----------+
| general | headline1 |
| general | headline4 |
| news | headline2 |
| news | headline3 |
+----------+-----------+
4 rows in set (0.00 sec)