Nita Nita - 1 month ago 7
MySQL Question

PDO Loop in the Loop - How to make it proper way?

I'm shifting to PDO from MySql_ AND/OR MySqli_ and I need some advice on how to get the things right. (Learning Process). In this case I have to loop thru categories (links_cat) and return all the records responding to this category from another table.(links). Code i have is working, but sure can be improved. And I would like to do it as professional as I can (without making in to crazy too.. (: Just clean, solid and fast code. Thank you for your advice in advance.

<?php

// Get Link Categories
$links_cat = $pdo->prepare("SELECT * FROM links_cat");

$links_categories = array();
if ($links_cat->execute()) {
while ($row = $links_cat->fetch(PDO::FETCH_ASSOC)):
$links_categories[] = $row;
$link_cat_id = $row['id'];
$link_cat_name = $row['name'];
echo $link_cat_name . "<br/>";

// Get Links in current Category

$links = $pdo->prepare("SELECT * FROM links WHERE category = '$link_cat_id' ORDER BY name");
$links->execute();

while ($link_row = $links->fetch()):
echo $link_name = $link_row['name']. "<br/>";
endwhile;

endwhile;
}

$pdo = null;

?>


UPDATE

I joined tables and return all results but i would like to list category only once, not every record.

<?php
$links_cat = "SELECT links_cat.name AS linkcat, links.link AS linkname FROM links_cat INNER JOIN links ON links.category=links_cat.id";
foreach($pdo->query($links_cat) as $row) {
echo $row['linkcat'] . "<br>";
echo $row['linkname'] . "<br>";
}
?>


Listing right now is like this:

Cat 1
- L1
Cat 1
- L2
Cat 2
- L3
Cat 2
- L4


I'm looking for listing like this:

Cat 1
- L1
- L2
Cat 2
- L3
- L4

Answer

Try changing your code like this

// ensure record is ordered by category name
$links_cat = "SELECT links_cat.name AS linkcat, links.link AS linkname FROM links_cat INNER JOIN links ON links.category=links_cat.id ORDER BY links_cat.name";

$prevCat = '';
foreach($pdo->query($links_cat) as $row) {  
    // if category name different from previous, show it
    if($row['linkcat'] != $prevCat) {
        echo $row['linkcat'] . "<br>";  
        $prevCat = $row['linkcat'];
    }
    echo $row['linkname'] . "<br>";  
}

SELECT N+1 issue is code issue where SELECT query is executed in a loop. If loop count is high, then it will degrade performance. It's called SELECT N+1 because query is executed N times in loop plus 1 for initial query.