CODEWITHSUNDEEP
phpmysqlpdo
Brainy Prb
Brainy Prb

Reputation: 303

How to add results of multiple queries to an array in PDO?

Below is my code

$stmt = $db->prepare("SELECT * FROM inbox ORDER BY `date` DESC ");
$stmt->execute(array());
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);        

$uniques = count(array_unique($row, SORT_REGULAR)); 

for ($i=0; $i < $uniques; $i++) {

    $inbox_id = $row[$i]["inbox_id"];

    $stmt = $db->prepare("SELECT * FROM messages WHERE inbox_id=? AND seen=? ORDER BY `date` DESC");
    $stmt->execute(array($inbox_id, 0));
    $row_msg = $stmt->fetchAll(PDO::FETCH_ASSOC);                
}

return $row_msg;

But this is nor returning all data.It is only returning values got from first iteration only.

Upvotes: 0

Views: 115

Answers (2)

JHoffmann
JHoffmann

Reputation: 727

Your PDO statement is prepared every round inside the loop, that is not necessary and i hope the inbox_id column is a primary key in the inbox table:

$stmt1 = $db->query("SELECT `inbox_id` FROM inbox ORDER BY `date` DESC ");
$stmt2 = $db->prepare("SELECT * FROM messages WHERE inbox_id=? AND seen=0 ORDER BY `date` DESC");
while($id = $stmt1->fetchColumn(0)) {
    $stmt2->execute(array($id));
    $row_msg[$id] = $stmt2->fetchAll(PDO::FETCH_ASSOC);
}    
return $row_msg;

If you do not want a multidimensional array you can just add the row to the result array by using array_merge() (PHP Manual: array_merge) inside the loop (instead of the line with $row_msg[$id]):

$row_msg = array_merge($row_msg, $stmt2->fetchAll(PDO::FETCH_ASSOC));

Upvotes: 0

Suchit kumar
Suchit kumar

Reputation: 11859

you can use:

$row_msg[$i] = $stmt->fetchAll(PDO::FETCH_ASSOC);

Upvotes: 2

Related Questions