forumhome: PM Queries optimization!

[N9ne]

I've seen people post about combining the "unread PMs, total PMs, new PMs" queries, into just one from three! How can this be achieved?! I would love to know because I need to combine them

[Xenon]

this are the codelines instead the original 3 queries:

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']; 


[Erwin]

I've always wondered why the vB team has never made this a standard thing in the latest versions of vB.

[Xenon]

good question...

i think they just wanted to fix all bugs, but leave their head free for working on vb3
if they had also optimizations for vb2 in the head they'll get confused *g*

[N9ne]

What are the variables to put in forumhome_pmloggedin template with that code? I have Total PMs working, but not unread and new PMs since your last visit...

[Erwin]

You can't decrease queries in templates.

You need to edit the PHP files.

Replace the PM query lines in index.php with the code that Xenon put up.

[Xenon]

you don't have to change anything in your templates, it is just a code replace

[N9ne]

No, I understand that...however it seems the variable to place in forumhome_pmloggedin has changed, as no number shows up. I think it may be:

$allpm['newpm']

And:

$allpm['unreadpm']

now?

[Xenon]

this is the original part:

PHP 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"); 


as you can see if you have replaced it with exact the part i post above no variablenames have changed

[N9ne]

Ok I figured out what was confusing me! I had no unread messages, and no new PMs so it didn't show 0, it showed nothing at all...is there a way to make it say 0 if you don't have any, instead of not showing anything at all?