$banner = $db->query_first("SELECT userid, title, ext, width, height FROM " . TABLE_PREFIX . "banners WHERE status = 'active' AND available >= 1 ORDER BY RAND() LIMIT 1");
if(ctype_digit($banner['userid'])) {
//show it
} else {
//show default
}
The above code works fine and prints out code when the user's status is active and their available impressions is at least 1. I want to modify it to also check if the user has posted within the past 24 hours. I don't know how to though as I am using my own table in the vbulletin database to store the banner information. As you see above the query that fetches the data I need is querying from the table banners. From my knowledge I would need to fetch the last post time from the lastpostdate column in the user table. But how would I add this to my query so all three of these are factored in when it gets the one random array ($banner[]) from the banners table?
As well I would like to setup the query to also not pick the row in the banners table that matches the vistor's userid (so users do not see their own banners being displayed).
Yea, but if you take a look at the code in my first post you'll see that I don't need to find out if the current visitor of the page has posted in the last 24 hours. Rather I need to find out if the random user that is generated has posted in the last 24 hours.
Basically my code is a random banner rotator built into vbulletin. I want to change my code which uses sql (in my first post) to randomly pick out a userid from the database so that it randomly picks one out that has posted within the last 24 hours.
Yes that is part of what I want. This query would select 1 random user that has posted within the last 24 hours from the mysql table, but the only problem is that this needs to coincide with my sql from the first post which picks 1 random user from a different table in the database.
Is there a way to do this? Maybe having a query store all members that have posted within the last 24 hours in an array or something. Then another query pick 1 by random (according to calls from the query in my first post) from the array. Basically I need to combine these two sql queries together:
[sql]SELECT userid FROM " . TABLE_PREFIX . "user WHERE lastpost > UNIX_TIMESTAMP(NOW())-86400 ORDER BY RAND() LIMIT 1[/sql]
[sql]SELECT userid, title, ext, width, height FROM " . TABLE_PREFIX . "banners WHERE status = 'active' AND available >= 1 ORDER BY RAND() LIMIT 1[/sql]
10-21-2005, 04:16 PM
showthread_query hook sql
Linear Mode
