Help with simple code (categories and subcategories) need correct

PHP
1 
$cats = mysql_query("SELECT * FROM categories");
while($showCats = mysql_fetch_array($cats)) {
$category = $showCats['catid'];
$subq = "SELECT * FROM categories WHERE catid = '$category' ";
$sub = mysql_query($subq);
if(!mysql_num_rows($sub)) die("Error. Your query was $subq <br> and the error was ".mysql_error());
echo "<tr><td class=\\"tablea\\" style=\\"border-style: none; border-width: medium\\" height=\\"10\\">&nbsp;<b>".$showCats['catname']."</b></td><td class=\\"tablea\\" style=\\"border-style: none; border-width: medium\\" height=\\"10\\"></td></tr>";
while($showSub = mysql_fetch_array($sub)) {
echo "<tr><td class=\\"tablea\\" style=\\"border-style: none; border-width: medium\\" height=\\"10\\">&nbsp;<a style=\\"font-size: 11px; font-family: tahoma, sans-serif; text-decoration: none; background: none\\" href=\\"index.php?cat=".$showSub['name']."\\"><font size=\\"2\\">".$showSub['name']."</font></a></td><td class=\\"tablea\\" style=\\"border-style: none; border-width: medium\\" height=\\"10\\"><b>".$cat['count_movies']."</b></td></tr>";
}}

Its was echo double information (if 2 subs, print double categories) like

Category1
- sub1
- sub2
Category1
- sub1
- sub2
Category2
- sub1
- sub2
- sub3
Category2
- sub1
- sub2
- sub3
Category2
- sub1
- sub2
- sub3
2

What does the categories table contain (structure and data) ?

3 
CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `catid` int(10) NOT NULL,
  `catname` varchar(50) NOT NULL,
  `name` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=30 ;

INSERT INTO `categories` VALUES (1, 1, 'sub1', 'somthing here1');
INSERT INTO `categories` VALUES (2, 1, 'sub1', 'somthing here2');
INSERT INTO `categories` VALUES (3, 2, 'sub2', somthing here1');
INSERT INTO `categories` VALUES (4, 2, 'sub2', 'somthing here2');
INSERT INTO `categories` VALUES (5, 3, 'sub3', 'somthing here1');
INSERT INTO `categories` VALUES (6, 3, 'sub3', 'somthing here2');
INSERT INTO `categories` VALUES (7, 3, 'sub3', 'somthing here3');
INSERT INTO `categories` VALUES (8, 4, 'sub4', 'somthing here1');
INSERT INTO `categories` VALUES (9, 5, 'sub5', 'somthing here1');
INSERT INTO `categories` VALUES (10, 6, 'sub5', 'somthing here2');
4

Ack.

Okay, #1: You need an ORDER BY on that category query. (Probably a double-order by… IE: ORDER BY catid,name ). That way we’re sure we’re getting the categories in the right order.
#1b: You probably dont need to SELECT *, either… only pull the fields you’re interested in using.

#2… you have no parent-child relationship defined for this data (or more to the point, you’ve defined everything as a child), so how do you know which ones are the ‘main’ categories, and which ones are the sub-categories?

5

The problem is because you have a loop inside a loop. You have two queries that are selecting * from categories, one of which has no condition. I’m not sure why you’d need both

6

I just created your test data and this appears to give me the result you’re looking for:

SELECT concat("Category", catid) AS category
     , concat("- ", catname) AS subcategory
  FROM categories;
7

On a related note, I highly recommend the Nested Set model for storing hierarchical data for categories and the like: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/