Need help with a query

[souperman]

I have a few query issues:

The query below returns zero rows, but if I remove the last three lines it works fine. Specifically is the last AND on the query.

SELECT user.userid, user.usergroupid, user.posts, access.forumid, userfield.field5 FROM user
LEFT JOIN userfield
ON userfield.userid = user.userid
LEFT JOIN access
ON access.userid = user.userid
AND access.forumid IS NULL
WHERE (user.usergroupid = 70 OR user.usergroupid = 71 OR user.usergroupid = 87)
AND (user.posts >= 150)
AND (user.joindate <= NOW()-1209600)
AND (user.lastactivity > NOW()-2419200)
ORDER BY user.lastactivity DESC
LIMIT 25


The code is suppose to grant access to specific forum sections based on gender.
Here's the the full code:

<?php
error_reporting(E_ALL & ~E_NOTICE & ~8192);
if (!is_object($vbulletin->db))
{
exit;
}

$auser = $db->query_read("
SELECT user.userid, user.usergroupid, user.posts, access.forumid, userfield.field5 FROM user
LEFT JOIN userfield
ON userfield.userid = user.userid
LEFT JOIN access
ON access.userid = user.userid
AND access.forumid IS NULL
WHERE (user.usergroupid = 70 OR user.usergroupid = 71 OR user.usergroupid = 87)
AND (user.posts >= 150)
AND (user.joindate <= NOW()-1209600)
AND (user.lastactivity > NOW()-2419200)
ORDER BY user.lastactivity DESC
LIMIT 25
");

$raccess = 1;

while ($suser = $db->fetch_array($auser))
{
$rgender == $suser['field5'];
if ($rgender = "Male") {
$rforumid = 159;
$vbulletin->db->query_write("
INSERT INTO access
(userid, forumid,accessmask) VALUES(".$suser['userid'].", ".$rforumid.", ".$raccess.")
");
}
elseif($rgender == "Female") {
$rforumid = 160;
$vbulletin->db->query_write("
INSERT INTO access
(userid, forumid,accessmask) VALUES(".$suser['userid'].", ".$rforumid.", ".$raccess.")
");
}
}

?>

--------------- Added 1303629727 at 1303629727 ---------------

Here's the error I get:

Cannot access empty property in /home/username/public_html/includes/cron/request.php on line 12

Line 12 is this line:
$auser = $db->query_read("

[kh99]

It looks like NOW() returns a time in a different format than is used by joindate and lastactivity, I think you'd want to use UNIX_TIMESTAMP() instead.

As for the other error, maybe $db isn't defined - use $vbulletin->db instead.

[souperman]

Thanks. Both your recommendations worked :)

[souperman]

Ok, now I have another problem. I hope you can help me out, or anyone reading this. The code above will insert a row into the access table to give a user access to a particular forum. The code works, when I check the user's permissions in the admincp they are correct.

The problem is that the user still sees the forum as "Private" even though the permissions are set correctly. When I grant access to the user via the admincp it works perfectly. So my question: Is there anything else I have to do to make sure the user is granted access properly? it appears that there's more steps than to just add them to the access table.

--------------- Added 1303776460 at 1303776460 ---------------

I got it to work. It had something to do with the datastore and caching the user's permission.