forums

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

forums

Post by s.dot »

I've made my own forums, and they're working pretty good. However, now that there's around 50,000+ posts I've noticed they're getting a lot slower.

So I was thinking it's in the mysql_num_rows function

I use that to determine how many posts each user has (to determine rank), to determine how many replies a thread has, to determine how many threads are in a forum, and to determine how many pages a particular thread has.

With all this counting of rows.. is that slowing my forums down?

Would it be more effective to add a field containing how many posts a user has, simply increasing, or decreasing it every time they post or delete? Would this speed up my forums?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

It is possible that it may speed up the forums, although phpbb dynamically counts post counts on the fly, as in it is not stored anywhere. You should however look at optimizing your query. Please post some table structures and your queries to see if anything is obviously slowing things down.
On the other hand, invision board stores the users post count. The only disadvantage I see of doing this is that you are going to be requiring more database calls when deleting posts and such. not really a big deal by any means though.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Table structures are like this

3 tables

forumtopics: (for names of the different forums)

id
topicname
description
rank
cat

forumtopicmain: (for names of threads within forums)

id
topicname
author
forumid
lastreply
time
threadstatus
type

forumentries (for the "meat" of the messages)

id
entry
time
topicid
forumid
istopic
author
time2
threadstatus

My pages..

To display the list of forums (forums.php)

Code: Select all

<?
$description = &quote;Forums - Share your thoughts, ideas, or just babble&quote;;
require 'header.php';

if(isset($_COOKIEї'username'])){ mysql_query(&quote;UPDATE users SET doing = 'In The Forums' WHERE username = '$theperson'&quote;); } ?>
<center>
<a href=&quote;scrotaye.sascha.hop.clickbank.net&quote; target=&quote;top&quote;><img src=&quote;http://www.internetdownloads.org/DownloadFreeDVDMovies.gif&quote; border=&quote;0&quote;></a>
</center>
<a href=&quote;forums.php&quote;>Show My Pro Forums</a> >
						<table width=&quote;100%&quote; cellspacing=&quote;0&quote; cellpadding=&quote;3&quote; style=&quote;border: solid 1px #000000&quote;>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;60%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>General Forums</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Topics</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Posts</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;20%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Last Post</font></B>
								</td>
							</tr>
