I’ve been using Kevin Yanks book to build my site a number of years ago and I’ve recently been making some changes. Now my database driven website is getting more advanced (for me!) I need some advice / clarification.
Im confused my the definitions on the the w3schools.com website so not sure which JOIN i should be trying to use.
Basically I have the following tables:
PROPERTIES | PHOTOS | AREAS
At the moment my query returns those properties with 2 photos twice.
SELECT DISTINCT site_properties.id, site_properties.title, site_properties.area, site_photos.id as photo_id, site_photos.property AS photo_prop, area_id, area_name
FROM site_properties, site_photos, site_areas
WHERE site_properties.id = site_photos.property
AND area = area_id
AND status = 1
What Id like is 1 row for each property even if it has two photos. What am I doing wrong?
I’d like to store an ‘array’ of the photo_id’s so they can be placed in the single row of the result. Something like…?
Ok. I have done some reading and added the GROUP BY function. This is great in that it returns only one results per property even when the property has more than one image.
GROUP BY site_properties.id ORDER BY entry_level DESC
However, Im still struggling with the out put for each property in the while statement. How do I access the photo data for each property?
in your query, this line contains two columns which are properly qualified with their table names –
WHERE site_properties.id = site_photos.property
and this line contains two columns which are not properly qualified –
[indent]AND area = area_id [/indent]if the columns are not properly qualified, it makes the query damned difficult to understand
SELECT site_properties.id
, site_properties.title
, site_properties.area
, site_photos.id as photo_id
, site_photos.property AS photo_prop
, site_areas.area_id
, site_areas.area_name
FROM site_properties
INNER
JOIN site_areas
ON site_areas.area_id = site_properties.area
INNER
JOIN site_photos
ON site_photos.property = site_properties.id
WHERE site_properties.status = 1
i would not use GROUP BY the way you did, to limit the photos to one
instead, i would decide some way of choosing which photo to display, and working that into the query – best option is a flag on the photos table to indicate “this is the default photo”
how/where are you storing the actual images? the photos table doesn’t seem to have a column for the path to a web server directory
Great - I now use the column imagenumber to label the photos added. Starting with 0 as the ‘default’ value and number. In the query I have removed my GROUP BY as suggested and instead added the following AND clause.
AND site_photos.imagenumber = 0
This too limits the results to one row per property even when that property has multiple images - great!
Now, how do I still get the site_photos.id’s for the other images that each property might have? I.e show all images each property has.