Not outputting a certain field

[webmasternovis]

Hi all,

Does anyone know if there is something similar to "GROUP BY", which, instead of not outputting rows, just doesn't output certain fields?

Thanks a lot,
Mike

[devinemke]

what are you trying to do exactly? be specific.

[webmasternovis]

I've got categories, and sub categories. So what I've done is list all the sub categories in a table, with another field holding the main category of the sub one.

So its like:

Main
maincatname

Sub
subcatname

..and a few more fields.

What I'm trying to do is list the sub categories and main categories in a drop down box set out like this...

MAIN CAT
sub cat
sub cat2
sub cat3
MAIN CAT 2
sub cat
sub cat1

etc...

I've got this at the moment:

$query = mysql_query("SELECT * FROM categories GROUP BY main");

for ($x=0; $x < mysql_num_rows($query); $x++)
		{
	$row = mysql_fetch_assoc($query);
	echo "<option>" . $row['main'] . "</option><option>  " . $row['sub'] . "</option>";
		}

But all this does is list the main category, and just ONE sub category beneath it.

Anyone know what the problem could be?

Cheers,
Mike

[devinemke]

i suggest a data structure like this:

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) default NULL,
  `parent_cat_id` int(11) default NULL,
  PRIMARY KEY  (`cat_id`)
) TYPE=MyISAM;

each category has a parent_cat_id (which can be NULL) for the category right above it in the heirarchy. this allows for infinite nesting of categories, sub-categories, sub-sub-categories, etc...

here's some dummy data and an example using the data structure above:

INSERT INTO `categories` VALUES (1, 'animals', NULL);
INSERT INTO `categories` VALUES (2, 'music', NULL);
INSERT INTO `categories` VALUES (3, 'plants', NULL);
INSERT INTO `categories` VALUES (4, 'dogs', 1);
INSERT INTO `categories` VALUES (5, 'cats', 1);
INSERT INTO `categories` VALUES (6, 'classical', 2);
INSERT INTO `categories` VALUES (7, 'jazz', 2);
INSERT INTO `categories` VALUES (8, 'trees', 3);
INSERT INTO `categories` VALUES (9, 'shrubs', 3);

$result = mysql_query('SELECT * FROM categories');
while ($row = mysql_fetch_assoc($result))
{
	$cat_names[$row['cat_id']] = $row['name'];

if (!$row['parent_cat_id']) {$categories[$row['cat_id']] = array();}
else {$categories[$row['parent_cat_id']][] = $row['cat_id'];}
}

foreach ($categories as $key => $value)
{
	echo '<b>' . $cat_names[$key] . '</b><br>';
	foreach ($value as $sub_value)
	{
		echo $cat_names[$sub_value] . '<br>';
	}
	echo '<br>';
}

[webmasternovis]

Heh, thanks for your quick response. I'm not doing what you said exactly, but yours concepts have helped a lot. Thanks 🙂