CODEWITHSUNDEEP
phpmysql
Filip Krstic
Filip Krstic

Reputation: 713

Displaying and grouping two joined tables in MySQL

I`ve a problem with grouping and displaying results from two tables in MySQL db.

I`m trying to make a shopping cart, and I have realised a shop menu which contains products categories and via jquery when category is clicked, menu expands and show products from that category.

Example of categories table:

+------------+
| ID | NAME  |
+------------+
| 1  | name1 |
| 2  | name2 |
| 3  | name3 |
| 4  | name4 |
+------------+

Example of products table:

+------------------------------------------------------------------------+
| ID | PR_CODE  | NAME  | DIMENSIONS | COLORS | OFFER | PRICE | CATEGORY |
+------------------------------------------------------------------------+
| 1  | pr_code1 | prod1 | 40 x 40    | blue   |   1   | 11.00 |    1     |
| 2  | pr_code2 | prod2 | 120 x 120  | white  |   1   | 12.00 |    1     |
| 3  | pr_code3 | prod3 | 60 x 120   | yellow |   0   | 13.00 |    2     |
| 4  | pr_code4 | prod4 | 40 x 60    | orange |   0   | 14.00 |    3     |
+------------------------------------------------------------------------+

Category row in products table telling which category product belongs.

Code for menu:

<div class="shopMenu">
    <ul>
    <?php
    $sql = "SELECT categories.*, products.id as prodId, products.name as prodName FROM `categories` LEFT JOIN `products` ON categories.id = products.category ORDER BY categories.id DESC";
    $query = execute_select($sql);      
        foreach($query as $row) {
            $id = $row['id'];
            $name = $row['name'];
            $prodId = $row['prodId'];
            $prodName = $row['prodName'];
            echo '<li><a href="#">' . $name . '</a>
                    <ul>
                        <li><a href="&id=' . $prodId . '">' . $prodName . '</a></li>
                    </ul>               
                   </li>'; }
    ?>                
    </ul>
</div>

This code works, but works wrong. If I put sql query as:

$sql = "SELECT categories.*, products.id as prodId, products.name as prodName FROM `categories` LEFT JOIN `products` ON categories.id = products.category ORDER BY categories.id DESC";

Result is: all categories are listed but categories which contains more products are repeated in main menu as many times as there are products in this category.

If I put sql query as:

$sql = "SELECT categories.*, products.id as prodId, products.name as prodName FROM `categories` LEFT JOIN `products` ON categories.id = products.category GROUP BY categories.id ORDER BY categories.id DESC";

Result is: all categories are listed once, but only one of products from chosen category is listed, missing other products.

Can somebody help with this query?

Upvotes: 1

Views: 2362

Answers (4)

krtek
krtek

Reputation: 26597

There is basically two ways of doing what you want, both of them have been explained in the other response and in the comments. I will try to expand a little about them in this answer.

One query

You retrieve all the data in one query and then use PHP to do some transformation in order to show them in the appropriate way.

There's two approaches to do that. You can modify the data first and then loop on them to display the menu or you can do everything in just one loop.

Two steps variant

First, we create an array containing the data in a more "usable" way for us, and then we display the content of this new array :

$sql = "SELECT categories.id as catid, categories.name as catname, products.id as prodId, products.name as prodName 
        FROM `categories` 
        INNER JOIN `products` ON categories.id = products.category 
        ORDER BY categories.id DESC";

$result = execute_select($sql);
$categories = array();
foreach($query as $row) {
    if( ! isset($categories[$row['catid']])) {
        $categories[$row['catid']] = array(
            'id' => $row['catid'],
            'name' => $row['catname'],
            'products' => array(),
        );
    }
    $categories[$row['catid']]['products'][] = array(
            'id' => $row['prodId'],
            'name' => $row['prodName'],
    );
}
foreach($categories as $cat) {
    echo '<li><a href="#">' . $cat['name'] . '</a><ul>';
    foreach($cat['products'] as $prod) {
        echo '<li><a href="&id=' . $prod['id'] . '">' . $prod['name'] . '</a></li>';
    }
    echo '</ul></li>';
}

One step variant

We store the current category, and when the category changes, we close the current list and open a new one :

$sql = "SELECT categories.id as catid, categories.name as catname, products.id as prodId, products.name as prodName 
        FROM `categories` 
        INNER JOIN `products` ON categories.id = products.category 
        ORDER BY categories.id DESC";

$result = execute_select($sql);
$actualcategory = null;
foreach($query as $row) {
    if($actualcategory != $row['catid']) {
        echo '<li><a href="#">' . $row['catname'] . '</a><ul>';
    }
    echo '<li><a href="&id=' . $row['prodId'] . '">' . $row['prodName'] . '</a></li>';
    if($actualcategory != $row['catid']) {
        echo '</ul></li>';
        $actualcategory = $row['catid'];
    }
}

n+1 query

In this solution, we retrieve the list of categories, and then, for each one, retrieves the list of products :

$sql = "SELECT categories.id, categories.name
        FROM `categories` 
        ORDER BY categories.id DESC";
$categories = execute_select($sql);
foreach($categories as $cat) {
    echo '<li><a href="#">' . $cat['name'] . '</a><ul>';
    $sql2 = "SELECT products.id, products.name
            FROM `products` 
            WHERE `products`.category = ".$cat['id'];
    $products = execute_select($sql2);
    foreach($products as $prod) {
        echo '<li><a href="&id=' . $prod['id'] . '">' . $prod['name'] . '</a></li>';
    }
    echo '</ul></li>';
}

Dry coding warning

I dry coded the preceding piece of PHP code, I'm not even sure I didn't made some kind of silly mistakes. It is possible you will have to adapt them to your needs. If something is wrong, please point it out in the comments, I will fix it ASAP :)

Conclusion

The first two possibilities executes only one query and then parse the results to display meaningful information. The code is, in my opinion, fairly hard to understand and error prone.

The last possibility is much more clearer and, I think, easier to modify and extend.

From a performance point of view, we have only one query in the first two versions, but we retrieves much more data and a join in necessary. I think there's no easy answer about which solution is best, it will greatly depends on the number of categories and products for each of them. I think the best to do is to test each of the solution on various data set to determine the quicker one.

If I would have to develop this kind of menu, I will personally use the n+1 query approach. Even if the performance are slightly off (which I'm not sure), the solution is so much clearer that it compensates the weaknesses.

Disclaimer

Kudos to every other poster on this question, I didn't provide a "new" answer, I just put the already provided one in PHP code. Hope this will help !

Upvotes: 1

apokryfos
apokryfos

Reputation: 40673

The SQL Grouping operator roughly works like this: You group by an attribute, and you (under the hood) make groups of rows which have the same value in that attribute. The query result will only contain the top of each those groups. You can use aggregate functions on each of those groups but that's about it.

You have two options: (a) Either perform separate queries for each category (as previously suggested) or (b) Using your first query, order by category and in your loop, while the current row is in the same category as the previous row then add it to your current list. When you reach a result which belongs to another category than the previous one then close the current list (echo </ul>;) and start a new one

Upvotes: 0

Peter
Peter

Reputation: 16923

SELECT 
    *, products.id as prodId, products.name as prodName
FROM 
    products 
LEFT JOIN
    categories ON categories.id = products.category

You want products table so take it FROM products not FROM categories

Upvotes: 0

Ariel
Ariel

Reputation: 26753

You want two queries.

First get a list of categories, and loop through them. Then within each category do a second query and get the products.

(Yes, it's technically possible to get all the data in one query and check if the category was already output, but I recommend against it.)

Upvotes: 0

Related Questions