CODEWITHSUNDEEP
phpmysqlpdo
Nita
Nita

Reputation: 561

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

Upvotes: 0

Views: 101

Answers (1)

Niyoko
Niyoko

Reputation: 7662

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.

Upvotes: 1

Related Questions