NYCPHP Meetup

[Gabriel PREDA]

> 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
> 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.

You should JOIN tables using their relation that is
"products.id=photos.id_product"
So you would have:
    SELECT products.id, products.title, products.description,
                    photos.id AS id_photos, photos.filename,
                    photos.filename, photos.height, photos.width
    FROM products
    LEFT JOIN photos ON(products.id=photos.id_product)
    WHERE photos.position='1'

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

    This can only be acomplished with 2 queryes... Because of the result...
    Let's say that "x" are results from products and "y" are from photos...
you would have something like:
xxxx yyyy
        yyyy
        yyyy

    This result set is imposible to be represented by an SQL server... the
closest match you can get is
xxxx     yyyy
NULLs yyyy
NULLs yyyy
    But this would make things worse because you would have to skip many
NULLs...

Have a good time programming !
> Alan
Gabriel