Server Time Out ?

[fla5h]

Hello,
I have written a small stats script for displaying members details.

The problem is that it doesn't show all the members, it seems to cut off after about the first 50. Is there anything that would make the script stop / timeout ?

Looking at the stats its taking about 3.5 seconds to display the page and 8 queries.

Any clues ?

[Dpcows]

Your stuck in a loop

[Xenon]

if it's jsut a littel script, can you please post it, maybe there's just a littl bug in it

[fla5h]

sure thanks for the help, I cannot see a loop to be stuck in, but I know nothing compared to you :D


<?php

require('./global.php');

$r1=$DB_site->query("SELECT user.userid, user.username, user.joindate, user.posts, user.lastactivity,

post.dateline,

post.threadid,
usergroup.title, thread.title as ptitle
FROM user,post,usergroup,thread
WHERE user.lastpost=post.dateline AND user.usergroupid=usergroup.usergroupid AND

post.threadid=thread.threadid
ORDER BY usergroup.title,user.username");



while($bob=mysql_fetch_array($r1))
{


$q2=$DB_site->query_first("select count(*) as postuserid from thread where

postuserid=$bob[userid]");
$threads=$q2[postuserid];
$ld=$bob[dateline];
$ld2=date("d/m/y (G:i)",$ld);
$lv=$bob[lastactivity];
$lv2=date("d/m/y (G:i)",$lv);
$jd=$bob[joindate];
$jd2=date("d/m/y (G:i)",$jd);

eval("\$memberdeets .= \"".gettemplate("memberdeets")."\";");



}

eval("dooutput(\"".gettemplate("memberdeetspage")."\");");

?>


This will show the following stats

Username
Usertitle ( not custom title )
Posts
Threads
Last Post ( both Date / Time and thread id )
Last visit Date / Time
Joined Date / Time

I am going to add a few more, but before I do I cannot understand why it stops at 50 odd members.

Would it help if I adopted the VB style where the number of results a page is used ?

Ie only 30 per page ?

Thanks for any help given :D

[fla5h]

mmm, looking at this code, its erm how can I say crap. just looked at the stats for it.

Mmmm, can anyone give me a lesson in optimising ?

How would I remove the queries inside the loop so they are not called everytime ?

Could this be why its stopping short of all the members because I'm loading the server too much ?

[fla5h]

mmm am I a little out of my depth here ??

The script above gives stats of


Page generated in 15.43927801 seconds (1.93% PHP - 98.07% MySQL) with 78 queries.


However if I alter the script slightly,


<?php

require('./global.php');


$q1="SELECT user.userid, user.username, user.joindate, user.posts, user.lastactivity, post.dateline, post.threadid,
usergroup.title, thread.title as ptitle
FROM user,post,usergroup,thread
WHERE user.lastpost=post.dateline AND user.usergroupid=usergroup.usergroupid AND post.threadid=thread.threadid
ORDER BY usergroup.title,user.username";
$r1=mysql_query($q1);


while($bob=mysql_fetch_array($r1))
{

$q2="select count(*) from thread where postuserid=$bob[userid]";
$r2=mysql_query($q2);
$ld=$bob[dateline];
$ld2=date("d/m/y (G:i)",$ld);
$lv=$bob[lastactivity];
$lv2=date("d/m/y (G:i)",$lv);
$jd=$bob[joindate];
$jd2=date("d/m/y (G:i)",$jd);


while($bill=mysql_fetch_array($r2)) {
eval("\$memberdeets .= \"".gettemplate("memberdeets")."\";");

}}
eval("dooutput(\"".gettemplate("memberdeetspage")."\");");



?>


The stats show as

Page generated in 3.41518295 seconds (97.98% PHP - 2.02% MySQL) with 8 queries.

I still only get the first 50 odd member results, but I guess the second query is better ? Less queries more php ?

Am I correct here ?

[Xenon]

indeed that could be the problem, but we'll find a way :)




<?php

require('./global.php');

$r1=$DB_site->query("SELECT user.userid, user.username, user.joindate, user.posts, user.lastactivity,

post.dateline,

post.threadid,
usergroup.title, thread.title as ptitle
FROM user,post,usergroup,thread
WHERE user.lastpost=post.dateline AND user.usergroupid=usergroup.usergroupid AND

post.threadid=thread.threadid
ORDER BY usergroup.title,user.username");

$threadstarters=$DB_site->query("SELECT COUNT(*) AS count, postuserid FROM thread GROUP BY postuserid");
while($threadstarter=$DB_site->fetch_array($threadstarters)) {
$threadcount[$threadstarter['postuserid']] = $threadstarter['count'];
}

while($bob=$DB_site->fetch_array($r1))
{


$threads=$threadcount[$bob['userid']];
$ld=$bob[dateline];
$ld2=date("d/m/y (G:i)",$ld);
$lv=$bob[lastactivity];
$lv2=date("d/m/y (G:i)",$lv);
$jd=$bob[joindate];
$jd2=date("d/m/y (G:i)",$jd);

eval("\$memberdeets .= \"".gettemplate("memberdeets")."\";");



}

eval("dooutput(\"".gettemplate("memberdeetspage")."\");");

?>



a restriction to 30 users per page isn't needed, but wouldn't hurt ;)

[Xenon]

for your last post before mine:

ohm, the microstats doesn't work if you use mysql_query.

the truth is the second is as bad as the first one, just the querycount is wrong because you didn't use $DB_site->query ;)

[fla5h]

are I see thats why the qury count is so low.

thank you for your script, its giving stats of

Page generated in 1.84838998 seconds (5.93% PHP - 94.07% MySQL) with 10 queries.

which is an improvement on mine, and now I get 69 members displayed, still not my total ammount of members ?

Sorry to be a pain.

Should I scrap this and try to modify the code found in user.php in the admin folder ?

[Xenon]

well what doy wou want to get exactly?

this part FROM user,post,usergroup,thread
WHERE user.lastpost=post.dateline AND user.usergroupid=usergroup.usergroupid AND

post.threadid=thread.threadid

is very suspicious and will take a long time, poor DB ;)

if you tell exactly what ya want i can maybe optimize that code also :)

