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.
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.
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?
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.
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
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:
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:
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?”
SELECT users.guide, guides.subscription, COUNT(*) as total FROM (users INNER JOIN
users as guides ON guides.uid = users.guide) LEFT OUTER JOIN campaigns ON campaigns.uID = users.uID
AND campaigns.campaign = 'Upgrade to gold' AND campaigns.answer IS NULL where users.guide = 110;