Left Join 3 tables or not?

[mcfmullen]

Alright this is going to be a doozy:

 

I have 3 tables: Collectibles, CollectiblesMethods and CollectionsCollectibles

 

Collectibles contains: ID, Photo, Collectible, Value, Resale, Rarity

CollectiblesMethods contains: ID, Collectible, Method

CollectionsCollectibles contains: ID, Collectible, Collection, Amount

 

Needless to say, this is a many to many relationship model. (many methods to one collectible, many collectibles to one collection).

 

The page collectibles.php is contained inside collspec.php

 

collspec.php shows information relating to a specific collection called by GET.

collectibles.php shows all collectibles belonging to the same specfic collection in collpec.php, thus why it is contained inside it.

 

I want collectibles.php to output the following:

Photo, Collectible, Amount, Resale, Rarity, Method

 

I already have collectibles.php outputting:

Photo, Collectible, Amount, Resale, Rarity

 

Method is proving to be difficult. I can either get it to show the word None for every collectible with this code:

<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td>
<?php
$sql3 = "SELECT CollectiblesMethods.Method FROM CollectiblesMethods WHERE Collectible = '" . $row2['Collectible'] . "'";
$obtain = mysql_query($sql3);
if (trim($row['Method']) != '') 
while($row3 = mysql_fetch_array($obtain)){
?>
<tr>
<td><?php echo $row3['Method']; ?></td>
</tr>
<?php
} else 
  echo 'None';
?>
</td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

 

or I can get it to show the Methods (of which certain collectibles have more than one method) using this code but with a catch; it repeats the photo, name, value and resale if there is more than one method for a given collectible:

<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible LEFT JOIN CollectiblesMethods ON Collectibles.Collectible = CollectiblesMethods.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td><?php echo $row2['Method']; ?></td>
<?php
} else 
  echo 'None';
?>
</tr>
</table>

 

So Let's say I have a collection called Toys. This is how it outputs using my above codes:

 

1st Code example:

__________________________________________

|Photo|Name |Amount|Sells For  |Rarity      |Obtained|

|Pic1  |Marble| x5        | 20 cents | Common | None    |

|Pic2  |Train  | x1      | 5 $          | Rare      |  None    |

 

2nd Code example:

__________________________________________

|Photo|Name |Amount|Sells For  |Rarity      |Obtained|

|Pic1  |Marble| x5        | 20 cents | Common | Barter  |

|Pic1  |Marble| x5        | 20 cents | Common | Trade    |

|Pic2  |Train  | x1      | 5 $          | Rare      |  Gift      |

 

How it should output:

__________________________________________

|Photo|Name |Amount|Sells For  |Rarity      |Obtained|

|Pic1  |Marble| x5        | 20 cents | Common | Barter  |

|        |          |            |              |              | Trade    |

|Pic2  |Train  | x1      | 5 $          | Rare      |  Gift      |

 

As you can see, I want it to output all methods obtained in the same box, increasing the rowspan as necessary.

 

I don't want it to output None (since this is not true) as in the first example.

I don't want it to repeat the same information simply to add a line for the next method as in the second example.

 

Can someone help me figure out what I'm doing wrong?

[s0c0]

This is really a question for the mysql forum.  Try looking into using a GROUP BY.  Information on that can be found by googling: mysql group by.  If that does not help, then post your tables create syntax and I'll look at it later.

[radar]

The way I do my joins on 2 or more tables using left join is like this, perhaps it'll help you as well.

 

$sql = "SELECT SQL_CALC_FOUND_ROWS cust.id, cust.f_name, cust.l_name, 
							cust.email, cust.password, cust.status,
							COUNT( o.id ) AS orders_cnt, 
							COUNT( d.id ) AS deals_cnt,
							d.c_id as dc_id, o.c_id
							FROM customers AS cust
							LEFT JOIN orders AS o ON o.c_id = cust.id
							LEFT JOIN deals AS d ON d.c_id = cust.id
							GROUP BY o.c_id, dc_id, cust.id ORDER BY cust.l_name LIMIT ".
							Paginate::getCurrentIndex().",".Paginate::getLimit();

[DavidAM]

Needless to say, this is a many to many relationship model. (many methods to one collectible, many collectibles to one collection).

 

Actually, for the record, these are one-to-many relationships. I'm not trying to be negative here, just want to make sure you identify the data model correctly.

 

The standard way to handle this is to keep track of the repeating fields and just not print them if the value is the same as the last record. If setting rowspan is a requirement, you're going to have to use multiple queries, one to get the number of rows and then queries to retrieve and display the data.

 

However, mysql has a function that could help and might produce acceptable results. GROUP_CONCAT().

SELECT id, name, GROUP_CONCAT(child) AS ChildList ... GROUP BY id

Which would return a single row for the id (and name) and separate all of the "child" names with a comma in a single field. However, you can specify the separator. If you use the HTML linebreak (<BR>) it would put each child name on a separate line in the (HTML) table when displayed in the browser:

SELECT id, name, GROUP_CONCAT(child SEPARATOR '<BR>') AS ChildList ... GROUP BY id

 

 

[mcfmullen]

Are you sure it isn't many to many? My description fo it may be off but in actuality, many methods can apply to many collectibles which apply to many collections.

 

In any case, I will ive this a try and let everyone know the results!

[mcfmullen]

Success!

<table width="99%">
<tr>
<th colspan="6"><h2>Collectibles</h2></th>
</tr>
<tr>
<td>Photo</td>
<td>Name</td>
<td># Needed</td>
<td>Sells For</td>
<td>Rarity</td>
<td>Obtained</td>
</tr>
<?php 
$sql2 = "SELECT * FROM CollectionsCollectibles LEFT JOIN Collectibles ON CollectionsCollectibles.Collectible = Collectibles.Collectible WHERE Collection = '" . mysql_real_escape_string($item) . "'";
$collectible = mysql_query($sql2);
if (trim($row['Collectible']) != '') 
while($row2 = mysql_fetch_array($collectible)){
?>
<tr>
<td width="14%" valign="top"><img src="<?php echo "$img{$row2['Photo']}"; ?>"></td>
<td><?php echo $row2['Collectible']; ?></td>
<td><?php echo $row2['Amount']; ?></td>
<td><?php echo $row2['Sell']; ?></td>
<td><?php echo $row2['Rarity']; ?></td>
<td>
<?php
$sql3 = "SELECT Collectible,GROUP_CONCAT(Method SEPARATOR '<BR>') As Methods FROM CollectiblesMethods WHERE Collectible = '" . $row2['Collectible'] . "'";
$CollectibleMethod = mysql_query($sql3);
$row3 = mysql_fetch_array($CollectibleMethod)
?>
<?php echo $row3['Methods']; ?>
</td>
<?php
} else 
  echo 'None';
?>
</td>
</tr>
</table>