How to display multiple categories and products underneath each category?

Generally there is a category menu and each menu links to a category page where shows all the items under that category.

Now I need to show all the categories and items underneath with PHP/MySQL in the same page.

So it will be like this.

Category 1
description of category 1
item 1 (some details, such as name, thumbnail etc for each item)
item 2
..

Category 2
description of category 2
item 5
item 6
..

Category 3
description of category 3
item 8
item 9
...

...

I have category and product table in my database.

It seems like I have to loop through categories and under each category I have loop through to get all the items.

But I am 开发者_如何学JAVAnot sure how to proceed.

CREATE TABLE IF NOT EXISTS `omc_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `product_order` int(11) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  `grouping` varchar(16) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL,
  `category_id` int(11) NOT NULL,
  `featured` enum('none','front','webshop') NOT NULL,
  `other_feature` enum('none','most sold','new product') NOT NULL,
  `price` float(7,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



CREATE TABLE IF NOT EXISTS `omc_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `status` enum('active','inactive') NOT NULL,
  `parentid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

The simple way: do one query to fetch the categories, and then a separate query to fetch each list of products. Something like:

<?php
    // Shortcuts to essential string-escaping functions
    //
    function h($s) {
        echo htmlspecialchars($s, ENT_QUOTES);
    }
    function u($s) {
        echo urlencode($s);
    }
    function m($s) {
        return "'".mysql_real_escape_string($s)."'";
    }

    // Get all categories
    //
    $q= mysql_query('SELECT id, name, shortdesc FROM omc_category WHERE status='active' ORDER BY id');
    $categories= array();
    while ($categories[]= mysql_fetch_assoc($q)) {}
?>

<?php foreach ($categories as $category) { ?>
    <h2><?php h($category['name']); ?></h2>
    <p><?php h($category['shortdesc']); ?></p>
    <?php
        // Get all products for a category
        //
        $q= mysql_query('SELECT id, name FROM omc_product WHERE status='active' AND category_id='.m($category['id']));
    ?>
    <ul>
        <?php while ($product= mysql_fetch_assoc($q)) { ?>
            <li>
                <a href="/product.php?id=<?php u($product['id']); ?>">
                    <?php h($product['name']); ?>
                </a>
            </li>
        <?php } ?>
    </ul>
<?php } ?>

(Note: the m() call here is not strictly necessary because integers are safe to insert into an SQL query without escaping. But it's not a good idea to get into that habit, as if you did it with strings you'd have an SQL-injection security hole. I'm using the mysql_ interface with manual escaping here; you're usually much better off with parameterised queries using mysqli_ or PDO, where available. This prevents you from having to worry about SQL escaping. The same goes for u()'s URL-encoding: not necessary for integers, but generally worth getting into the habit of using.)

For a simple site, this would be OK. For a larger site with many categories you might want to get all your products in a single query, which you could do like:

SELECT omc_product.id, omc_product.name, omc_product.category_id
FROM omc_product
JOIN omc_category ON omc_category.id=omc_product.category_id
WHERE omc_product.status='active' AND omc_category.status='active'
ORDER BY category_id, id

and then on each row you get back, check to see if the category_id has changed since the previous row. If it has, output the name and shortdesc of the corresponding category object from the previous query. (Or you can select omc_category.name and shortdesc in the above query too to get them at the same time, though you might not want to do that if the descriptions can be quite long.)

Also, consider using an InnoDB table with proper FOREIGN KEY references between your tables. There's little good reason to be using MyISAM today (except for FULLTEXT).

`parentid` int(11) NOT NULL,

Oh, I hope that doesn't mean you've got hierarchical categories! That would make everything more complicated. See also: nested sets.

It seems like I have to loop through categories and under each category I have loop through to get all the items.

1. you query to get all your categories
2. you loop your categories • you display the category general header • you query to get all the items of that category
3. you loop through the items and display them

SELECT A.`id` AS catid, A.`name` AS catname, ..., B.`id` AS prodid, ...
FROM `omc_category` A LEFT JOIN `omc_product` B ON (B.`category_id` = A.`id`)
ORDER BY A.`id`

You dind't even need to order by category, but it makes processing the result easier.

NOTE: if the result set is very large, I would favor Lo'oris's approach.