aslo as a tip for future:
lear to use significant variable names.
i don't know what $bob should mean, but $usersinthread would be clear.

[fla5h]

thanks for the tips xenon.

I want to produce a stats page,

I basically need the following variables.

The username
The userid
The usertitle ( not custom title, default usergroup title )
Number of post the user has posted
Number of threads the user has created
The Intro thread id ( PPN's hack, basically do a lookup in the intro forum for a thread with a title of the users username )
The users last post date / time
The users last post id
The users last visit date / time
The users join date / time
The users referral users id
The users referral username

Thanks for your help once again, I am maybe asking a lot here, I know you are busy with your other duties so if you do not have time to do this, if you can give me some pointers as to where to look / start that would be great.

Thanks

[Xenon]

ahh i see :)

then use instead of this code: $r1=$DB_site->query("SELECT user.userid, user.username, user.joindate, user.posts, user.lastactivity,

post.dateline,

post.threadid,
usergroup.title, thread.title as ptitle
FROM user,post,usergroup,thread
WHERE user.lastpost=post.dateline AND user.usergroupid=usergroup.usergroupid AND

post.threadid=thread.threadid
ORDER BY usergroup.title,user.username");

this:
$r1=$DB_site->query("SELECT user.userid, user.username, user.joindate, user.posts, user.lastvisit,
post.postid, post.threadid, thread.title as ptitle,
usergroup.title, thread.title as ptitle
FROM user
LEFT JOIN usergroup USING (usergroupid)
LEFT JOIN post ON (post.userid=user.userid AND dateline>=user.lastpost-30)
LEFT JOIN thread ON (thread.threadid=post.threadid)
ORDER BY usergroup.title,user.username");

the referrals would mean extra queries again... (same procedure as threads counter :))