<?
$query = mysql_query(&quote;SELECT id, topicname, description FROM forumtopics WHERE cat = 'general' ORDER BY rank&quote;);
while($array = mysql_fetch_array($query)) {
$query2 = mysql_query(&quote;SELECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;'&quote;); ?>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;60%&quote; align=&quote;left&quote;><a href=&quote;showforum.php?forumid=<? echo $arrayї'id']; ?>&quote;><? echo $arrayї'topicname']; ?></a><BR><font size=&quote;2&quote;><? echo $arrayї'description']; ?></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $query3 = mysql_query(&quote;SElECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' AND istopic = 'y'&quote;); $numrows2 = mysql_num_rows($query3); echo $numrows2; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $numrows = mysql_num_rows($query2); echo $numrows; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;20%&quote; align=&quote;left&quote;><font size=&quote;2px&quote;><? $array4 = mysql_fetch_array(mysql_query(&quote;SELECT time, author FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' ORDER BY time2 desc LIMIT 1&quote;)); echo $array4ї'time']; ?></font><BR><font size=&quote;2&quote;>by</font> <a href=&quote;showme.php?u=<? echo $array4ї'author']; ?>&quote;><? echo $array4ї'author']; ?></a>
								</td>
							</tr>
<? } ?>
						</table>
						<BR>
						<table width=&quote;100%&quote; cellspacing=&quote;0&quote; cellpadding=&quote;3&quote; style=&quote;border: solid 1px #000000&quote;>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;60%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>People Forums</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Topics</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Posts</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;20%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Last Post</font></B>
								</td>
							</tr>
<?
$query = mysql_query(&quote;SELECT id, topicname, description FROM forumtopics WHERE cat = 'people' ORDER BY rank&quote;);
while($array = mysql_fetch_array($query)) {
$query2 = mysql_query(&quote;SELECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;'&quote;); ?>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;60%&quote; align=&quote;left&quote;><a href=&quote;showforum.php?forumid=<? echo $arrayї'id']; ?>&quote;><? echo $arrayї'topicname']; ?></a><BR><font size=&quote;2&quote;><? echo $arrayї'description']; ?></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $query3 = mysql_query(&quote;SElECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' AND istopic = 'y'&quote;); $numrows2 = mysql_num_rows($query3); echo $numrows2; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $numrows = mysql_num_rows($query2); echo $numrows; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;20%&quote; align=&quote;left&quote;><font size=&quote;2px&quote;><? $array4 = mysql_fetch_array(mysql_query(&quote;SELECT time, author FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' ORDER BY time2 desc LIMIT 1&quote;)); echo $array4ї'time']; ?></font><BR><font size=&quote;2&quote;>by</font> <a href=&quote;showme.php?u=<? echo $array4ї'author']; ?>&quote;><? echo $array4ї'author']; ?></a>
								</td>
							</tr>
<? } ?>
						</table>
						<BR>
						<table width=&quote;100%&quote; cellspacing=&quote;0&quote; cellpadding=&quote;3&quote; style=&quote;border: solid 1px #000000&quote;>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;60%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Subject Forums</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Topics</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Posts</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;20%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Last Post</font></B>
								</td>
							</tr>
<?
$query = mysql_query(&quote;SELECT id, topicname, description FROM forumtopics WHERE cat = 'subject' ORDER BY rank&quote;);
while($array = mysql_fetch_array($query)) {
$query2 = mysql_query(&quote;SELECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;'&quote;); ?>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;60%&quote; align=&quote;left&quote;><a href=&quote;showforum.php?forumid=<? echo $arrayї'id']; ?>&quote;><? echo $arrayї'topicname']; ?></a><BR><font size=&quote;2&quote;><? echo $arrayї'description']; ?></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $query3 = mysql_query(&quote;SElECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' AND istopic = 'y'&quote;); $numrows2 = mysql_num_rows($query3); echo $numrows2; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><font size=&quote;2&quote;><B><? $numrows = mysql_num_rows($query2); echo $numrows; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;20%&quote; align=&quote;left&quote;><font size=&quote;2px&quote;><? $array4 = mysql_fetch_array(mysql_query(&quote;SELECT time, author FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' ORDER BY time2 desc LIMIT 1&quote;)); echo $array4ї'time']; ?></font><BR><font size=&quote;2&quote;>by</font> <a href=&quote;showme.php?u=<? echo $array4ї'author']; ?>&quote;><? echo $array4ї'author']; ?></a>
								</td>
							</tr>
<? } ?>
						</table>
						<BR>
						<table width=&quote;100%&quote; cellspacing=&quote;0&quote; cellpadding=&quote;3&quote; style=&quote;border: solid 1px #000000&quote;>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;60%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Site Forums</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Topics</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Posts</font></B>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;20%&quote; bgcolor=&quote;#CCCCCC&quote;><B><font size=&quote;2&quote;>Last Post</font></B>
								</td>
							</tr>
<?
$query = mysql_query(&quote;SELECT id, topicname, description FROM forumtopics WHERE cat = 'site' ORDER BY rank&quote;);
while($array = mysql_fetch_array($query)) {
$query2 = mysql_query(&quote;SELECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;'&quote;); ?>
							<tr>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;60%&quote; align=&quote;left&quote;><a href=&quote;showforum.php?forumid=<? echo $arrayї'id']; ?>&quote;><? echo $arrayї'topicname']; ?></a><BR><font size=&quote;2&quote;><? echo $arrayї'description']; ?></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><B><font size=&quote;2&quote;><? $query3 = mysql_query(&quote;SElECT id FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' AND istopic = 'y'&quote;); $numrows2 = mysql_num_rows($query3); echo $numrows2; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;><B><font size=&quote;2&quote;><? $numrows = mysql_num_rows($query2); echo $numrows; ?></B></font>
								</td>
								<td style=&quote;border: solid 1px #000000&quote; width=&quote;20%&quote; align=&quote;left&quote;><font size=&quote;2&quote;><? $array4 = mysql_fetch_array(mysql_query(&quote;SELECT time, author FROM forumentries WHERE forumid = '&quote;.$arrayї'id'].&quote;' ORDER BY time2 desc LIMIT 1&quote;)); echo $array4ї'time']; ?></font><BR><font size=&quote;2&quote;>by</font> <a href=&quote;showme.php?u=<? echo $array4ї'author']; ?>&quote;><? echo $array4ї'author']; ?></a>
								</td>
							</tr>
<? } ?>
						</table>
<a href=&quote;forums.php&quote;>Show My Pro Forums</a> >
<? require 'footer.php'; ?>
The showforum page (showforum.php?forumid=#)

Code: Select all

<?
$description = "Browse forum topics";
require 'header.php';

// See if user is logged in, if so update doing section //
if(isset($_COOKIE['username'])){ mysql_query("UPDATE users SET doing = 'Browsing Forums' WHERE username = '$theperson'"); }

// Generate Page //
$forumid = mysql_real_escape_string(strip_tags($forumid));
$forumarray = mysql_fetch_array(mysql_query("SELECT topicname FROM forumtopics WHERE id = '$forumid'"));

// BEGIN HERE
$per_page = 25; 
$sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply desc");
if (!$page) { 
$page = 1; 
} 
$prev_page = $page - 1; 
$next_page = $page + 1;
$page_start = ($per_page * $page) - $per_page;
$query = mysql_query($sql_text);
$num_rows = mysql_num_rows($query);
if ($num_rows <= $per_page) { $num_pages = 1; } else if 
(($num_rows % $per_page) == 0) { $num_pages = ($num_rows / $per_page); } else { 
$num_pages = ($num_rows / $per_page) + 1; }
$num_pages = (int) $num_pages;
if (($page > $num_pages) || ($page < 0)) { die("You have specified an invalid page number"); }
$sql_text = $sql_text . " LIMIT $page_start, $per_page"; 
$query = mysql_query($sql_text); ?>
<center>
<a href="http://scrotaye.sascha.hop.clickbank.net" target="top"><img src="http://www.internetdownloads.org/gif%20file4.gif" border="0"></a>
</center><BR>
<a href="forums.php">Show My Pro Forums</a> > <a href="showforum.php?forumid=<? echo $forumid; ?>"><? echo $forumarray['topicname']; ?></a> ><input type="button" onClick="parent.location='newforumtopic.php?forumid=<? echo $forumid; ?>'" value="New Topic">
<table width="100%" cellspacing="0" cellpadding="3" style="border: solid 1px #000000">
							<tr>
								<td style="border: solid 1px #000000" align="center" colspan="5" bgcolor="#CCCCCC"><B><font size="2">A Space For Your Thoughts</font></B>
								</td>
							</tr>
							<tr>
								<td style="border: solid 1px #000000" align="center" width="5%" bgcolor="#CCCCCC">&nbsp;
								</td>
								<td style="border: solid 1px #000000" align="center" width="50%" bgcolor="#CCCCCC"><B><font size="2">Topic</font></B>
								</td>
								<td style="border: solid 1px #000000" align="center" width="10%" bgcolor="#CCCCCC"><B><font size="2">Replies</font></B>
								</td>
								<td style="border: solid 1px #000000" align="center" width="15%" bgcolor="#CCCCCC"><B><font size="2">Author</font></B>
								</td>
								<td style="border: solid 1px #000000" align="center" width="20%" bgcolor="#CCCCCC"><B><font size="2">Last Post</font></B>
								</td>
							</tr>
<? 
$timereplyarray = mysql_fetch_array(mysql_query("SELECT time2 FROM forumentries WHERE forumid = '$forumid' ORDER BY time2 desc LIMIT 1"));
if(mysql_num_rows($query) < 1){ ?>
							<tr>
								<td style="border: solid 1px #000000" bgcolor="#<? if($color3){ echo $color3; } ELSE { echo "#FFFFFF"; } ?>" width="55%" align="left" colspan="5">
								There are no topics yet in this forum.  Make one. 
								</td>
							</tr>
<? }
// BEGIN HERE
if($page == "1"){
$stickyquery = mysql_query("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type = 'sticky' ORDER BY lastreply desc");
while($stickyarray = mysql_fetch_array($stickyquery)){
$stickyquery2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$stickyarray['id']."'");
$stickyreplies = mysql_num_rows($stickyquery2) - 1;
$stickylastpostarray = mysql_fetch_array(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$stickyarray['id']."' ORDER BY time2 DESC LIMIT 1"));
?>
							<tr>
								<td style="border: solid 1px #000000" width="5%" align="center"><? if($stickyarray['threadstatus'] == "open"){ ?><img src="images/thread_open.gif"><? } ELSE { ?><img src="images/thread_locked.gif"> <? } ?>
								</td>
								<td style="border: solid 1px #000000" width="50%" align="left"><a href="showthread.php?threadid=<? echo $stickyarray['id']; ?>&page=1"><? echo "STICKY: ".stripslashes($stickyarray['topicname']).""; ?></a>
								</td>
								<td style="border: solid 1px #000000" width="10%" align="center"><B><font size="2"><? echo $stickyreplies; ?></B>
								</td>
								<td style="border: solid 1px #000000" width="15%" align="center"><a href="showme.php?u=<? echo $stickyarray['author']; ?>"><? echo $stickyarray['author']; ?></a>
								</td>
								<td style="border: solid 1px #000000" width="20%" align="left"><font size="2"><? echo $stickylastpostarray['time']; ?></font><BR><font size="2">by</font> <a href="showme.php?u=<? echo $stickylastpostarray['author']; ?>"><? echo $stickylastpostarray['author']; ?></a>
								</td>
							</tr>
<?
} }

while($array = mysql_fetch_array($query)) { 
$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
$replies = mysql_num_rows($query2) - 1;
$lastpostarray = mysql_fetch_array(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));


// Generate Number Of Pages For Topic //
$per_page2 = 25; 
$sql_text2 = ("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
$query2 = mysql_query($sql_text2);
$page_start2 = ($per_page2 * $page2) - $per_page2; 
$num_rows2 = mysql_num_rows($query2); 
if ($num_rows2 <= $per_page2) { $num_pages2 = 1; } else if
(($num_rows2 % $per_page2) == 0) { $num_pages2 = ($num_rows2 / $per_page2); } else {
$num_pages2 = ($num_rows2 / $per_page2) + 1; }
$num_pages2 = (int) $num_pages2; 
$sql_text2 = $sql_text2 . " LIMIT $page_start2, $per_page2";
?>
							<tr>
								<td style="border: solid 1px #000000" width="5%" align="center"><? if($array['threadstatus'] == "open"){ ?><img src="images/thread_open.gif"><? } ELSE { ?><img src="images/thread_locked.gif"> <? } ?>
								</td>
								<td style="border: solid 1px #000000" width="50%" align="left"><a href="showthread.php?threadid=<? echo $array['id']; ?>&page=1"><? echo stripslashes($array['topicname']); ?></a><? if($num_pages2 > 1){ echo "<BR><font size=1>Page: </font>"; for ($i = 1; $i <= $num_pages2; $i++) {echo "<a href=\"showthread.php?threadid=".$array['id']."&page=$i\"><font size=1>$i</font></a> "; } } ?>
								</td>
								<td style="border: solid 1px #000000" width="10%" align="center"><B><font size="2"><? echo $replies; ?></B>
								</td>
								<td style="border: solid 1px #000000" width="15%" align="center"><a href="showme.php?u=<? echo $array['author']; ?>"><? echo $array['author']; ?></a>
								</td>
								<td style="border: solid 1px #000000" width="20%" align="left"><font size="2"><? echo $lastpostarray['time']; ?></font><BR><font size="2">by</font> <a href="showme.php?u=<? echo $lastpostarray['author']; ?>"><? echo $lastpostarray['author']; ?></a>
								</td>
							</tr>
<? } ?>
						</table>
<? echo "<font size=2>Page Number: ";
if ($prev_page) { echo "<a href=\"$PHP_SELF?forumid=$forumid&page=$prev_page\">Prev</a> "; }
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $page) { echo " <a href=\"$PHP_SELF?forumid=$forumid&page=$i\">$i</a> "; } else { 
echo " $i "; } }

if ($page != $num_pages) { echo "<a href=\"$PHP_SELF?forumid=$forumid&page=$next_page\">Next</a>"; } ?><BR>

<a href="forums.php">Show My Pro Forums</a> > <a href="showforum.php?forumid=<? echo $forumid; ?>"><? echo $forumarray['topicname']; ?></a> ><input type="button" onClick="parent.location='newforumtopic.php?forumid=<? echo $forumid; ?>'" value="New Topic"><BR>
<p align="left"><img src="images/to_e.gif"> Thread Open&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<img src="images/tl_e.gif"> Thread Locked</font></p>
<? require 'footer.php'; ?>
and finally, the page that displays each individual thread (showthread.php?threadid=#)

Code: Select all

<?
$description = "Viewing forum topic";
require 'header.php';

if(isset($_COOKIE['username'])){ mysql_query("UPDATE users SET doing = 'Viewing Forum Topic' WHERE username = '$theperson'"); }

// Purfiy URL String Information //
$threadid = mysql_real_escape_string(strip_tags($threadid));
$page = mysql_real_escape_string(strip_tags($page));

// Generate Page //
$forumsql = "SELECT topicname, forumid FROM forumtopicmain WHERE id = '$threadid'";
$forumquery = mysql_query($forumsql);
$forumarray = mysql_fetch_array($forumquery);
$forumnamequery = mysql_query("SELECT topicname, id FROM forumtopics WHERE id = '".$forumarray['forumid']."'");
$forumnamearray = mysql_fetch_array($forumnamequery); ?>
<center>
<a href="http://scrotaye.sascha.hop.clickbank.net" target="top"><img src="http://www.internetdownloads.org/gif%20file2.gif" border="0"></a>
</center><BR>
<a href="forums.php">Show My Pro Forums</a> > <a href="showforum.php?forumid=<? echo $forumnamearray['id']; ?>"><? echo $forumnamearray['topicname']; ?></a> > <B><font size="2"><? echo stripslashes($forumarray['topicname']); ?></font></B>
<table width="100%" cellspacing="0" cellpadding="3" style="border: solid 1px #000000">
							<tr>
								<td style="border: solid 1px #000000" align="center" colspan="4" bgcolor="#CCCCCC"><B><font size="2">A Space For Your Thoughts</font></B>
								</td>
							</tr>
<? $getentriessql = "SELECT * FROM forumentries WHERE topicid = '$threadid' ORDER BY time2 asc";
$getentriesquery = mysql_query($getentriessql);
$per_page = 25;
$sql_text = "SELECT * FROM forumentries WHERE topicid = '$threadid' ORDER BY time2 asc";
$query = mysql_query($sql_text);
if (!$page) { $page = 1; }
$prev_page = $page - 1; 
$next_page = $page + 1; 
$page_start = ($per_page * $page) - $per_page; 
$num_rows = mysql_num_rows($query);
if ($num_rows <= $per_page) { 
$num_pages = 1; 
} else if (($num_rows % $per_page) == 0) { 
$num_pages = ($num_rows / $per_page); 
} else { 
$num_pages = ($num_rows / $per_page) + 1; 
} 
$num_pages = (int) $num_pages; 

if (($page > $num_pages) || ($page < 0)) {
die("You have specified an invalid page number"); 
}
$sql_text = $sql_text . " LIMIT $page_start, $per_page"; 
$query = mysql_query($sql_text); ?>
							<tr>
								<td colspan="4" bgcolor="#CCCCCC" style="border: solid 1px #000000">
									<table width="100%" cellspacing="0" cellpadding="0" border="0" bgcolor="#CCCCCC">
										<tr>
											<td width="75%" bgcolor="#CCCCCC" valign="top"><B><font size="2"><? echo stripslashes($forumarray['topicname']); ?></font></B>
											</td>
											<td width="25%" align="right"><B><font size="2">Jump To Page:</B></font><BR>
											<? $i2 = ($num_rows/$per_page) + 1;
											for($i = 1; $i<$i2; $i++){ ?><a href="showthread.php?threadid=<? echo $threadid; ?>&page=<? echo $i; ?>"><? echo $i; ?>&nbsp;</a><? } ?>
											</td>
										</tr>
									</table>
								</td>
							</tr>
<? while($getentriesarray = mysql_fetch_array($query)) { 
$getpicturesquery = mysql_query("SELECT username, filename FROM pictures WHERE username = '".$getentriesarray['author']."'");
$getpicturesarray = mysql_fetch_array($getpicturesquery);
$getnumberquery = mysql_query("SELECT id, author, istopic FROM forumentries WHERE author = '".$getentriesarray['author']."'");
$getnumberarray = mysql_fetch_array($getnumberquery);
$number = mysql_num_rows($getnumberquery);
?>
							<tr>
								<td style="border: solid 1px #000000"" align="left">
									<table width="100%" cellspacing="0" cellpadding="0" border="0">
										<tr>
											<td width="25%" align="left" valign="top">
											<? if(mysql_num_rows($getpicturesquery) < 1) { echo "<font size=2>Account Gone.</font>"; } ELSE {
											echo "<a href=showme.php?u=".$getpicturesarray['username']."><img src=thumbs/".$getpicturesarray['username']."/".$getpicturesarray['filename']." border=0></a>"; } ?><BR><?
											echo "<a href=showme.php?u=".$getpicturesarray['username'].">".$getpicturesarray['username']."</a>";
											$onlinestatusquery = mysql_query("SELECT user_last_visit FROM users WHERE username = '".$getpicturesarray['username']."'");
											$onlinestatusarray = mysql_fetch_array($onlinestatusquery);
											$time20 = time();
											$timediff = $time20 - 300;
											if($onlinestatusarray['user_last_visit'] >= $timediff){ echo "<BR><font color=red size=2><B>Online</B></font>"; } ?>
											<BR>
											<B><font size="2">Posts</B>: <? echo $number; ?></font><BR>
											<B><font size="2">Rank</B>: <? if($getnumberarray['author'] == "scrotaye") { echo "Site Owner";} ELSEIF
											($getnumberarray['author'] == "artbalaoro"){ echo "Forum Mod"; } ELSE {
											if($number > "999") { echo "Supreme"; } ELSEIF
											($number > "500") { echo "Ultra"; } ELSEIF
											($number > "200") { echo "Standard"; } ELSEIF
											($number > "100") { echo "Junior"; } ELSE
											{ echo "n00b"; } } ?></font>
											</td>
											<td width="75%" valign="top"><font size="2"><? echo stripslashes($getentriesarray['entry']); ?></font><hr width="100%" color="#000000">
											<font size="2">Posted on <? echo $getentriesarray['time']; ?></font>
											<? $adminarray = array("scrotaye", "artbalaoro"); if(in_array($theperson, $adminarray)){ echo "<p><input type='button' value='Delete Post' onClick=\"parent.location='showthread.php?action=deleteentry&entrytodelete=".$getentriesarray['id']."&threadid=$threadid&page=$page'\"></p>"; } ?> <p align="right"><a href="showthread.php?threadid=<? echo $threadid; ?>&page=<? echo $num_pages; ?>&mode=quote&m=<? echo $getentriesarray['id']; ?>#quote"><img src="images/quote.gif" alt="Quote" border="0"></a>
											<? if($getpicturesarray['username'] == $theperson){ if($getentriesarray['istopic'] == "n") { echo " <a href=forumsubmit.php?action=deletepost&threadid=$threadid&author=".$getentriesarray['author']."&postid=".$getentriesarray['id']."&page=$page><img src=images/delete.gif alt=Delete border=0></a> "; } echo "<a href=editforumpost.php?postid=".$getentriesarray['id']."><img src=images/edit.gif alt=Edit border=0></a>"; } ?></p>
											</td>
										</tr>
									</table>
								</td>
							</tr>
<? } ?>
							<tr>
								<td style="border: solid 1px #000000" align="center" bgcolor="#CCCCCC">
<font size="2"><? echo "Page Number: ";
if ($prev_page) { 
echo "<a href=$PHP_SELF?threadid=$threadid&page=$prev_page>Prev</a> ";
}
for ($i = 1; $i <= $num_pages; $i++) { 
if ($i != $page) { 
echo " <a href=$PHP_SELF?threadid=$threadid&page=$i>$i</a> "; 
} else { 
echo " $i "; 
} 
} 
if ($page != $num_pages) { 
echo "<a href=$PHP_SELF?threadid=$threadid&page=$next_page>Next</a>";
} ?></font>
								</td>
<? if($page == $num_pages){
$getlastreplystatusarray = mysql_fetch_array(mysql_query("SELECT threadstatus FROM forumentries WHERE topicid = '$threadid'"));

// Check to see if thread is locked 
if($getlastreplystatusarray['threadstatus'] == "locked"){ ?>
							<tr>
								<td style="border: solid 1px #000000" align="center" bgcolor="#CCCCCC"><B><font size="2">Post A Reply</font></B>
								</td>
							</tr>
							<tr>
								<td style="border: solid 1px #000000" align="center"><p align="center"><B><font size="2">THREAD IS LOCKED</font></B></p>
								</td>
							</tr>
							<? } ELSE { ?>
							<tr>
								<td style="border: solid 1px #000000" align="center" bgcolor="#CCCCCC"><B><font size="2">Post A Reply</font></B>
								</td>
							</tr>
							<tr>
								<td style="border: solid 1px #000000" align="center">
<? // check if they are posting a quote
if($mode){
if($mode == "quote"){
$m = mysql_real_escape_string(strip_tags($m));
$piarray = mysql_fetch_array(mysql_query("SELECT author, entry FROM forumentries WHERE id = '$m'"));
$quotemessage = stripslashes($piarray['entry']);
$quotemessage2 = str_replace($smiliesimg, $smilies, $quotemessage);
$quotemessage3 = str_replace("<img src=smilies/arrow.gif>", ":arrow:", $quotemessage2);
$quotemessage4 = str_replace("<img src=smilies/badgrin.gif>", ";-D", $quotemessage3);
$quotemessage5 = str_replace("<img src=smilies/biggrin.gif>", ":-D", $quotemessage4);
$quotemessage6 = str_replace("<img src=smilies/confused.gif>", ":-S", $quotemessage5);
$quotemessage7 = str_replace("<img src=smilies/cool.gif>", "B-)", $quotemessage6);
$quotemessage8 = str_replace("<img src=smilies/cry.gif>", ":((", $quotemessage7);
$quotemessage9 = str_replace("<img src=smilies/doubt.gif>", ":-/", $quotemessage8);
$quotemessage10 = str_replace("<img src=smilies/evil.gif>", "V:)", $quotemessage9);
$quotemessage11 = str_replace("<img src=smilies/exclaim.gif>", ":-!", $quotemessage10);
$quotemessage12 = str_replace("<img src=smilies/idea.gif>", "o-)", $quotemessage11);
$quotemessage13 = str_replace("<img src=smilies/lol.gif>", ":))", $quotemessage12);
$quotemessage14 = str_replace("<img src=smilies/mad.gif>", "X-(", $quotemessage13);
$quotemessage15 = str_replace("<img src=smilies/neutral.gif>", ":-|", $quotemessage14);
$quotemessage16 = str_replace("<img src=smilies/question.gif>", ":-?", $quotemessage15);
$quotemessage17 = str_replace("<img src=smilies/razz.gif>", ":-P", $quotemessage16);
$quotemessage18 = str_replace("<img src=smilies/redface.gif>", ":-$", $quotemessage17);
$quotemessage19 = str_replace("<img src=smilies/rolleyes.gif>", "8-|", $quotemessage18);
$quotemessage20 = str_replace("<img src=smilies/sad.gif>", ":-(", $quotemessage19);
$quotemessage21 = str_replace("<img src=smilies/shock.gif>", ":-o", $quotemessage20);
$quotemessage22 = str_replace("<img src=smilies/smile.gif>", ":-)", $quotemessage21);
$quotemessage23 = str_replace("<img src=smilies/surprised.gif>", ":.o", $quotemessage22);
$quotemessage24 = str_replace("<img src=smilies/wink.gif>", ";-)", $quotemessage23);
$quotemessage25 = str_replace("<br />", "", $quotemessage24);
 } } ?>
								<p id="quote"><form action="forumsubmit.php?threadid=<? echo "$threadid&page=$num_pages"; ?>" method="post" enctype="application/x-www-form-urlencoded" name="frmPost" id="frmPost">
								<input type="hidden" name="action" value="postreply">
								<textarea name="entry" id="txtPost" rows="5" cols="40" style="border: solid 1px #000000; cursor: default"><? if($mode){
								if($mode == "quote"){ ?>[quote]Originally posted by <? echo $piarray['author']; ?>


<? echo $quotemessage25; ?>[/quote] <? } } ?>
</textarea><BR>
								<font size="2">Links will be automatically converted into clickable hyperlinks.<BR><BR>
								<B>Insert Image:</B> [img]http://www.link.com/your/pic.jpg[/img]<BR>
								You must change the link inside the IMG tags.</font><BR><BR>
								<table border="1px" bordercolor="#000000" cellspacing="0" cellpadding="5">
									<tr>
										<td align="center"><B><font size="2">Add A Smiley</font></B></center>
										</td>
									</tr>
									<tr>
										<td align="center"><a href="#" OnClick="insertSmilie(':arrow:'); return false;"><img src="smilies/arrow.gif" alt="Arrow" border="0"></a>  <a href="#" OnClick="insertSmilie(';-D'); return false;"><img src="smilies/badgrin.gif" alt="Bad Grin" border="0"></a>  <a href="#" OnClick="insertSmilie(':-D'); return false;"><img src="smilies/biggrin.gif" alt="Big Grin" border="0"></a>  <a href="#" OnClick="insertSmilie(':-S'); return false;"><img src="smilies/confused.gif" alt="Confused" border="0"></a>  <a href="#" OnClick="insertSmilie('B-)'); return false;"><img src="smilies/cool.gif" alt="Cool" border="0"></a>  <a href="#" OnClick="insertSmilie(':(('); return false;"><img src="smilies/cry.gif" alt="Cry" border="0"></a>  <a href="#" OnClick="insertSmilie(':-/'); return false;"><img src="smilies/doubt.gif" alt="Doubt" border="0"></a>  <a href="#" OnClick="insertSmilie('V:)'); return false;"><img src="smilies/evil.gif" alt="Evil" border="0"></a><BR>
					<a href="#" OnClick="insertSmilie(':-!'); return false;"><img src="smilies/exclaim.gif" alt="Exclaim" border="0"></a>  <a href="#" OnClick="insertSmilie('o-)'); return false;"><img src="smilies/idea.gif" alt="Idea" border="0"></a>  <a href="#" OnClick="insertSmilie(':))'); return false;"><img src="smilies/lol.gif" alt="Lol" border="0"></a>  <a href="#" OnClick="insertSmilie('x-('); return false;"><img src="smilies/mad.gif" alt="Mad" border="0"></a>  <a href="#" OnClick="insertSmilie(':-|'); return false;"><img src="smilies/neutral.gif" alt="Neutral" border="0"></a>  <a href="#" OnClick="insertSmilie(':-?'); return false;"><img src="smilies/question.gif" alt="Question" border="0"></a>  <a href="#" OnClick="insertSmilie(':-P'); return false;"><img src="smilies/razz.gif" alt="Razz" border="0"></a>  <a href="#" OnClick="insertSmilie(':-$'); return false;"><img src="smilies/redface.gif" alt="Redface" border="0"></a><BR>
					<a href="#" OnClick="insertSmilie('8-|'); return false;"><img src="smilies/rolleyes.gif" alt="Roll Eyes" border="0"></a>  <a href="#" OnClick="insertSmilie(':-('); return false;"><img src="smilies/sad.gif" alt="Sad" border="0"></a>  <a href="#" OnClick="insertSmilie(':-o'); return false;"><img src="smilies/shock.gif" alt="Shock" border="0"></a>  <a href="#" OnClick="insertSmilie(':-)'); return false;"><img src="smilies/smile.gif" alt="Smile" border="0"></a>  <a href="#" OnClick="insertSmilie(':.o'); return false;"><img src="smilies/surprised.gif" alt="Surprised" border="0"></a>  <a href="#" OnClick="insertSmilie(';-)'); return false;"><img src="smilies/wink.gif" alt="Wink" border="0"></a>
										</td>
									</tr>
								</table><BR>
								<input type="submit" value="Submit Reply">
								</form></P>
								</td>
							</tr> 
							<? } } ?>
</table>
<a href="forums.php">Show My Pro Forums</a> > <a href="showforum.php?forumid=<? echo $forumnamearray['id']; ?>"><? echo $forumnamearray['topicname']; ?></a> > <B><font size="2"><? echo stripslashes($forumarray['topicname']); ?></font></B><BR>
<BR>
<? require 'footer.php'; ?>

I know I may have done things a bit assbackwards, so please don't poke fun. Give constructive criticizm, or help me speed things up a little bit. The page that seems to take the longest to load is showforum.php.
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

Post by programmermatt »

Here is my revised code for forums.php. I think I cut out alot of queries and it should work with maybe one or two bugs, I haven't tested it because I am off to eat.

Code: Select all

&lt;?php
$description = &quote;Forums - Share your thoughts, ideas, or just babble&quote;;
require 'header.php';

//eliminate a query by checking it their status isn't already 'In The Forums'
if( isset($_COOKIE&#1111;'username']) &amp;&amp; $_COOKIE&#1111;'doing'] == 'In The Forums' ) {
	mysql_query(&quote;UPDATE users SET doing = 'In The Forums' WHERE username = '$theperson'&quote;);
	$_COOKIE&#1111;'doing'] = 'In The Forums';
}
?&gt;
&lt;center&gt;
&lt;a href=&quote;scrotaye.sascha.hop.clickbank.net&quote; target=&quote;top&quote;&gt;&lt;img src=&quote;http://www.internetdownloads.org/DownloadFreeDVDMovies.gif&quote; border=&quote;0&quote;&gt;&lt;/a&gt;
&lt;/center&gt;
&lt;a href=&quote;forums.php&quote;&gt;Show My Pro Forums&lt;/a&gt; &gt;
                        &lt;table width=&quote;100%&quote; cellspacing=&quote;0&quote; cellpadding=&quote;3&quote; style=&quote;border: solid 1px #000000&quote;&gt;
                            &lt;tr&gt;
                                                            &lt;td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;60%&quote; bgcolor=&quote;#CCCCCC&quote;&gt;&lt;B&gt;&lt;font size=&quote;2&quote;&gt;
&lt;?php
$query = mysql_query(&quote;SELECT id, topicname, description, cat FROM forumtopics ORDER BY rank&quote;);
while($array = mysql_fetch_array($query)) {

	$query2 = mysql_query(&quote;SELECT id, istopic, time, author FROM forumentries WHERE forumid = '&quote;.$array&#1111;'id'].&quote;' ORDER BY time2 desc&quote;);

	if( $array&#1111;'cat'] == 'general' ) {
		echo &quote;General Forums&quote;;
	} elseif ( $array&#1111;'cat'] == 'people' ) {
		echo &quote;People Forums&quote;;
	} elseif ( $array&#1111;'cat'] == 'subject' ) {
		echo &quote;Subject Forums&quote;;	
	} elseif ( $array&#1111;'cat'] == 'site' ) {
		echo &quote;Site Forums&quote;;	
	}
?&gt; 
								&lt;/font&gt;&lt;/B&gt;    
								&lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;&gt;&lt;B&gt;&lt;font size=&quote;2&quote;&gt;Topics&lt;/font&gt;&lt;/B&gt;
                                &lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;10%&quote; bgcolor=&quote;#CCCCCC&quote;&gt;&lt;B&gt;&lt;font size=&quote;2&quote;&gt;Posts&lt;/font&gt;&lt;/B&gt;
                                &lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; align=&quote;center&quote; width=&quote;20%&quote; bgcolor=&quote;#CCCCCC&quote;&gt;&lt;B&gt;&lt;font size=&quote;2&quote;&gt;Last Post&lt;/font&gt;&lt;/B&gt;
                                &lt;/td&gt;
                            &lt;/tr&gt;
                            &lt;tr&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; width=&quote;60%&quote; align=&quote;left&quote;&gt;
                                	&lt;a href=&quote;showforum.php?forumid=&lt;? echo $array&#1111;'id']; ?&gt;&quote;&gt; &lt;? echo $array&#1111;'topicname']; ?&gt; &lt;/a&gt;
                                	&lt;br /&gt;
                                	&lt;font size=&quote;2&quote;&gt;&lt;? echo $array&#1111;'description']; ?&gt;&lt;/font&gt;
                                &lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;&gt;
                                	&lt;font size=&quote;2&quote;&gt;&lt;B&gt;
                                	&lt;? 
                                	$numrows2 = 0;
                                	while ($row = $mysql_fetch_array($query2) ) {
                                		if( $row&#1111;'istopic'] == 'y' ) $numrows2++;
                                	}
                                	echo $numrows2;
                                	?&gt;
                                	&lt;/B&gt;&lt;/font&gt;
                                &lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; width=&quote;10%&quote; align=&quote;center&quote;&gt;
                                	&lt;font size=&quote;2&quote;&gt;&lt;B&gt;
                                	&lt;? 
                                	$numrows = mysql_num_rows($query2);
                                		echo $numrows; ?&gt;
                                	&lt;/B&gt;&lt;/font&gt;
                                &lt;/td&gt;
                                &lt;td style=&quote;border: solid 1px #000000&quote; width=&quote;20%&quote; align=&quote;left&quote;&gt;
                                	&lt;font size=&quote;2px&quote;&gt;
                                	&lt;? 
                                	$array4 = mysql_fetch_array($query2);
                                	echo $array4&#1111;'time'];
                                	?&gt;
                                	&lt;/font&gt;&lt;BR&gt;
                                	&lt;font size=&quote;2&quote;&gt;by&lt;/font&gt; 
                                	&lt;a href=&quote;showme.php?u=&lt;? echo $array4&#1111;'author']; ?&gt;&quote;&gt;&lt;? echo $array4&#1111;'author']; ?&gt;&lt;/a&gt;
                                &lt;/td&gt;
                            &lt;/tr&gt;
&lt;? 

}
?&gt;
                        &lt;/table&gt;
                     
&lt;a href=&quote;forums.php&quote;&gt;Show My Pro Forums&lt;/a&gt; &gt;
&lt;? require 'footer.php'; ?&gt;
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

if you'd like to see a working copy of the forums, go to http://www.showmypro.com/forums.php .

And thanks for the forum page! I can see now how my code was more repetitive than it should be.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

What you should be gearing towards is optimizing your queries through some joins.
If you give me a database export sample then I am more than willing to assist you.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

okay, what specifically would you like to see?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

a small export of

forumtopics: (for names of the different forums)

id
topicname
description
rank
cat

forumtopicmain: (for names of threads within forums)

id
topicname
author
forumid
lastreply
time
threadstatus
type

forumentries (for the "meat" of the messages)

id
entry
time
topicid
forumid
istopic
author
time2
threadstatus
Post Reply