SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Recording a user's response from a lightbox

    My site incorporates a nice lightbox when certain links are clicked. I am taking it a step further by having a lightbox appear right when my user lands on a certain page. I want to use these to update their account in the database based on their response. Each of the following questions will require a specific SQL query to be executed:

    You have 56 leads. Would you like to upgrade to Gold to contact them?
    You have logged in 45 times. Would you like to signup as a distributor?
    You have never sent a referral to a friend. Would you like to now?

    I'm having a hard time figuring out the best way to modify the database to accommodate these questions and responses. My initial idea was to create a "prompts" table as well as a "promptResponses" table. When the website user logs in and lands on the Welcome back page, I need to see which of the prompts they have not responded to, and then show that one. Has anyone done something similar to this on their website? The more I look into this idea, the more complex its becoming.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What's wrong with your existing idea for the prompts table? It sounds fairly straightforward. Check if row exists, if not, show prompt, and when submitted, insert a row.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This works if there is only one question I want to ask my users, like, "You have logged in 45 times. Would you like to become a distributor". This becomes much more complicated when I have several campaigns that I want to run at the same time. Some campaigns will apply to some users, other campaigns will apply to all of them.

    So what would be the best way, once a user logs in, to see if there are any campaigns that apply to them? And if there is, the special query needed for that campaign. Do you agree that I will need one table called campaigns that will store the needed SQL queries for the campaigns, and then another table called campaignReponses that will record what the user submits?
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hmm, no. The table can have the columns (user_id, prompt, response). It works for as many questions as you want to ask. One row per user-prompt pair with a response.

    So what would be the best way, once a user logs in, to see if there are any campaigns that apply to them?
    Only you know that. We don't know the business rules that define these prompts. Depending on the rules, you might simply code them as some conditionals and database queries in a bit of code you run when the user logs in, if they're complex but generalizable maybe you store them in a database and have some code that can map that database table to how to check if a user matches any of the campaigns.

    If it's simple as "has user logged in 45 times and not answered that prompt before", then at login, you run a database query to check if the user has logged in 45 times and hasn't answered that prompt already.

    Code:
    SELECT COUNT(*) FROM users LEFT OUTER JOIN prompts ON prompts.user_id = users.id AND prompts.prompt = 'logged_in_45' WHERE users.id = X AND users.login_count >= 45 AND prompts.answer IS NULL
    If 1 then prompt, else this rule doesn't apply.

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, you have pointed me in the right direction. Thanks. The first campaign I am going to setup will look to see how many leads a person has in their account. The following query does that for me:

    PHP Code:
    SELECT users.guide
         
    guides.subscription
         
    COUNT(*) as total 
      FROM users 
    INNER
      JOIN users 
    as guides
        ON guides
    .uid users.guide
    where users
    .guide 110
    This query tells me the user is 110, the type of subscription is Free, and the total number of leads this user has is 5. Is it possible to put two joins in a query? I need to somehow add the following that you helped me with:

    PHP Code:
    LEFT OUTER JOIN campaigns ON campaigns.uID users.uID 
    AND campaigns.answer 'Upgrade to gold' WHERE users.uID 110 AND campaigns.answer IS NULL
    Once I have this query working, I should be able to spawn a lightbox with a message that says, "You have 5 leads in your account. Would you like to upgrade to the Gold account so that you can make contact with these leads?"

    Thanks for your help!
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, this query seems to be working so far.....

    PHP Code:
    SELECT users.guideguides.subscriptionCOUNT(*) as total FROM (users INNER JOIN 
    users 
    as guides ON guides.uid users.guideLEFT OUTER JOIN campaigns ON campaigns.uID users.uID 
    AND campaigns.campaign 'Upgrade to gold' AND campaigns.answer IS NULL where users.guide 110
    Thanks!
    Convert your dollars into silver coins. www.convert2silver.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
  •