Hello guys. I'm pulling my hair out over this one, and some help will def be appreciated. Here are the 2 tables:
table 1: prizes
idprize_namemax_winnerswin_type
1$15daily
2$22daily
3$51weekly
table 2: logs
idprize_idwin_time
111280494274
211280494300
331280493899
How can I make it so that I can join the 2 tables, using prize.id=log.prizeid, then choose a prize that has not reached maximum winners for the period (win_type).
Query which I have been working on which isn't working properly:
select prize.id,prize.prize_name,ifnull(county,0),tp.prize_id,prize.max_winners,tp.win_time,prize.win_type from prizes prize
left join (
select prize_id,win_time,count(*) as county from logs
) tp
on prize.id=tp.prize_id where prize.max_winners>'0' and ((prize.win_type='once' and prize.max_winners>county) or (prize.win_type='hourly' and tp.win_time>'$_last_hr')) order by rand() limit 1
There is a few different "win_types"...
-once (which only gets awarded once)
-hourly
-daily
-weekly
I can get it to select a prize, but as soon as I add the "county" it stops working. I've tried so many methods to get this work but now it's time to throw in the towel and get some help please.
Thanks in advance. If you require more info please let me know.