ansaurus

Question

Is there a better way to group query results with a loop in PHP?

Answer 1

+1 A:

I would suggest reading into Hierarchical Data. A great read. You should particularly pay attention to the Nested Set Model.

It does take a bit to learn and also takes a bit to implement, but is well worth it for the end result!

Brad F Jacobs 2010-09-01 18:28:43

Wow, I didn´t know I was in a so complex situation!Thanks for the link. It will be very usefull for me to learn more about databases, the nested set model and the implementation of data with tree structure.

FranQ 2010-09-01 18:58:21

we use that same hierarchal approach where i work, and it's by no means easy. But the alternatives are even worse. Unlimited-nested categories and multi-parent nodes are very complicated beasts.

hopeseekr 2010-09-01 19:27:10

Yea, it is not easy at all, especially when adding / creating items. But once the system is in place it is 10x easier and more efficient than the alternative. It is worth reading up on to gain the knowledge at any rate :)

Brad F Jacobs 2010-09-01 19:31:34

Answer 2

+1 A:

It's always best not to mix HTML with PHP.

Since you wanted my advice (veteran PHP coder of some 12 years), I'll just code it from scratch real fast:

<?php
$pdo = new PDO($dsn, $user, $pass);

$sql = "SELECT categories.name as category,products.name as product
FROM categories
INNER JOIN products ON(categories.id = products.category)";

$stmt = $pdo->prepare($sql);
$stmt->execute();

$categories = array();
while (($row = $stmt->fetch(PDO::FETCH_ASSOC)))
{
    $category = $row['category'];
    $categories[$category][] = $row['product'];
}

// In your view:
?>
<html>
    <body>
<?php
    foreach ($categories as $category => $products)
    {
?>
        <h2><?php echo $category; ?></h2>
        <ul class="products">
<?php
        foreach ($products as $product)
        {
?>
            <li><?php echo $product; ?></li>
<?php
        }
?>
        </ul>
<?php
    }
?>
    </body>
</html>

hopeseekr 2010-09-01 18:35:23

Thanks for your code and your advice. I think it´s the exact answer for my question: create an array with the results and open it with a foreach().The link given by premiso about Hierarchical Data it´s very usefull too.

FranQ 2010-09-01 19:10:07

Could you also vote up my answer? it helps my reputation. thanks, bro.

hopeseekr 2010-09-01 19:25:53

Also, please avoid nesting HTML in PHP wherever possible. It's **ok** and even **preferable** to embed **logic-free** code in an HTML template, but **totally wrong** to embed business logic in a view. E.g. if you ever find yourself doing much more than outputting text in a view, you're doing it wrong.

hopeseekr 2010-09-01 19:28:36

I tried to vote up your answer, but I cant due to still haven´t 15 of reputation. Asap I have 15 I´ll vote it up.Thanks for the advice. I´ll keep it in mind. I actually work with CodeIgniter, and I´m trying to keep my views as clean as possible.

FranQ 2010-09-01 19:59:57

ansaurus

tags:

views:

answers:

Is there a better way to group query results with a loop in PHP?

related questions