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.

Edited by baudday: Forgot to attach image

Attachments database.jpg 110.72 KB
8 Years
Discussion Span
Last Post by ppetree

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.


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!


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.


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?

	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;?>
			<?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;?>

You'll probably get more detailed help if you post this in the mysql forum here or on the mysql site.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.