Is there a better way to group query results with a loop in PHP?_问答_开发者

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

开发者 https://www.devze.com 2023-01-14 16:38 出处：网络

that´s my first question in stackoverflow. I have two MYSQL tables: categories and products. I manage the query results with a while loop in PHP to group every category with its products. It´s the

相关专题：php while-loop

that´s my first question in stackoverflow.

I have two MYSQL tables: categories and products. I manage the query results with a while loop in PHP to group every category with its products. It´s the first time I do something so, and I think I made it very "crafty/hardcoded" (Sorry for my English). I think that should be a better way to do this. So, I´d like to ask to professional programmers. Here is my code:

 CREATE TABLE IF NOT EXISTS `categories` (
 `id` int(11) NOT NULL auto_increment,
 `name` text NOT NULL,
 PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;


 CREATE TABLE IF NOT EXISTS `products` (
 `id` int(11) NO开发者_Python百科T NULL auto_increment,
 `name` text NOT NULL,
 `description` text NOT NULL,
 `category` int(11) NOT NULL,
 `photo` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;

I have a query that returns products relationed with their category:

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

With PHP I manage the results to create an unordered list for every category with its products:

<?php
$category = '';//A control variable

while($row = mysql_fetch_array($query))
{
    //if its a new category I start a new <ul>
    if($row['category'] != $category)
    {
        //If it´s not the firt category I need to close previous one
        if($category != '')
        {
            echo "</ul>\n";
        }
        //I start the new <ul>
        echo '<h2>' . $row['category'] . '</h2>';
        echo '<ul>';
    }

    //I create the correspondient <li>
    echo '<li>' . $row['product'] . "</li>\n";

    //Asign the value of actual category for the next time in the loop
    $category = $row['category'];

}

//I neeed to close last <ul>
echo "</ul>\n";
?>

Is there a better way to do this operation? Thanks in advance for your answers!

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>

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!