database queries in a loop.

[fr8]

Ive managed to finish all of my current projects and have now moved onto creating a forum for my website. Im not looking to make something truly advanced like ipb or phpbb. Im just looking to build a text one. im also not looking for a "use hotscipts.com" answer.

What im wondering about is having my queries inside my while loops.

example. This is the begining of my loops.

while ($topics = mysql_fetch_array($topicquery)) {
 echo '
        <TR>
         <TD bgcolor="0C2734">
                <Strong>'.$topics["topic_name"].':</Strong><BR>
                '.$topics["topic_subject"].'
         </TD>
         <TD bgcolor="0C2734" align="center">';
$topicstarter = mysql_query ('SELECT username FROM users
 WHERE userid="'.$topics["topic_starter"].'"');
$thestarter = mysql_fetch_array($topicstarter);
echo ''.$thestarter["username"].'';

Now once i get to the end of all my loops i have used 8 queries. Now the question I have is since I have them inside of loops does that mean that if i pull 20 topics them im doing 20x8 queries to the database? If so what can i do to reduce this back to 8 if possible?

[sarahk]

I suspect that you can pull the information out using fewer queries if you join some tables.

Maybe give some examples of the queries and typical results.

[axo]

yeah, I also think so... some join queries would get rid of the inner while loop.

to be able to help, i would need to know the name of the tables and columns, though ... 🙂

[fr8]

I commented everything well. If something is not understood please ask and I will tell.

$topicquery = mysql_query ('SELECT * FROM js_forum_topics
 WHERE forumid="'.$sub.'"');
while ($topics = mysql_fetch_array($topicquery)) {
//Displays all of the topics and a subjects for the topic
//Database structure for js_forum_topics 
//topicid (INT),  forumid (INT),  topic_starter (INT),  topic_name, 
//topic_subject,  topic_date,  topic_content,  topic_views (INT), 
//lastpost_user (INT),  lastpost_time
<a href="thread.php?top='.$topics["topicid"].'">
  '.$topics["topic_name"].':
</a><BR>
  '.$topics["topic_subject"].'
/**********************
 Get Poster Username. Using $topics["topic_starter"] from above
 while loop
*************/
$topicstarter = mysql_query ('SELECT username FROM js_users
 WHERE userid="'.$topics["topic_starter"].'"');
$thestarter = mysql_fetch_array($topicstarter);
echo ''.$thestarter["username"].'';
/**********************
 Get Reply count for topic. using $topics["topicid"] from while loop.
 while loop
*************/
// Database structure for js_forum_replys
//replyid (INT),  forumid (INT),  topicid (INT),  reply_subject,
//reply_comment,  reply_date,  repy_user (INT). 
$replycount = mysql_query ('SELECT * FROM js_forum_replys 
WHERE topicid="'.$topics["topicid"].'"');
$totalreplys = mysql_num_rows($replycount);
echo ''.$totalreplys.'';
}
/**********************
 I currenty dont have a query for Last Reply for topic. But will
 have one in the future.
*************/

This shows only 3 but if i can solve these then the rest will be easy changes.

[sarahk]

have a look at this, bear in mind it will need html formatting and is untested.

<?php
$sql = "SELECT topicid, topic_name, topic_subject, username, count(*) as cnter "
	. " FROM js_forum_topics, js_users, js_forum_replys "
	. "WHERE forumid='{$sub}' "
	. "and js_users.userid = js_forum_topics.topic_starter "
	. "group by topicid";
$topicquery = mysql_query ($sql); 
while ($topics = mysql_fetch_array($topicquery)) 
{ 
  echo "<a href='thread.php?top={$topics['topicid']}'>{$topics['topic_name']}:</a>
  <BR> {$topics['topic_subject']} 
  <BR> {$topics['username']} 
  <br> {$topics['cnter']}";
} 
/********************** 
I currenty dont have a query for Last Reply for topic. But will 
have one in the future. 
* you could add max(js_forum_replys.reply_date) as lastreply
*************/
?>

[fr8]

I cant seem to get that one to work even with some changes. tells me not a valid mysql result.

MySQL said:
Column: 'topicid' in field list is ambiguous

[Weedpacket]

Originally posted by fr8
I cant seem to get that one to work. tells me not a valid mysql result.

Do you have a field called "topicid"?

[fr8]

in all of them except js_users because there is no need.

[sarahk]

whoops, and I missed a join too

$sql = "SELECT js_forum_topics.topicid, topic_name, topic_subject, username, count(*) as cnter " 
    . " FROM js_forum_topics, js_users, js_forum_replys " 
    . "WHERE forumid='{$sub}' " 
    . "and js_users.userid = js_forum_topics.topic_starter " 
    . "and js_forum_replys.topicid = js_forum_topics.topicid "
    . "group by topicid"; 

[fr8]

It moved. 🙂
MySQL said:
Column: 'forumid' in where clause is ambiguous

i can take the forumid out of the WHERE and it works but that picks everything from the other forum.

[sarahk]

ok, didn't notice that forumid was in 2 tables

$sql = "SELECT js_forum_topics.topicid, topic_name, topic_subject, username, count(*) as cnter "  

    . " FROM js_forum_topics, js_users, js_forum_replys "  

    . "WHERE js_forum_topics.forumid='{$sub}' "  

    . "and js_users.userid = js_forum_topics.topic_starter "  

    . "and js_forum_replys.topicid = js_forum_topics.topicid " 
    . "group by topicid";

[fr8]

wow. now thats an sql query. thank you very much for that.

Would you mind breaking that sql query down for me and explain to me what is happening? Ive never seen what setup like. reason why i couldnt really troubleshoot it.

[fr8]

Having a problem with the SQL statment. It is relying on that there is a reply for the topic. So if a new topic is made and there isnt a reply for it then nothing is shown. but if i add a reply then the topic gets displayed.

$sql = "SELECT js_forum_topics.topicid, topic_name,
 topic_subject,lastpost_time, topic_views,username,
 count(*) as cnter "
    . " FROM js_forum_topics, js_users, js_forum_replys "
    . "WHERE js_forum_topics.forumid='{$sub}' "
    . "and js_users.userid = js_forum_topics.topic_starter "
    . "and js_forum_replys.topicid = js_forum_topics.topicid "
    . "group by topicid";


// . "and js_forum_replys.topicid = js_forum_topics.topicid "

// If I get rid of this line then the topics display but it counts all of the
// rows in the table instead of just the ones it owns.

[iceraider]

This is where JOIN comes in handy

[fr8]

Originally posted by iceraider
This is where JOIN comes in handy

How do you mean?

Now as to my other problem. I did a work around. Instead of having the comment data in the topic i made it as part of the replys.

And to make the replie counts not get effected i just did a '-1' from the varible.

Is this a good way of doing things?

[sarahk]

Possibly.

There are 2 types of SQL. I'm old fashioned and use old SQL but in the new syntax you can do inner, outer, left and right joins. I never try and write them, but use a modelling tool. In this case I use the tool in Visual Fox Pro.

What the left join lets you do is select even when there isn't a match.

Now, your work around seems fine, and it does the job, so run with it.

[fr8]

Well my work a round will not work. if multiple people are posting ids get messed up. since i dont know the topicid until after the post is made i cant set it for the js_forum_replys. i can count the rows before or after but if someone makes a post in between those checks then the reply id will not be correct for the replys. hopefully i explained this well.

Now i need to find a way that it will display the new topics if there isnt a reply. Any ideas?

Originally posted by fr8
Having a problem with the SQL statment. It is relying on that there is a reply for the topic. So if a new topic is made and there isnt a reply for it then nothing is shown. but if i add a reply then the topic gets displayed.

$sql = "SELECT js_forum_topics.topicid, topic_name,
 topic_subject,lastpost_time, topic_views,username,
 count(*) as cnter "
    . " FROM js_forum_topics, js_users, js_forum_replys "
    . "WHERE js_forum_topics.forumid='{$sub}' "
    . "and js_users.userid = js_forum_topics.topic_starter "
    . "and js_forum_replys.topicid = js_forum_topics.topicid "
    . "group by topicid";


// . "and js_forum_replys.topicid = js_forum_topics.topicid "

// If I get rid of this line then the topics display but it counts all of the
// rows in the table instead of just the ones it owns.

[/B]

[fr8]

Would i be better off if i add collum for table replys?

[sarahk]

1. Change column names to match yours, my tool only allows 10 chars in a name so I mucked them about a bit.
2. if it doesn't run then use the mysql manual to check the syntax but I would expect there NOT to be problems.

good luck

SELECT Js_forum_topics.topicid, Js_forum_topics.topic_name,;
  Js_forum_topics.topic_view, Js_forum_topics.topic_subj,;
  Js_users.username, count(*);
 FROM  js_forum_topics INNER JOIN js_users;
    LEFT OUTER JOIN js_forum_replys ;
   ON  Js_forum_topics.topicid = Js_forum_replys.topicid ;
   ON  Js_forum_topics.starter = Js_users.userid;
 GROUP BY Js_forum_topics.topicid

[fr8]

no luck with that.
MySQL said:
You have an error in your SQL syntax near 'LEFT OUTER JOIN js_forum_replys ON js_forum_topics.topicid = js_forum_replys.t' at line 4

Went to the mysql documentation to read up on the Join and that just threw me for a loop. i was hoping that the user comments would help explain it more. Could someone explain the Join on how it is being used here? Anyone understand the error?