SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist cronsrcs's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    500
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying other adverts from same category

    I have a classifieds section in a site i am developing. It allows classified adverts to be viewed by section and then each advert to be viewed in detail.

    I have a classified_adverts table which has an advert_id and also class_sect (the section it belongs to).

    The class_section table has an id and the name of the section.

    On the view_advert.php I display all of the details about the advert, i also want to display links to other adverts in the same section.

    When viewing the view_advert.php page, the querystring has the id of the advert and also the classified section id - ie view_advert.php?advert_id=4&sec_id=1

    So i have a recordset that selects the details about the advert;

    SELECT advert_id, class_sect, ad_title, ad_body, ad_price, ad_contactEmail, ad_contactPhone, ad_contactName, item_photo, DATE_FORMAT(date_created,'%D %M %Y') as date_posted
    FROM ClassifiedAdvert
    WHERE advert_id = advert

    advert gets its value from $_GET['advert_id']

    I then have a recordset that selects records where the classified section is the same as the id in the querystring and the advert_id is NOT = to the advert id:

    SELECT advert_id, class_sect, item_type, ad_title
    FROM ClassifiedAdvert
    WHERE class_sect = section AND NOT advert_id = advert
    ORDER BY item_type ASC

    But the second record set it not returning any adverts, although there are other adverts in the same section. I have run my query on the command line in my terminal session and it returns an empty set.....

    Is this the best way to go about what I am trying to achieve and I have just made a stupid mistake with my SQL OR is there an easler/better way of achieving this?

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    one thing i noticed is that if each advert belongs to a particular section, you don't actually need to pass the section id in via the url

    once you've retrieved the advert using the url advert_id (let's call it A), you automatically get the class_sect (let's call it S) that this advert belongs to

    use this S value (instead of the url value) when retrieving the other adverts in the same section

    SELECT advert_id, item_type, ad_title
    FROM ClassifiedAdvert
    WHERE class_sect = S
    AND advert_id <> A
    ORDER BY item_type ASC
    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
  •