[fw-general] Re: [fw-db] How to print a single column ordered/grouped list from db table data?

[Enkhbilguun Erdenetsogt]

Oh, it seems I showed wrong wrong example. Actually, I have only a table 
stores photo articles, which are included in some categories (cat_id; i 
didn't use relationship but cat_id can be referred as foreign key). And 
the categories are divided into several pieces regarding what year a 
category is belonging.
So my current table structures are follows:

_Table Photos:
_id | cat_id | name | src | description | added_date
_Table Photo Categories_
id | year_id | type | name

_Table Photo Years_
id | year


By the way, I didn't mention the categories have its type. There are 
several types of photo such as ceremonial photos and regular photos. I 
have 2 different controllers, which show only photos with the same 
visual appearance. So I decided to store the photo information in a 
single table instead of creating 2 different named the same tables. In 
order to do that I needed to assign category type.

So my controller index page needs to show the same as the following:

2009
    - Category name 1 (50 photos)
    - Category name 2 (14 photos)
    - .......................
2008
    - Category name 5 (0 photos)

Each category that has photos will be a link, which shows all the photos 
thumbnails in it, and each photo will a link which shows bigger photo 
with description.

I'm not a professional programmer. Even I don't really know printing 
many kind of multidimensional arrays or many kind of objects and even 
php function related to this kind of things. Last a few days, I tried to 
solve this by myself, but still couldn't find any solution. Perhaps, I 
need to modify my table structure.

Currently, when I fetchall and echo Zend_Db returned object with foreach 
and if statements, I getting the following two different views:

2009
    - Category name 1 (150 photos)
2009
    - Category name 2 (14 photos)
2008
    - Category name 5 (0 photos)

OR

2009 2009
    - Category name 1 (150 photos)
    - Category name 2 (14 photos)
2008
    - Category name 5 (0 photos)


Really thank you Ralph and Daniel Latter for the reply.

Oh, Daniel Latter, I already tried GROUP BY clause to my SQL query. 
Unfortunately I couldn't echo the returned object. With my current 
echoing method (foreach ($this->object as $item)), only single years 
with single category were listed. :-D


---
Sincerely,

Enkhbilguun Erdenetsogt


On 10.01.03 4:54, Ralph Schindler wrote:
How do you distinguish sub-article in your database?

-ralph

Enkhbilguun Erdenetsogt wrote:
Happy New Year,

I would like to print articles list ordered/grouped by year column of 
my db table, but I can't find how I should loop to print something 
like the following:

    * 2007 (column year)
          o Article 1 (column cat_id)
                + Sub Article 1 (column name with description)
                + Sub Article 2
                + Sub Article 3
          o Article 2
          o Article 3
          o Article 4


To print the above list with the below table data, I need to make all 
the same year value to one and every articles (column cat_id) should 
be included in it, but because of my poor knowledge of php and OOP, I 
couldn't find simple way to loop them.

My table structure looks:

id | type | cat_id | year | name | description ......



Thank you for your help my friends.


---
Enkhbilguun Erdenetsogt

<<attachment: enkhbilguun.vcf>>