[RESOLVED] Displaying multipul results in a table??

[shozza87]

Hi there i currently have a many 2 many table relationship in my database so i created another table to stop this from happening e.g

I had Competition and Teams and many Teams can enter many Competitions...

So i created entercomp which stopped the many 2 many and this only has Teamid and compid and now i have created a table on my website and i want to display the current competitions that the team is in but i can only display the teams compid is there i way that i could get the competitions name and stuff?

        <table width="500" border="1" cellspacing="0" cellpadding="3">
<tr>
<td width="20%">CompID</td>
<td width="40%">Comp Name</td>
<td width="40%">Comp Type</td>
</tr>

<?php
// Retrieve data from database
$Query3="SELECT * FROM entercomp where teamid = $teamid";
$result=mysql_query($Query3);
// loops so all the data from the database will be added
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td width="20%"><?php echo $rows['compid']; ?></td>
<td width="40%"><?php echo $rows['compname']; ?></td>
<td width="40%"><?php echo $rows['comptype']; ?></td>
</tr>

<?php
}

// loop had to be closed after the table because the table wasnt being populated.

mysql_close();
?>

</table>

Thanks for looking

[laserlight]

You should use a join, possibly of all three tables. If you posted more information about the tables and their relationships, it would be easier to construct a direct example.

[shozza87]

how do i get details of all my tables im using phpmyadmin thanks

[niroshan]

Hi shozza87,
By reading your example code I have obtained the table field names. Please change them if there is any change.

As laserlight says you can simply use a join to do it as,

SELECT A.compid, A.compname, A.comptype FROM competitions A LEFT JOIN entercomp B ON A.compid = B.compid where B.Teamid=<-- selected team ID -->

hope this help,
Best Regards,
niroshan

[shozza87]

sorry i havent heard of Join how come the A's and B's are in there sorry if this is a stupid question

[niroshan]

Hi shozza87,

No its not a stupid question 🙂

Well Actually those As and Bs are called table alias. Well its doesn`t have to be A or B it can be any thing... simply we use alias in our queries because it makes a query easy to read and write.

we can write the same query as,

SELECT competitions.compid, competitions.compname, competitions.comptype FROM competitions  LEFT JOIN entercomp  ON competitions.compid = entercomp.compid where entercomp.Teamid=1

which one is easy to write?🙂 query with alias right?

One important thing about using alias is that assume your querying two tables which has same column names but different values and you want records from both tables. In this case we have to use alias values to tell the database engine that we need data from both tables which has same field name.

Here is a small example:

products

id
name
comment

product_comments

id
productId
comment

now if you want the product with their comments you have to use alias when querying the tables because as you can see comment field is present in both tables.

so simple query will be,

SELECT A.id, A.name, B.comment FROM products A LEFT JOIN product_comments B ON B.productId = A.id AND A.id = <-- product id -->

Hope this clear your doubts.

Please read more about joins since that will reduce the no of queries you have to use to achieve something.

best regards,
niroshan

[shozza87]

thanks very much that has cleared it up for me