在SQL查询中创建一个foreach循环或通过PHP动态地执行它？

Question

I have 2 tables in my DB, Polyptychs and Illustrations. The PK of Polyptychs is FK in Illustrations. What I want to do is:

SELECT polyptychID FROM Polyptychs

and subsequently, foreach ID returned I need all illustrations. Via PHP the solution is something like this(using PDO sintax):

<?php
//create the connection with DB
$sql = "SELECT polyptychID, polyptych_image FROM Polyptychs";
$stmt = $this->DBH->query($sql);
$resultTmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
$final_result  = array();
foreach($resultTmp as $val){
  $id = $val['polyptychID'];
  $final_result["$id"]["image"] = $val['polyptych_image'];
  $sql2 = "SELECT * FROM Illustrations WHERE polyptychID = :polyID";
  $stmt2 = $this->DBH->prepare($sql2);
  $stmt2->execute(array('polyID' => $id));
  $resultTmp2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
  $final_result["$id"]["illustrations"] = $resultTmp2;
  unset($id);
  unset($sql2);
  unset($stmt2);
  unset($resultTmp2);
}
?>

Now $final_result contains all polyptychID as key of the array and its relative image and illustrations (if there's no error in the code). What I want to know is if there is an easier way to get it, maybe doing it via SQL, and what is the best solution. Thanks

Answer 1

Here's what I'd do, if I were you:

SELECT p.polyptychID as ID,
    p.polyptych_image as image,
    i.*
FROM Polyptychs p
INNER JOIN Illustrations i
    ON i.polyptychID = p.polyptychID

In light of your comment, check this handy graph to understand why an INNER JOIN is what I chose to use here:

Then, to format the array in the way you want it, just write this:

$formatted = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $id = $row['ID'];
    if (!isset($formatted[$id]))
    {
        $formatted[$id] = array(
            'image' => $row['image'],
            'illustrations' => array() //we'll set this in a minute
        );
    }
    unset($row['ID'], $row['image']);//remove id & image values
    $formatted[$id]['illustrations'][$row['illustrationsID']] = $row;//assign rest of result-set
}

Of course, if there are fields in the Illustrations called either ID or image, then you'd have to change the query to p.polyptychID AS p_ID or something. Just make sure the aliasses are unique and you'll be fine.

On the re-use of prepared statements, it seems to be something people tend to overlook, but you really can use the same prepared statements over and over. As luck would have it, in fact, I wrote an answer about this matter just this morning. I use some basic examples that might be applicable to your case, too. So if -for some reason- you can't do a join, you can simply write:

$stmt = $pdo->prepare(
    'SELECT * FROM Illustrations WHERE polyptychID = :pId'
);
$baseStmt = $pdo->query(
    'SELECT polyptychID, polyptych_image FROM Polyptychs'
);
$result = array()
while($row = $baseStmt->fetch(PDO::FETCH_ASSOC))
{
    $result[$row['polyptychID']] = array(
        'image' => $row['polyptych_image'],
        'illustrations' => null
    );
    $stmt->execute( array(':pId' => $row['pId']));
    $result[$row['polyptychID']]['illustrations'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}