Mysql – How to get list of items + related items using same query

To elaborate:

Step 1, get list of all products:

SELECT h.`ProdId`, h.`ProdName`
FROM `products` AS h

Step 2, add related products:

SELECT h.`ProdId`, h.`ProdName`, r.`ProdId_2`, d.`ProdName`
FROM `products` AS h
LEFT JOIN `related_products` AS r ON r.`ProdId_1` = h.`ProdId`
LEFT JOIN `products` AS d ON d.`ProdId` = r.`ProdId_2`;

This example has not been tested, it is code off the top of my head. The result returned will not be exactly what you want. You will need to use PHP code to get it as you want. You should get 2 listings for product 1 and 5. You can use ‘ORDER BY’ to sort on the DB side.

Post back if you have any questions/problems.

UPDATE:

You can get the results you want if you try this query:

SELECT h.`ProdId`, h.`ProdName`, r.`ProdId_2` AS ProdId, d.`ProdName`
FROM `products` AS h
LEFT JOIN `related_products` AS r ON r.`ProdId_1` = h.`ProdId`
LEFT JOIN `products` AS d ON d.`ProdId` = r.`ProdId_2`
ORDER BY h.`ProdId` ASC;

Since this query is not tested you may have to play with a bit.
Post back if you have any questions/problems.

UPDATE 2:

Ok, after some sleep I see a problem with my previous update. This code is tested and returns the data you want. But, to put it in the format you want you need to use PHP code. I took into account that products can have more than 1 related item.

$pdo = new Database();
$sql = "SELECT h.`ProdId`, h.`ProdName`, r.`ProdId_2`, d.`ProdName` AS rProdName
        FROM `products` AS h
        LEFT JOIN `related_products` AS r ON r.`ProdId_1` = h.`ProdId`
        LEFT JOIN `products` AS d ON d.`ProdId` = r.`ProdId_2`
        ORDER BY h.`ProdId` ASC;";
$pdo->query($sql);
$results = $pdo->resultSet();
$newArray = array();
if($pdo->rowCount())
{
 $cur = null;
 foreach($results as $row)
 {
  if($cur != $row["ProdId"])
  {
   $newArray[] = array("ProdId" => $row["ProdId"], "ProdName" => $row["ProdName"]);
   $cur = $row["ProdId"];
  }
  if(!empty($row["ProdId_2"]))
  {
   $newArray[] = array("ProdId" => $row["ProdId_2"], "ProdName" => $row["rProdName"]);
  }
 }
}

$newArray will contain the data, in the format of your sample data. Hope this helps.