Member Avatar for baudday

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.

database.jpg 110.72 KB
Edited by baudday because: Forgot to attach image
  • 3 Contributors
  • 6 Replies
  • 124 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by ppetree
Member Avatar for 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.

Member Avatar for baudday

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!

Member Avatar for baudday

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.

Member Avatar for baudday

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>
Member Avatar for ppetree

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.