1

I've two tables named categories and products. Simple structure is given below.

categories

cat_id        cat_name
    1           c1
    2           c2
    3           c3

products

prod_id     cat_id      prod_name
    123         1           p1
    546         2           p2
    562         3           p3
    567         3           p4
    234         1           p5

I want to get product list along with their related category name. ex.

Category : c1, Product : p1
Category : c2, Product : p2
Category : c3, Product : p3
Category : c3, Product : p4
Category : c1, Product : p5

For that i'm using below simple queries,

if($res1 = mysqli_query("SELECT cat_id, cat_name FROM categories")) {
    while($tbl1 = mysqli_fetch_assoc($res1)) {

    if($res2 = mysqli_query("SELECT prod_name  FROM products WHERE cat_id = ".$tbl1['cat_id'])) {
        while($tbl2 = mysqli_fetch_assoc($res2)) {
            echo "Category :". $tbl1['cat_name'] . " Product". $tbl2['prod_name'];
        }
    }

}
}

It can be Improved in a single query but having no idea about it. Please help.

Also suggest list those products according to Category order. (ex. first c1 categories products, c2 categories products and so on... )

Thank you...

1 Answer 1

2

Here is the query to order it the way the question has it

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

Here is that query formatted

SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY prod_name;

If you want to it order by category, then

SELECT c.cat_name,p.prod_name
FROM products p INNER JOIN category c
ON p.cat_id = c.cat_id
ORDER BY cat_name,prod_name;

Give it a Try !!!

4
  • Does the query improved ?? in terms of performance.
    – Bhavesh G
    Oct 9, 2014 at 17:12
  • Your code would order by cat_id. My proposed query would order it by prod_name as displayed in your question. Oct 9, 2014 at 17:12
  • Many many thanks. i've learned a lot from these queries. another question .. what if i don't want to use the USING clause (eg. the common column names would not same)
    – Bhavesh G
    Oct 9, 2014 at 17:15
  • 2
    It has to be better in performance. I issue only one query. Your method in PHP is essentially issuing 6 queries (1 against products and 5 against category) Oct 9, 2014 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.