Which type of JOIN to use?

Hi,

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…?

while ($property = mysql_fetch_array($properties)) {

$p_photo1 = $property[0];
$p_photo2 = $property[1];

Hope that makes sense and someone can point me in the right direction!

could you please do a SHOW CREATE TABLE for each of your tables?

it’s kind of hard to guess which columns in your query belong to which table, because most of the columns are not properly qualified

Thanks for answering but Im not sure what you mean - not properly qualified? How could I improve things?

CREATE TABLE `site_areas` (
 `area_id` int(11) NOT NULL auto_increment,
 `area_name` varchar(200) NOT NULL default '',
 PRIMARY KEY  (`area_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
CREATE TABLE `site_photos` (
 `id` int(11) NOT NULL auto_increment,
 `property` int(11) NOT NULL default '0',
 `title` varchar(200) NOT NULL default '',
 `description` text NOT NULL,
 `tn` int(1) NOT NULL default '0',
 `image` int(1) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=153 DEFAULT CHARSET=latin1
CREATE TABLE `site_properties` (
 `id` int(11) NOT NULL auto_increment,
 `entry_level` varchar(255) NOT NULL default '',
 `type` int(11) NOT NULL default '0',
 `area` int(11) NOT NULL default '0',
 `title` text NOT NULL,
 `location` varchar(30) NOT NULL default '',
 `ref` int(50) NOT NULL default '0',
 `phone` varchar(50) NOT NULL default '',
 `description` longtext NOT NULL,
 `email` varchar(200) NOT NULL default '',
 `beds` int(11) NOT NULL default '0',
 `smoking` int(1) NOT NULL default '0',
 `pets` int(1) NOT NULL default '0',
 `link` varchar(200) NOT NULL default '',
 `status` int(1) NOT NULL default '0',
 `Created` date NOT NULL,
 `featured` int(10) NOT NULL default '0',
 `hits` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1002 DEFAULT CHARSET=latin1

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

That seems to make sense! How would I do that - add another column to the photos table?

No your right - there is no path stored. The images are renamed with the site_photos.id and copied to an uploads folder.

yes, add a column to the photos table

as for the image id being used as the file name, that works, but i prefer to give my images actual names that make sense

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.

I.E Each result has the default photo plus its other photos if it has any?

Should I do a another query? That seems a little inefficient.

see query in post #5

I think I missing something! I’ve tried using your query in post 5 but I can’t seems to get it to work - Sorry.

Im looking for this sort of result.

Property One
Name, Description, Picture[1]

Property Two
Name, Description, Picture[1]

Property Three
Name, Description, Picture[1], Picture[2], Picture[3]

Property Four
Name, Description, Picture[1]

SELECT site_properties.title
     , site_areas.area_name
     , [COLOR="Blue"]GROUP_CONCAT(site_photos.property) AS photos[/COLOR]
  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
[COLOR="blue"]GROUP
    BY site_properties.id[/COLOR]