View Full Version : Lisit Results By categorys

02-26-2010, 06:18 AM

I have Made Small Module , there is Small Problem i'm stuck with ,

I have 2 Tables 1- for Categorys Name 2- for Names ..

Now i need to list names in Categorys .. for example i have 3 Categorys .

Cat1 , Cat2, Cat3,

Each Has 3 Names .. Now i need to list this Names under the Category they Belong too .. like

2-Name 2

2-Name 2

2-Name 2

Any idea?

02-26-2010, 07:56 AM
I do not post the final solution. But I hope it would give you an idea...

The table `names` should contain the field `category_id` - id of the category to which it belongs.

You simply names like this:

SELECT n.name
, c.category_name
, c.id
FROM names AS n
JOIN categories AS c
ON c.id=n.category_id
BY c.id
, n.name

Here I assumed that category_id is always set to some category id and could not be null.

Then you pass the resultset in a loop and show the names for each category.

You could even do most of the work right in the SQL by using GROUP_CONCAT() ... (provided the DB is MySQL I mean)

02-26-2010, 08:05 AM
and i had this idea before but i did not know wht after that .. this is SQL Select.

$result = mysql_query( "SELECT c.c_id,c.c_name,COUNT(p_id)
FROM ".$DB->prefix("category")." as c
LEFT JOIN ".$DB->prefix("name")." as p ON p.category = c.c_id
GROUP BY c.c_id" );

Then wht? Loop?

02-26-2010, 08:15 AM
This query would not list names under categories. It selects the categories and counts number of names in each category.

Also strictly speaking if GROUP BY with aggregate function is used, you need to group by all non-aggregate fields. I think MySQL would allow the query you have shown, but still I would recommend to change the GROUP BY to:

GROUP BY c.c_id,c.c_name

02-26-2010, 08:22 AM
Yah Exactly , It dose not list the Names but it Lists the Category names

Cat1 Cat2 ..

I could not Figuer how to Get the Title Names from the Names Table with the Command i used above ..

02-26-2010, 08:54 AM
Try this (not checked):

SELECT c.c_id
, c.c_name
, (SELECT GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR '\n') FROM name AS p WHERE p.category = c.c_id GROUP BY p.category) as names
FROM category AS c

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum