Query for pulling a banner based on a flag

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Query for pulling a banner based on a flag

Post by s.dot »

I'm trying to write a query where a member can rotate banner advertisements only if a flag called allow_banners is = 1 in the users table.

To select the banner my query is simple:

Code: Select all

SELECT * FROM `banners` ORDER BY RAND() LIMIT 1
But to add in the user flag to the query it gets complicated (seems like it should be easy, maybe I've been working too much).. something like

Code: Select all

SELECT * FROM `banners` WHERE `user_id` IN(SELECT `id` FROM `users` WHERE `allow_banners` = 1) ORDER BY RAND() LIMIT 1
This feels really ugly. It might work but I haven't tried it due to how ugly it feels. Is there a more elegant solution or is that right?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query for pulling a banner based on a flag

Post by mikosiko »

... one of this probably will be my choice

Code: Select all

 
SELECT a.* FROM banners a, users b
 WHERE a.user_id = b.id
     AND b.allow_banners = 1
 ORDER BY RAND() LIMIT 1
 

Code: Select all

 
SELECT a.* FROM banners a JOIN  users b ON ( a.user_id = b.id)
 WHERE  b.allow_banners = 1
 ORDER BY RAND() LIMIT 1
 
Post Reply