Page 1 of 1

How to display two linked tables

Posted: Wed Jan 06, 2010 4:01 am
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;

Re: How to display two linked tables

Posted: Wed Jan 06, 2010 4:08 am
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.

Re: How to display two linked tables

Posted: Wed Jan 06, 2010 5:13 am
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