Peter J. Boettcher — Wed, 26 Jun 2002 17:27:48 +0000

That would explain it!

korndragon — Wed, 26 Jun 2002 16:10:16 +0000

because i have 36 forums on my site.. lol
i will attempt to combine some of the queries later today.

Peter J. Boettcher — Wed, 26 Jun 2002 12:49:51 +0000

The backend database design is the most important part. The design and efficiency of your backend will determine how efficient your queries can be. For example, it looks like your first 2 queries could have been combined into one if your database design would accomodate it.

I'm still wondering why you're getting 90 queries though, even if you're going in a loop the query should only execute once.

zollet — Wed, 26 Jun 2002 05:26:41 +0000

It's kind of hard to figure out what code is in what page and how you can optimize it. However, I suggest you lear table joins (http://www.devshed.com/Server_Side/MySQL/Join/page1.html) as they are extremely helpful.

By the way, your forum looks very nice. Good job

korndragon — Wed, 26 Jun 2002 05:12:07 +0000

ok, heres the code that gets all the information from the DB..

############# Get Forums' Group's Info #######################
$sql=$Db1->query("SELECT id, title, group_id FROM forum_groups WHERE type='1' ORDER BY forder");
$total_groups=$Db1->num_rows();
$x=0;
while($Db1->next_record()) {
$groups[$x][0] = $Db1->Record['id'];
$groups[$x][1] = stripslashes($Db1->Record['title']);
$groups[$x][2] = $Db1->Record['group_id'];
$x++;
}
$Db1->free_result($sql);

############### Get Forum's Info ########################
for($x=0; $x<$total_groups; $x++) {
$sql=$Db1->query("SELECT id, title, descr, group_id FROM forum_groups WHERE type='2' AND group_id='".$groups[$x][2]."' ORDER BY forder");
$total_forums[$x]=$Db1->num_rows();
$y=0;
while($Db1->next_record()) {
$forums[$x][$y][0] = $Db1->Record['id'];
$forums[$x][$y][1] = stripslashes($Db1->Record['title']);
$forums[$x][$y][2] = $Db1->Record['descr'];
$forums[$x][$y][3] = $Db1->Record['group_id'];
if($forums[$x][$y][2] != "") {$forums[$x][$y][2]="".$forums[$x][$y][2]."";}
$y++;
}
$Db1->free_result($sql);
}

################## Get Forum's Total Threads #################
for($x=0; $x<$total_groups; $x++) {
for($y=0; $y<$total_forums[$x]; $y++) {
$sql=$Db1->query("SELECT id FROM forum_posts WHERE forum_id='".$forums[$x][$y][0]."' and reply='0'");
$forums[$x][$y][4]=$Db1->num_rows();
$Db1->free_result($sql);
}
}

################## Get Forum's Total Threads #################
for($x=0; $x<$total_groups; $x++) {
for($y=0; $y<$total_forums[$x]; $y++) {
$sql=$Db1->query("SELECT id FROM forum_posts WHERE forum_id='".$forums[$x][$y][0]."'");
$forums[$x][$y][5]=$Db1->num_rows();
$Db1->free_result($sql);
}
}

################## Get Forum's Last Activity #################
for($x=0; $x<$total_groups; $x++) {
for($y=0; $y<$total_forums[$x]; $y++) {
$sql=$Db1->query("SELECT id, thread_id, title, dsub, subm, reply FROM forum_posts WHERE forum_id='".$forums[$x][$y][0]."' ORDER BY dsub DESC LIMIT 1");
$la[5]=$Db1->num_rows();
if($la[5] != 0) {
$Db1->next_record();
$la[0]=$Db1->Record['id'];
$la[1]=$Db1->Record['thread_id'];
$la[2]=stripslashes($Db1->Record['title']);
$la[3]=date('F d', mktime(0,0,$Db1->Record['dsub'],1,1,1970));
$la[8]="".date('h:i a', mktime(0,0,$Db1->Record['dsub'],1,1,1970))."";
$la[4]=$Db1->Record['subm'];
$la[7]=$Db1->Record['reply'];
$Db1->free_result($sql);

if(strlen($la[2]) > $forumtitlewidth) {
$la[2]=substr($la[2], 0, $forumtitlewidth)."...";
}

$sql=$Db1->query("SELECT username FROM user WHERE userid='".$la[4]."'");
$Db1->next_record();
$la[6]=$Db1->Record['username'];
$Db1->free_result($sql);

if($la[7] == 1) {
$sql=$Db1->query("SELECT title FROM forum_posts WHERE thread_id='".$la[1]."'");
$Db1->next_record();
$la[2]=stripslashes($Db1->Record['title']);
$Db1->free_result($sql);
}
$forums[$x][$y][7] = "".$la[2]." @ ".$la[3]." ".$la[8]."By ".$la[6]."";
}

if($la[5] == 0) {
$forums[$x][$y][7] = " ";
}

}
}

korndragon — Wed, 26 Jun 2002 05:10:35 +0000

i am bettering my skills right now, and learning how to select from more than 1 table at once. i guess i can post the code. its quite a bit though

zollet — Wed, 26 Jun 2002 05:04:55 +0000

krondragon, I'm guessing you have some kind of loop and that's why you get 90 queries for only one page? Maybe we can help you out if you post the lines you are using to retrive information from the database.

korndragon — Wed, 26 Jun 2002 01:26:06 +0000

yeah, just take a look at http://www.newbie-developer.com/?view=forums/index.php
its mainly just select statements.

mairving — Wed, 26 Jun 2002 01:16:42 +0000

If you are talking about 90 queries to get one bit of info, then it is pretty excessive. I am also assuming that by a query that you mean a SELECT statement. You might provide a little more info to us on what you mean by queries and what kind of info you are trying to get and your table relationships.