banner advertising mysql-query with multiple tables

Question

Hello. My problem is described further under the table structure.

This is my tables for my advertising banner system:

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

1. b.usertype be '0' or some value you pass in the $usertype variable
2. 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 ?