PHP/MySQL How do I remove duplicate information from a LEFT JOIN?

Question

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"&gt;Specs&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pic_pil.jpg"&gt;Photo&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pd_pil.TIF"&gt;Diagram&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/is_pil.pdf"&gt;Instructions&lt;/a&gt;&lt;/li&gt;
</ul><ul><li><a href="http://URL/ss_pil.pdf"&gt;Specs&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pic_pil.jpg"&gt;Photo&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pd_pil.TIF"&gt;Diagram&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/isu_pil.pdf"&gt;Instructions (ALT)</a></li>
</ul>

When what I really need is:

<ul>
<li><a href="http://URL/ss_pil.pdf"&gt;Specs&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pic_pil.jpg"&gt;Photo&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/pd_pil.TIF"&gt;Diagram&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/is_pil.pdf"&gt;Instructions&lt;/a&gt;&lt;/li&gt;
<li><a href="http://URL/isu_pil.pdf"&gt;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.

Answer 1

Try to SELECT only the field you need and use the keyword DISTINCT in your statement:

$resource_sql = "SELECT DISTINCT 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'";

Edit:

Ok i see what you are doing, one easy way to filter this column in PHP is to put the line you are writting into an associative array and check if you have already write back this data. Move also your UL outside of the while loop if you don't have two list.

Example:

if ($resource_num_rows > 1){
    echo '<h2>Downloads</h2>' . PHP_EOL . '<ul>' . PHP_EOL;
    $seen=array(); //use to filter te line written
    while($row = mysql_fetch_array($resource_result, MYSQL_ASSOC)) {
        $count = 0;
        foreach ($row as $key => $value) {
            if ($count % 2) {
                $line .= '">' . $value . '</a></li>';

                // check if the line has already been output
                if (!array_key_exists($line, $seen)) {
                    // no mark it as written
                    $seen[$line]=true;

                    // and output the line
                    echo $line . PHP_EOL;
                }
            } else {
                $line='<li><a href="' . RESOURCES_URI . $value;
            }
            $count++;
        }
    }
    echo "</ul>";
}

Answer 2

GROUP BY products.prod_id smells like it should work at first glance.