SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Joins and epmty rows. And stuff.

    Hi everyone! I have a bit of a problem. I have two tables in a mysql database that looks something like this:


    sponsor
    ---sponsorid
    ---title
    ---interest

    user_interest
    ---userid
    ---sponsorid
    ---interest

    The userid is stored in a PHP variable called $bbuserinfo[userid] (yes, that is vBulletin)

    I want get all the sponsors in the sponsor table as an array, and add the "user_interest.interest" to the "sponsor.interest". Something like this:

    SELECT s.sponsorid, s.interest+i.interest AS interest
    FROM sponsor s, user_interest i
    WHERE s.sponsorid = i.sponsorid
    AND i.userid = '$bbuserinfo[userid]'

    The problem here is, naturally, that sponsor-rows with no associated user_interest row won't appear in the fetched array - which I want them to do! I tried to do it something like this:

    SELECT s.sponsorid, s.interest+i.interest AS interest
    FROM sponsor s, user_interest i
    WHERE s.sponsorid = i.sponsorid
    AND i.userid = '$bbuserinfo[userid]'
    OR i.userid = NULL

    .. but it didn't work. Dang. I'm totally dumbfounded here - cound anyone please help?
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just grab them seperately and add them together once the query's results have been returned....it's a tiny PHP operation that won't effect load time at all, really.

  3. #3
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, I thought about doing that, but I just couldn't figure out how to do it! What method should I use for this? PHP:s array functions?
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you want the user_interest row, even if they don't have a sponser row, is that right? just use LEFT JOIN:

    Code:
    SELECT s.sponsorid, i.interest+IFNULL(s.interest, 0) AS interest 
    FROM user_interest i
    LEFT JOIN sponsor s ON i.sponsorid=s.sponsorid
    WHERE i.userid='$bbuserinfo[userid]'
    Last edited by DR_LaRRY_PEpPeR; Nov 5, 2001 at 23:08.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rereading your post, i don't think i have it right. you say you want "all the sponsors in the sponsors table" but yet you're also looking for a specific userid in the user_interest table?

  6. #6
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Nay, sorry - that doesn't work - it still just picks the ones that have corresponding rows.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  7. #7
    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)
    Originally posted by DR_LaRRY_PEpPeR
    rereading your post, i don't think i have it right. you say you want "all the sponsors in the sponsors table" but yet you're also looking for a specific userid in the user_interest table?
    LEFT JOIN the user_interest table instead.

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what EXACTLY are you wanting to do? i don't quite understand. if you don't mind explaining again.

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    LEFT JOIN the user_interest table instead.
    ha ha, that's what i had in my query first, but then i saw he's looking for a specific userid in the user_interest table. so if he's looking for a certain userid, there's not much point in LEFT JOINing the user_interest table is there? that's why i want to know what he's trying to do.

  10. #10
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah, yes - I belive I have to explain the nature of the application a little. It's a system for picking sponsors/ads out of a database based on the users preferences.
    The "interest" in the sponsor table is the global interest that all visitors collectively think of the sponsor. (If visitors have clicked the "not interested" button on this sponsor, it will have a low rating, and if they have checked it out a lot, it will have a high rating)

    The user preference table simply links the sponsor to a user and what he individually thinks about it. However, sometimes, the user has not made any interaction with the sponsor at all, and thus, the user_interest will not have a row for that sponsor - but I still need to get that sponsor in the array, just without the user_interest added.

    I'm sorry if I'm unclear here, I'm bad at explaining things at 04:00 AM
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  11. #11
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ... essentially, if there is no corresponding row for the userid, I need the user preference to be 0, just like you were trying to do in your query.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com


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
  •