NYCPHP Meetup

[Paul Reinheimer]

Hmm,

As a PHP solution, you could run with solution 1 or 2, and wrap it
with a class that makes everything nice and pretty for whatever ends
up deailing with the data.

However, more along the lines of a different solution, i have seen two
posts that may be of use

Building a new table with foreign keys
http://forums.devshed.com/archive/t-152685

Using more specific queries
http://lists.mysql.com/mysql/930


hope that helps
paul

On Sun, 4 Jul 2004 20:26:00 -0700, Alan T. Miller
<amiller at criticalmedia.biz> wrote:
> 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 figured someone
> here might be able to suggest something.
> 
> 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. 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
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>