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;
How to display two linked tables
Moderator: General Moderators
Re: How to display two linked tables
Code: Select all
SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.fieldkey1=Table2.table1fieldkey3
ORDER BY Table1.fieldkey1;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
I have managed to work it out. Can I improve on this? Can this be achieved using single recordset with JOINS?
CODE:
RESULT:
> Cate1
>>> Type1.1
>>> Type1.2
> Cate2
>>> Type2.1
>>> Type2.2
> Cate3
>>> Type3.1
>>> Type3.2
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>> Cate1
>>> Type1.1
>>> Type1.2
> Cate2
>>> Type2.1
>>> Type2.2
> Cate3
>>> Type3.1
>>> Type3.2