making a foreach loop inside an SQL query or do it dynamically via PHP? -

i have 2 tables in db, polyptychs , illustrations. pk of polyptychs fk in illustrations. want is:

select polyptychid polyptychs 

and subsequently, foreach id returned need illustrations. via php solution this(using pdo sintax):

<?php //create connection db $sql = "select polyptychid, polyptych_image polyptychs"; $stmt = $this->dbh->query($sql); $resulttmp = $stmt->fetchall(pdo::fetch_assoc); $final_result  = array(); foreach($resulttmp $val){   $id = $val['polyptychid'];   $final_result["$id"]["image"] = $val['polyptych_image'];   $sql2 = "select * illustrations 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 polyptychid key of array , relative image , illustrations (if there's no error in code). want know if there easier way it, maybe doing via sql, , best solution. thanks

here's i'd do, if you:

select p.polyptychid id,     p.polyptych_image image,     i.* polyptychs p inner join illustrations     on i.polyptychid = p.polyptychid 

in light of comment, check handy graph understand why inner join chose use here:

then, format array in way want it, 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 in 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 fields in illustrations called either id or image, you'd have change query p.polyptychid p_id or something. make sure aliasses unique , you'll fine.

on re-use of prepared statements, seems people tend overlook, can use same prepared statements on , over. luck have it, in fact, wrote answer matter morning. use basic examples might applicable case, too. if -for reason- can't join, can write:

$stmt = $pdo->prepare(     'select * illustrations polyptychid = :pid' ); $basestmt = $pdo->query(     'select polyptychid, polyptych_image 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); }