I am trying to generate a simple list from a series of tables in a MySQL database. The SQL I am using is:
$resource_sql = "SELECT prod_spec_sheets.spec_uri, prod_spec_sheets.spec_title,
prod_photos.photo_uri, prod_photos.photo_title,
prod_diagrams.diag_uri, prod_diagrams.diag_title,
prod_ies.ies_uri, prod_ies.ies_title,
prod_instruction_sheets.instr_sheet_uri, prod_instruction_sheets.instr_sheet_title
FROM products
LEFT JOIN prod_spec_sheets ON products.prod_id = prod_spec_sheets.prod_id
LEFT JOIN prod_photos ON products.prod_id = prod_photos.prod_id
LEFT JOIN prod_diagrams ON products.prod_id = prod_diagrams.prod_id
LEFT JOIN prod_ies ON products.prod_id = prod_ies.prod_id
LEFT JOIN prod_instruction_sheets ON products.prod_id = prod_instruction_sheets.prod_id
WHERE products.prod_code = '$product_code'";
The PHP I am using to display it looks like this:
if ($resource_num_rows > 1){
echo '<h2>Downloads</h2>';
while($row = mysql_fetch_array($resource_result, MYSQL_ASSOC)){
echo "<ul>";
$count = 0;
foreach ($row as $key => $value) {
if ($count % 2) {
echo '">' . $value . '</a></li>' . PHP_EOL;
} else {
echo '<li><a href="' . RESOURCES_URI . $value;
}
$count++;
}
echo "</ul>";
}
}
Which generates the following HTML:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
</ul><ul><li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
When what I really need is:
<ul>
<li><a href="http://URL/ss_pil.pdf">Specs</a></li>
<li><a href="http://URL/pic_pil.jpg">Photo</a></li>
<li><a href="http://URL/pd_pil.TIF">Diagram</a></li>
<li><a href="http://URL/is_pil.pdf">Instructions</a></li>
<li><a href="http://URL/isu_pil.pdf">Instructions (ALT)</a></li>
</ul>
Each table (except products) could have many items associated with a product, this example only shows what happens if there is more than one set of instructions in the prod_instruction_sheets table.
I'm not sure if the answer is in a better MySQL statement, or a change in the PHP to my loop. Any help would be gratefully received.