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

Question

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

Use JOIN in database, not in client side. SELECT * FROM links_cat lc JOIN links c ON l.category = lc.id Your code has SELECT N+1 issue. — Niyoko
– Niyoko, Commented Oct 12, 2016 at 0:47
could you so kind to give some code example on how to disply data (i updated my question so you can see how i want this data to be displayd). Cuold also tell me more on SELECT N+1 issue (what do you mean here?) — Nita
– Nita, Commented Oct 12, 2016 at 1:50

Niyoko · Accepted Answer · 2016-10-12 03:15:35Z

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.

this is cool, thanks a lot, works very well, i made some speed comparations, yes is good.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related