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:

Code:

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.

PHP Code:


			
          $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:

Code:

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):

Code:

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