reducing pm queries?

[TECK]

i replaced this code segment:
$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:
$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]

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

[g-force2k2]

TECK just use this 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]

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

[g-force2k2]

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]

:) i forgot... i was testing it on a demo template.. it's working great either ways the code :)
thanks alot for the tip.

[Neo]

I use something like this :)

[NTLDR]

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

[Neo]

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

[TECK]

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

[DrkFusion]

Thanks, this worked really well on my graphics board.

Thanks
Arunan