SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DISTINCT troubles

    Hi all.......
    first I will start with the layout of the database concerningt his question

    PHP Code:
    users(uidnamephoneaddressemailwebsitepremiumwriteupcoupon)

    category(cidvalue)

    lookup(uidcid

    Now when I preform a search, it sometimes gets messed up when there a user belongs to multiple categories, indicated by the lookup table which links the two other tables together

    for example if lookup looked like....

    |uid | cid|
    | 1   || 43   |
    | 1   || 26   |
    | 1   || 11   |

    it would display the results 3 times... I know I need to use distinct, but I cant realy remember how to use it to well....this is hte statement I came up with

    PHP Code:
    $result =mysql_query("SELECT DISTINCT lookup.uid, lookup.cid, users.name, users.uid, users.phone, users.address, users.email, users.website, users.premium, users.writeup, users.coupon, category.* FROM users, lookup, category WHERE  (((category.cid = lookup.cid) AND (lookup.uid = users.uid)) AND ((users.name LIKE '%$keyword%') or (category.value LIKE '%$keyword%'))) ORDER BY premium DESC, name ASC LIMIT $offset$x"
    and its not working.... it still brings up all of hte results

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the problem is that each row in your result set is going to be unique - by definition. This is because, as you know each row in the lookup table is, of course, going unique.

    <<edited>>
    Sorry I had an SQL here that was bad code. I will repost if I can think of the solution
    Last edited by freakysid; Aug 18, 2001 at 05:08.

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DISTINCT will select a distinct tuple NOT a distinct column.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use only user fields in select part and keep distinct. It sounds you dont need category information or u need only one of them?

  5. #5
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "category.value LIKE '%$keyword%'"
    yes, i do need the category in there

  6. #6
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you do not use that in SELECT part
    that is used in WHERE part


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
  •