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 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 [DATE]1303629727[/DATE] at [TIME]1303629727[/TIME] ---------------

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 [DATE]1303776460[/DATE] at [TIME]1303776460[/TIME] ---------------

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