Attached is a picture of the database layout I'm talking about. I would like to list out all the shirts in the database along with which categories they are in, and which textures and colors they come in. These are all many to many relationships, and hence the linking tables. I can't think of a way to do this. I think I will need a main foreach loop that loops through the shirts, then inside that, a foreach loop for the array of colors/textures/categories that each shirt has/falls under, if any. Any help is appreciated.
Recommended Answers
Jump to PostYou need to use multiple join clauses. Your shirts need to be left join as they should be listed regardless of whether they have a reference in a joined table. I don't know which package you'r eusing for MySQL GUI, but does it has an SQL output feature?
Some …
Jump to PostDownload manual: http://dev.mysql.com/doc/
Manual examples
http://dev.mysql.com/doc/refman/4.1/en/join.htmlExperiment with the JOIN syntax
All 6 Replies
diafol
You need to use multiple join clauses. Your shirts need to be left join as they should be listed regardless of whether they have a reference in a joined table. I don't know which package you'r eusing for MySQL GUI, but does it has an SQL output feature?
Some GUIs have an output feature that simplifies joining multiple tables.
baudday
0
Junior Poster in Training
Thanks for the reply!
I'm actually using phpMyadmin and it does have that feature. Could you possibly give me a link that describes how joining works? I'm a little confused on it. If I left join shirts, what do I do with say, colors? Will this give me results as if I'm querying one table? This would be absolutely great!
baudday
0
Junior Poster in Training
Like after I do that would I be able to echo out colors for a shirt like:
foreach($result as $row)
{
echo $row->colorHex;
}How would I get this to only give the colors related to that shirt? Some shirts come in multiple colors, so they will have multiple entries in the shirtcolorslink table.
baudday
0
Junior Poster in Training
I'm doing it like this since I don't want to list the same shirt multiple times. Is this the best way or is there a better way?
<html>
<head>
</head>
<body>
<table>
<tr><td><b>Name</b></td><td><b>Description</b></td><td><b>Colors</b></td><td><b>Textures</b></td></tr>
<?php
if($colors->num_rows() > 0):
foreach($shirts->result() as $row):?>
<tr><td style="border-left: 1px solid #000000;"><?=$row->shirtName?></td>
<td style="border-left: 1px solid #000000;"><?=$row->Description?></td>
<td style="border-left: 1px solid #000000;">
<?php foreach($colors->result() as $color):
if($color->shirtId == $row->shirtId):?>
<div style="background-color: #<?=$color->colorHex?>;">#<?=$color->colorHex?></div>
<?php endif;?>
<?php endforeach;?>
</td></tr>
<?php foreach($textures->result() as $texture):
if($texture->shirtId == $row->shirtId):?>
<div style="background-image: url(textures/<?=$texture->file?>);
width: 32px;
height: 32px;
display: block;"></div>
<?php endif;?>
<?php endforeach;?>
<?php endforeach;?>
<?php endif;?>
</body>
</html>
diafol
Download manual: http://dev.mysql.com/doc/
Manual examples
http://dev.mysql.com/doc/refman/4.1/en/join.html
Experiment with the JOIN syntax
ppetree
2
Junior Poster
You'll probably get more detailed help if you post this in the mysql forum here or on the mysql site.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.