how to use sql query in sql query

Question

I have two tables:

1. table category(cat_id,category)
2. table category_details(cat_id,id,...)

I have sql code like bellow:

$sql=mysql_query("select * from category");
while($rows=mysql_fetch_array($sql)){
$id_count=$rows['cat_id'];      

$sql1=mysql_query("select * from category_details where cat_id='$id_count'");
$count=mysql_num_rows($sql1);

}

Can I use like this?

Answer 1

Use JOIN.

Example

SELECT * FROM category LEFT JOIN 
   category_details ON category_details.cat_id = category.cat_id;

above query will return all the category and associated category detail.

OP Comment Response

SELECT    c.name,
          IFNULL(sub_c.total, 0) num
FROM      category c
LEFT JOIN ( SELECT   COUNT(*) total, cat_id
            FROM     category_details
            GROUP BY cat_id
          ) sub_c ON (sub_c.cat_id = c.cat_id);

Complete Code

<?php
    $query = "SELECT    c.name,
                IFNULL(sub_c.total, 0) num
            FROM      products_category c
                LEFT JOIN ( SELECT   COUNT(*) total, cat_id
                    FROM     product
                    GROUP BY cat_id
                ) sub_c ON (sub_c.cat_id = c.id)";

    $result = mysql_query($query)or die(mysql_error());

    echo "<table><tr><td>NameCount</td></tr>";
    while($row = mysql_fetch_assoc($result))
    {
        echo "<tr><td>".$row['name']."(".$row['num'].")"."</td></tr>";
    }
    echo "</table>";
?>

MySQL Table

ProductCategory Table

CREATE TABLE IF NOT EXISTS `products_category` (
  `id` int(11) NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `products_category`
--

INSERT INTO `products_category` (`id`, `name`) VALUES
(1, 'Erasmus'),
(2, 'Preston'),
(3, 'Ulric'),
(4, 'Gray'),
(5, 'Joseph'),
(6, 'Merrill'),
(7, 'Alan'),
(8, 'Jeremy'),
(9, 'Solomon'),
(10, 'Andrew'),
(11, 'Galvin'),
(12, 'Craig'),
(13, 'Cameron'),
(14, 'Omar'),
(15, 'Addison');

Product Table

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `cat_id`) VALUES
(1, 'Wesley', 1),
(2, 'Graiden', 2),
(3, 'Cruz', 5),
(4, 'Hayden', 5),
(5, 'Kennedy', 6),
(6, 'Uriah', 8),
(7, 'Alan', 8),
(8, 'Cade', 1),
(9, 'Ryan', 5),
(10, 'Brody', 7);

Above will output

Erasmus(2)
Preston(1)
Ulric(0)
Gray(0)
Joseph(3)
Merrill(1)
Alan(1)
Jeremy(2)
Solomon(0)
Andrew(0)
Galvin(0)
Craig(0)
Cameron(0)
Omar(0)
Addison(0)