reducing pm queries?

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:

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]

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

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