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:
$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:
$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?

[Xenon]

you have a real wierd board have ya?
it shows also 0 at my boards...

[NTLDR]

This is the code I have, with the origional variables in the templates and it works fine on 2.2.7 and 2.2.8 and displays 0 for me:

$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=$allpm['newpm'];
$unreadpm=$allpm['unreadpm'];

[N9ne]

I have this:

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

Which looks the same! However it still doesn't show 0 for unread and new PMs! Why is it doing this? Is there anything in index.php that could cause this? [i have modified it a tiny bit, but not the PM bits :S]

[Xenon]

hmm, depends on what you've changed ;)

try
$newpm['messages']=intval($allpm['newpm']);
$unreadpm['messages']=intval($allpm['unreadpm']);
instead of$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];

[N9ne]

It works! Thankyou for putting up with me and my problems, keep up the good work :)

[Xenon]

:)
you're welcome

i'm glad finally it works ;)

btw. i think it's an php internal issue, can you tell me which php version you run?

[NTLDR]

Originally posted by Xenon

$newpm['messages']=intval($allpm['newpm']);
$unreadpm['messages']=intval($allpm['unreadpm']);

Xenon would changeing the code I have to what you have posted be a more secure way of doing it? I read a post somewhere from one of the dev's saying you should use intval for security reasons. Also there were lots of changes that use intval in 2.2.8.

[Xenon]

well this part isn't a security risk part, so you can leave your code or change it, no problem...

intval is more secure, because strings are always converted into an int value. that'll take effect on inputboxes whenever a user should enter an intval but enters a string this could produce security problems as i can think of.

hmm, is this understandable?

perhaps an example:


$posts="5, password=MD5('hello')";
$DB-site->query("UPDATE user SET posts=$posts");

as you can see this would run this query:
UPDATE user SET posts=5,password=MD5('hello')

if you use intval this cannot happen:
$posts="5, password=MD5('hello')";
$DB-site->query("UPDATE user SET posts=".intval($posts));

this would just run this query (as normally wanted):
UPDATE user SET posts=5

[NTLDR]

Thanks for the explaination Xenon :) I understand why and where intval should be used now ;)

[Erwin]

Yes, I think that's why they did it - to avoid users entering in non-integer values and running the risk of corrupting the db or causing db errors.

[eoc_Jason]

I know with oracle you can run a cost on a query (or queries), I was wondering if MySQL has the same. I'm wondering which is really faster, the 3 separate, or the single complex...

[Xenon]

the single complex should also be faster, because it takes the infos just once out of the db and the just uses if, sum and count functions.

the 3 seperate would take the infos three times...