SQL : get rows from two tables relatively 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...
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 !!!