php mysql : How to list two tables with related data?

Question

I had a simple tree structure output question,

In the beginning I use two mysql_query, one is to get the pro_class data, include pro_class.cid , and to query next table by cid, like:

mysql_query("select * from pro_class ");
mysql_query("select * from pro where cid='pro_class.cid' ");

but I want to using one query,how can I accomplish?

table: pro_class

cid 　title 　time

1　　PEOPLE　 2013/8/31

4　　CAR　　　2013/8/30

7　　Music　 2014/7/10

table: pro

id 　cid 　title 　content 　time
1　  1　　Mark     xxxx      2010/8/31
2　  4　　BMW      xxxx      2012/2/12
3　  1　　Joe      xxxx      2015/1/31
4　  7　　sweet    xxxx      2013/8/22
5　  1　　KEN      xxxx      2010/1/31

The structure should be

PEOPLE
-Mark
-Joe
-KEN
CAR
-BMW
Music
-sweet

I want to the Html output can like this (by php+mysql)

<ul>
 <li class=""><a href="#">PEOPLE</a></li>
 <ul class="sub">
   <li><a title='Mark' href='?id=1'>Mark</a></li>
   <li><a title='Joe' href='?id=3'>Joe</a></li>
   <li><a title='KEN' href='?id=5'>KEN</a></li>
 </ul>
 <li class=""><a href="#">CAR</a></li>
 <ul class="sub">
   <li><a title='BMW' href='?id=2'>BMW</a></li>
 </ul>
 <li class=""><a href="#">Music</a></li>
 <ul class="sub">
   <li><a title='sweet' href='?id=4'>sweet</a></li>
 </ul>
</ul>

How do I to coding sql?


$rst = mysql_query("select ?????? ");
while($row = mysql_fetch_object($rst)){ 

echo <<<EOD
<ul>
 <li class=""><a href="#">{$row->pro_class.title}</a></li>
 <ul class="sub">
   <li><a title='{$row->pro.title}' href='?id={$row->pro.id}'>{$row->pro.title}</a></li>
 </ul>
</ul>


EOD;
}

YES! In the beginning I use two mysql_query, one is to get the pro_class data, include pro_class.cid , and by it to query next table, mysql_query("select * from pro where cid='pro_class.cid' "); — ss123, Aug 31 '13 at 13:59
Do not even consider using the mysql_* interface. Switch to mysqli or PDO. mysql_* is deprecated and gone in the latest PHP release. — Rick James, Jun 05 '17 at 01:53

Fluffeh · Answer 1 · 2013-09-01T07:02:24.670

0

You can use a mysql function called group_concat which will merge a number of rows into a string that is delimited by something you choose.

The following will get you most of the way to getting what you want - you can probably work out the display part on your own though.

select
    pro_class.title,
    GROUP_CONCAT(pro.title SEPARATOR ',') AS listItems
from
    pro_class
        left outer join pro
            on pro_class.cid=pro.cid
group by
    pro_class.title

edited Sep 01 '13 at 07:02

answered Aug 31 '13 at 11:51

Fluffeh

33,228
16
67
80

thanks,but if table:pro_class hava data,but table:pro has no,then it can not show the pro_class.title – ss123 Sep 01 '13 at 07:01
@ss123 I changed the join to an outer join which will solve your issue about the second table being empty. You would also do well to have a good read of this article I put together: http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables – Fluffeh Sep 01 '13 at 07:03

rakeshjain · Answer 2 · 2013-08-31T13:03:07.670

0

You can try this approach. It uses group_concat.

<?php
$query = "select pc.title as title, group_concat(p.title) as inner_titles FROM pro_class pc  LEFT JOIN pro p ON p.cid=pc.cid GROUP BY p.cid ORDER BY p.title";
$result=mysqli_query($query);
print "<ul>";
while($res=mysqli_fetch_array($result)) {
    print "<li>" . $res['title'] . "</li>";
    $arr = explode(",",$res['inner_titles']);
    if(count($arr)>0) {
        print "<ul>";
        foreach($arr as $val) {
            print "<li>" . $val . "</li>";
        }
        print "</ul>";
    }
}
print "</ul>";
?>

edited Aug 31 '13 at 13:03

answered Aug 31 '13 at 11:56

rakeshjain

1,791
11
11

thanks,but if table:pro_class hava data,but table:pro has no,then it can not show the pro_class.title... – ss123 Aug 31 '13 at 12:57
@ss123 you can use left join to get rid of that problem. It will return at least one row for each row of pro_class table even if no matching row is there is pro table – rakeshjain Aug 31 '13 at 13:01
@ss123 were you not able to solve your issue with my answer and the above comment? – rakeshjain Sep 02 '13 at 11:47

php mysql : How to list two tables with related data?

2 Answers2