Sql Question

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.

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

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.

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

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] = " ";
}

}
}

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

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.

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

That would explain it!