Fill an array from a database to display records. Please help

[geroid]

Hi

This is kinda further to a post I had yesterday. I have a problem I could use some help with. I have a database (imagesumprog) with the following fields

 

image_id

image_name

category

prog_date

 

The important field here is the prog_date which holds nothing more than a year (2009, 2010, 2011 etc.). The user can search the database by year i.e. prog_date to retrieve images from the database for that year.

The problem is that these images are stored in one of 3 categories

 

1. Mini Campa

2. Campa Samhraidh

3. Coláiste Samhraidh

 

There may or may not be images in the Mini Campa,  Campa Samhraidh or Coláiste Samhraidh categories.

 

So if I was to write the sql statement in english to say search the database for images for the year 2009, it would be like this:

 

select all images records for the year 2009

display all images first from the Mini Campa category (if any exist)

then display all images from the Campa Samhraidh category (if any exist)

then display all images from the Coláiste Samhraidh (if any exist)

 

So I suppose what I need to do is first check what categories exist for the year 2009 so then I can carry on to display images from the categories that exist.

 

Any ideas how to do this??

 

 

 

[kickstart]

Hi

 

Not sure I see the problem.

 

Do you want a heading saying (say) "No Images For 2009 For Mini Campa"?

 

If you just want the images then:-

 

SELECT image_id, image_name, category, prog_date

FROM imagesumprog

WHERE prog_date = '2009'

 

If you want a null image when there aren't any for a particular year / category:-

 

SELECT a.image_id, a.image_name, b.category, a.prog_date

FROM (SELECT DISTINCT category FROM imagesumprog) a

LEFT OUTER JOIN (SELECT image_id, image_name, category, prog_date FROM imagesumprog WHERE prog_date = '2009') b

ON a.category = b.category

ORDER BY category

 

Note that you probably should have a seperate table of categories.

 

All the best

 

Keith

[geroid]

Thanks form that Keith. I'll have a close look at your code. looks interesting

 

ger