I know what I want to do has been done a thousand times before, however I am
looking for suggestions on the best way to go about it. I have two tables,
one holds products, the other holds photos. There is a one to many
relationship between products and photographs.
For example...
TABLE 1 (products):
products.id
products.title
products.description
TABLE 2 (product photos)
photos.id
photos.id_product
photos.filename
photos.height
photos.width
photos.position
the 'position' field is used to determine which is the main photo (photos
are ordered by their position). IN other words the photo with position 1
would be the main thumbnail.
There are two things I would like to be able to get from a SELECT statement.
The first is a list of products with the thumbnail info for the product. I
immediately see two ways of doing this but am looking for a better solution.
SOLUTION 1: select all products, and then loop through that result set and
do another select to get the photo information on each iteration of the
loop. This would be slow, and create unnecesary overhead on the database. I
do not think this is a good solution.
SOLUTION 2: select all products and then use subselects to get the
photograph information. I do not like this solution either, as there seems
to be no clear way to get multiple fields such as filename and height and
width etc without many subselects, or perhaps a creative concatenation
routine.
SOLUTION 3: I am open to suggestions... please!
The other main task I would like to accomplish is to be able to select all
the product information for a single product and get all the photographs for
the product as well. However, aside from using an array to return all photos
in that array in the select for products, I cannot think of an efficient way
to do this without issuing two queries (one to get the product, and one to
get the photo).
I am very interested to hear how others have tacked simular situations like
this. Any help is greatly appreciated.
Alan
