How to display two linked tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shznaqvi
Forum Newbie
Posts: 2
Joined: Wed Jan 06, 2010 3:33 am

How to display two linked tables

Post by shznaqvi »

Hello!

I want to display recordset of two linked tables as one categorical list.

Table Structure:
Table1 (fieldkey1, field2)
Table2 (fieldkey1, field2, Table1fieldkey3)

SQL:
rsTable1 = Select * from Table1
rsTable2 = Select * from Table2

Expected Result:

Colors(Table1)
Blue(Table2)
Green
Red
Yellow
:
:
Fruits(Table1)
Apple(Table2)
Orange(Table2)
:
:

Please also suggest if the following SQL statement would be better and how to use it:
SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.fieldkey1=Table2.table1fieldkey3
ORDER BY Table1.fieldkey1;
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How to display two linked tables

Post by requinix »

Code: Select all

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.fieldkey1=Table2.table1fieldkey3
ORDER BY Table1.fieldkey1;
Looks okay to me... but:
1) If you don't need everything from a table, don't SELECT * from it - just get the fields you need
2) You probably want to have another sorting field after the fieldkey1 - like Table2.fieldkey1


If you need help with the PHP code to display the lists, try it yourself first and then come to us if you have problems. Remember to post whatever code you have - especially if it isn't working.
shznaqvi
Forum Newbie
Posts: 2
Joined: Wed Jan 06, 2010 3:33 am

Re: How to display two linked tables

Post by shznaqvi »

I have managed to work it out. Can I improve on this? Can this be achieved using single recordset with JOINS?

CODE:

Code: Select all

<ul>
  <?php do { ?>
    <li><strong><?php echo $rowCate['CateNm']; ?></strong>
        <ul>
<?php $rsType = mysql_query($query_rsType, $ConnMainDb) or die(mysql_error());
        while($rowType=mysql_fetch_assoc($rsType)){
            if($rowCate['CateID'] == $rowType['TypeCateID']){
?>
  <li><em><?php echo $rowType['TypeNm']; ?></em></li><?php }}?>
      </ul>
    </li>
    <?php } while ($rowCate = mysql_fetch_assoc($rsCate)); ?>
</ul>
RESULT:
> Cate1
>>> Type1.1
>>> Type1.2
> Cate2
>>> Type2.1
>>> Type2.2
> Cate3
>>> Type3.1
>>> Type3.2
Post Reply