SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    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..?

    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for answering but Im not sure what you mean - not properly qualified? How could I improve things?

    Code:
    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
    Code:
    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
    Code:
    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

  4. #4
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by realnoob View Post
    not properly qualified?
    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 --
    AND area = area_id
    if the columns are not properly qualified, it makes the query damned difficult to understand
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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

  9. #9
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by realnoob View Post

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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by realnoob View Post
    I.E Each result has the default photo plus its other photos if it has any?
    see query in post #5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    May 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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]

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by realnoob View Post
    Im looking for this sort of result.
    Code:
    SELECT site_properties.title
         , site_areas.area_name
         , GROUP_CONCAT(site_photos.property) AS photos
      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
    GROUP
        BY site_properties.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •