vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Need help with a query (https://vborg.vbsupport.ru/showthread.php?t=262543)

souperman 04-24-2011 06:19 AM
Need help with a query
 
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.
Code:
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:

Code:
Cannot access empty property in /home/username/public_html/includes/cron/request.php on line 12
Line 12 is this line:
Code:
$auser = $db->query_read("

kh99 04-24-2011 08:14 AM
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 04-24-2011 08:49 AM
Thanks. Both your recommendations worked :)

souperman 04-25-2011 07:40 PM
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.


All times are GMT. The time now is 09:31 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01051 seconds
  • Memory Usage 1,731KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (3)bbcode_code_printable
  • (1)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (4)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete