showthread_query hook sql

[Nullifi3d]

$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).

[JTyson]

$vbulletin->userinfo['lastpost']

should give you that info

[Nullifi3d]

$vbulletin->userinfo['lastpost']

should give you that info
Not enough info... Seems to be an array? This doesn't seem to answer my question very well.

[Andreas]

if ($vbulletin->userinfo['lastpost'] > (TIMENOW-86400))
{
// User has posted in the last 24 hours
}

[Nullifi3d]

if ($vbulletin->userinfo['lastpost'] > (TIMENOW-86400))
{
// User has posted in the last 24 hours
}

Wouldn't this be how to check if the user that is viewing the page has been online in the last 24 hours? That's not what I need if it is.

[Paul M]

Wouldn't this be how to check if the user that is viewing the page has been online in the last 24 hours? That's not what I need if it is.I would imagine that 'lastpost' is exactly what it says, 'lastactivity' is what you are referring to.

[Nullifi3d]

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.

Sorry if I'm wrong and just having a brain fart.

[Nullifi3d]

Does anyone have any further comments or suggestions? I need this addition to my mod please.

[Andreas]

SELECT userid FROM user WHERE lastpost > UNIX_TIMESTAMP(NOW())-86400 ORDER BY RAND() LIMIT 1

If that is what you want

[Nullifi3d]

If that is what you want
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:
SELECT userid FROM " . TABLE_PREFIX . "user WHERE lastpost > UNIX_TIMESTAMP(NOW())-86400 ORDER BY RAND() LIMIT 1
SELECT userid, title, ext, width, height FROM " . TABLE_PREFIX . "banners WHERE status = 'active' AND available >= 1 ORDER BY RAND() LIMIT 1

[Andreas]

SELECT user.userid, banners.title, banners.ext, banners.width, banners.height
FROM " . TABLE_PREFIX . "user AS user
INNER JOIN " . TABLE_PREFIX . "bannners AS banners USING (userid)
WHERE user.lastpost > UNIX_TIMESTAMP(NOW())-86400 AND banners.status = 'active' AND banners.available >= 1
ORDER BY RAND()
LIMIT 1


If this is what you want.

[Nullifi3d]

Nevermind. It was a simple fix, just had to replace the decimals with commas. I didn't look at the code close enough the first time.

[Andreas]

I don't know why I had "." instead ",", but should be fixed now.

[Nullifi3d]

Yea I saw that right after I posted. Thanks for the code it works. You're the greatest!