The Arcive of vBulletin Modifications Site.

reducing pm queries? · **Released**: 09-08-2002

i replaced this code segment:

Code:

  $allpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");
  $newpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] AND dateline>$bbuserinfo[lastvisit] AND folderid=0 $ignoreusers");
  $unreadpm=$DB_site->query_first("SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=$bbuserinfo[userid] AND messageread=0 AND folderid=0 $ignoreusers");

with:

Code:

  $allpm=$DB_site->query_first("
    SELECT COUNT(*) AS messages,
    SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
    SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
    FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers
  ");

  $newpm['messages'] = $allpm['newpm'];
  $unreadpm['messages'] = $allpm['unreadpm'];

actually it works. use it guys it will seve you 2 queries.

Velocd · #2 09-09-2002, 04:57 PM

Bump ^
Mainly because I'm interested in knowing as well

g-force2k2 · #3 09-09-2002, 06:22 PM

TECK just use this code:

PHP Code:


			  $allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
                                SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
                                SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
                                FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

  $newpm[messages]=$allpm['newpm'];
  $unreadpm[messages]=$allpm['unreadpm'];

you don't need the single slashes... because once you define an array variable outside of an array the singel slashes aren't necessary...

regards...

g-force2k2

TECK · #4 09-09-2002, 08:00 PM

it doesnt do any diff if you remove the slashes or not. i tried that before i posted. is something else wrong...

g-force2k2 · #5 09-09-2002, 08:14 PM

i tested it just now and it worked just fine... did you make sure the template variables were $newpm[messages] and $unreadpm[messages] ?

cause i know on the forum home pm template the variables are just $newpm and $unreadpm? regards...

g-force2k2

TECK · #6 09-09-2002, 09:20 PM

i forgot... i was testing it on a demo template.. it's working great either ways the code

thanks alot for the tip.

Neo · #7 09-11-2002, 04:01 PM

I use something like this

NTLDR · #8 09-11-2002, 06:56 PM

Thanks for the code TECH/g-force2k2 added to my board and works a treat, another 2 queries removed

Neo · #9 09-11-2002, 07:54 PM

Now all we need is a VB with only 10 queries per thread instead of the average 25.

TECK · #10 09-16-2002, 11:46 PM

my average no of queries is 14 on forumhome for a max of 18, elsewhere.

no vital functions of vb are removed... just played with the queries...