...

View Full Version : Lisit Results By categorys



ajloun
02-26-2010, 06:18 AM
Hello

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

Cate1
------
1-Name1
2-Name 2
3-Name3

Cate2
-----
1-Name1
2-Name 2
3-Name3

Cate3
----
1-Name1
2-Name 2
3-Name3

Any idea?

SKDevelopment
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
INNER
JOIN categories AS c
ON c.id=n.category_id
ORDER
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)

ajloun
02-26-2010, 08:05 AM
Thx,
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?

SKDevelopment
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

ajloun
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 ..

SKDevelopment
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