banner

id (unique key)

title

bannersrc

maxhits

maxklik

activefrom

activeuntil

dato

bannerhits (number of shows)

id (unique key)

ip

bannerid (index) (referring to banner.id)

dato

bannerklik (number of clicks)

id (unique key)

ip

bannerid (index) (referring to banner.id)

dato

question

What I want is to make an query that selects a banner from the table banner WHERE:

(b.usertype = $usertype OR b.usertype = 0 )

AND

( activefrom <= now activeuntil >= now

OR

maxklik > number of rows in bannerklik where bannerklik.bannerid = banner.id

OR

maxhits > number of rows in bannerhits where bannerhits.bannerid = banner.id )

Can you create a query for me, because I really don t get how to make that :)

Answer 1

i think the following should work .. (assuming you have a usertype field in the banner table)

SELECT
     b.id, b.title, b.bannersrc
FROM
     banner b 
     LEFT OUTER JOIN bannerhits bh ON b.id = bh.bannerid
     LEFT OUTER JOIN bannerklik bk ON b.id = bk.bannerid 
WHERE
     (b.usertype =  0  OR b.usertype = $usertype)
     AND 
     (b.activefrom <= curdate() AND b.activeuntil >= curdate())
GROUP BY 
     b.id, b.title, b.bannersrc, b.maxklik, b.maxhits
HAVING
     ( 
      b.maxklik > count( DISTINCT(bk.id) )
      OR
      b.maxhits > count( DISTINCT(bh.id) )
     )

[EDIT 1]

ok lets revise the conditions here..

You want the following conditions to be true at the same time in order to display an ad

b.usertype be 0 or some value you pass in the $usertype variable
any one of the following rules
1. b.activefrom <= curdate() AND b.activeuntil >= curdate()
2. b.maxklik > count( DISTINCT(bk.id)
3. b.maxhits > count( DISTINCT(bh.id)

am i correct ?

banner

bannerhits (number of shows)

bannerklik (number of clicks)

question

友情链接