CODEWITHSUNDEEP
phpmysqlsqldatabase
Waqas Saeed
Waqas Saeed

Reputation: 66

Get random record from database table with multiple checks in mysql?

I have database table named 'ads'.i want to show these ads on the the websites randomly.My table structure is

user_id                 int(11)              
type                    varchar(50)             
title                   varchar(100)         
body                    varchar(100)           
display_url             varchar(100)           
target_url              varchar(100)       
dimension               varchar(50)    
image                   varchar(200)    
preferred_countries     text                  
preferred_languages     text                    
preferred_sites         text                  
excluded_sites          text                
preferred_keywords      text                
excluded_keywords       text                  
preferred_devices       varchar(100)      
schedule                mediumtext            
clicks                  int(20)              
clicked                 int(11)                
cpc                     decimal(20,20)

As you can see there are columns like preferred_countries, preferred_languages, preferred_devices etc. I want to show these ads to a visitor by applying multiple checks like if there is prefered country for any ad then it will be shown to the visitor of that specific country and so on. I have fetched all the data related to user ie. visitor's country, visitor's language etc

But i am not getting any idea how to fetch the records like this.

Upvotes: 0

Views: 84

Answers (3)

rebroken
rebroken

Reputation: 633

You could consider MySQL's RAND() function combined with an ORDER BY FIELD()

SELECT * FROM `ads` 
ORDER BY FIELD(preferred_countries, '$mycountry') DESC, RAND()
LIMIT 1

This won't filter out rows that don't match $mycountry, but if they exist, you'll get them first. You should also get a random result if there is more than one match.

Of course this won't work as well if your preferred_countries field might contain multiple country names.

Upvotes: 1

Boopathi Rajan
Boopathi Rajan

Reputation: 1210

try this

select * from ads
where preferred_countries = $mycountry and preferred_languages=$language
order by rand()
limit 1

Upvotes: 0

juergen d
juergen d

Reputation: 204766

select * from ads
where preferred_countries = $mycountry
order by rand()
limit 1

Upvotes: 2

Related Questions