Page 1 of 1

combining queries

Posted: Thu Jun 23, 2005 5:17 am
by s.dot
On my forums page, I have 4 queries that are basically the same thing with the exception of one word.

I was wondering if it's possible to combine them in one while loop, using a variable for the changing word.

Here are my 4 queries

Code: Select all

$query = mysql_query("SELECT id, topicname, description FROM forumtopics WHERE cat = 'general' ORDER BY rank");
while($array = mysql_fetch_array($query))
{
     // code
}

$query = mysql_query("SELECT id, topicname, description FROM forumtopics WHERE cat = 'people' ORDER BY rank");
while($array = mysql_fetch_array($query))
{
     // code
}

$query = mysql_query("SELECT id, topicname, description FROM forumtopics WHERE cat = 'subject' ORDER BY rank");
while($array = mysql_fetch_array($query))
{
     // code
}

$query = mysql_query("SELECT id, topicname, description FROM forumtopics WHERE cat = 'site' ORDER BY rank");
while($array = mysql_fetch_array($query))
{
     // code
}
I'm pretty sure all of that is unnecessary, right?

What I would like is something like this

Code: Select all

$query = mysql_query("SELECT id, topicname, description FROM forumtopics WHERE cat = '$variable' ORDER BY rank");
And this would have to be in a loop, because I'd need it to run through the general query, the people query, the subject query, and the site query.

Grr, hope I explained myself okay.

Just to summarize

4 separate queries here
need to loop through them, with the query changing each time it goes through.

Posted: Thu Jun 23, 2005 5:50 am
by timvw

Code: Select all

SELECT id, topicname, description, cat 
FROM forumtopics 
WHERE cat IN ('general', 'general', 'people', 'subject')  
ORDER BY cat, rank

Code: Select all

$current_cat = null;
while ($row = mysql_fetch_assoc($rs))
{
  if ($current_cat != $row['cat'])
  {
    // new category is found
    // might want to write a new heading...

  }

  // do stuff with the record

  $current_cat = $row['cat'];
}

Posted: Thu Jun 23, 2005 10:57 am
by s.dot
Would doing it the way you described, speed up the page any more?

As of right now, the page is taking 3-4 seconds to load.

...

Posted: Thu Jun 23, 2005 6:29 pm
by s.dot
This is the absolute best I could optimize this page.

Code: Select all

<?
$description = "Forums - Share your thoughts, ideas, or just babble";
require 'header.php'; ?>
<a href="forums.php">Show My Pro Forums</a> >
<?
$query = mysql_query("SELECT id, topicname, description, cat FROM forumtopics WHERE cat IN ('General', 'People', 'Subject', 'Site')  ORDER BY forumrank, rank");

$current_cat = null; ?>
<table width="100%" style="border: solid 1px #000000" cellspacing="0" cellpadding="3">
<?
while($row = mysql_fetch_assoc($query))
{
	if($current_cat != $row['cat'])
	{ ?>
	<tr>
		<td class="forum" align="center" width="60%" bgcolor="#CCCCCC"><B><? echo $row['cat']; ?> Forums</B>
		</td>
		<td class="forum" align="center" width="10%" bgcolor="#CCCCCC"><B>Topics</B>
		</td>
		<td class="forum" align="center" width="10%" bgcolor="#CCCCCC"><B>Posts</B>
		</td>
		<td class="forum" align="center" width="20%" bgcolor="#CCCCCC"><B>Last Post</B>
		</td>
	</tr><?
	}
	$query3 = mysql_query("SELECT id FROM forumtopicmain WHERE forumid = '".$row['id']."'");
	$query4 = mysql_query("SELECT id FROM forumentries WHERE forumid = '".$row['id']."'");
	$numrows = mysql_num_rows($query3);
	$numrows2 = mysql_num_rows($query4); ?>
	<tr>
		<td class="forum" width="60%" align="left"><a href="showforum.php?forumid=<? echo $row['id']; ?>"><? echo $row['topicname']; ?></a><BR><font size="2"><? echo $row['description']; ?></font>
		</td>
		<td class="forum" width="10%" align="center"><font size="2"><B><? echo $numrows; ?></B></font>
		</td>
		<td class="forum" width="10%" align="center"><font size="2"><B><? echo $numrows2; ?></B></font>
		</td>
		<td class="forum" width="20%" align="left">
		<? $array4 = mysql_fetch_array(mysql_query("SELECT time, author FROM forumentries WHERE forumid = '".$row['id']."' ORDER BY time2 desc LIMIT 1"));
		echo $array4['time']; ?><BR>by</font> 
		<a href="showme.php?u=<? echo $array4['author']; ?>"><? echo $array4['author']; ?></a>
		</td>
	</tr>
	<? $current_cat = $row['cat'];
} ?>
</table>
<a href="forums.php">Show My Pro Forums</a> >
<? require 'footer.php'; ?>
You can look at it at http://www.showmypro.com/forums.php .

It is still slow loading ;/

If I store the individual number counts for rows and topics, instead of using mysql_num_rows() function, is that the only thing left to do to speed up the loading?

If I do store a number, instead of counting rows, what happens when 2 people make a post at the same time? would the number count be off? or is that possible?

Posted: Thu Jun 23, 2005 6:42 pm
by programmermatt

Code: Select all

$query3 = mysql_query("SELECT id FROM forumtopicmain WHERE forumid = '".$row['id']."'");
    $query4 = mysql_query("SELECT id FROM forumentries WHERE forumid = '".$row['id']."'");
Instead of doing this, consider adding to the forum table the fields `num_topics` and `num_posts`. Then, everytime someone posts they make increment those values. Eliminates two queries every page load, but adds one on the posting page (?pends on how you handle posting?), but considering people look at the forum page more often then they post, it is more than worth it.

BTW, have you considered using an opensource, premade forum like phpBB or SMF? Just asking.

Posted: Thu Jun 23, 2005 6:47 pm
by s.dot
I have used PHPBB in the past, and I decided to make my own forums to fit in with the site, and people use it way more now, so it was worth it to make it. Plus I can customize it exactly how I want it.

Now, about the num_topics and num_posts..

what happens if two users make a post at the exact same time? Both of them would grab the same number from the db and increment it by 1. Thus, there would be two forum posts, but the count only shows one additional.

Is it feasable to say that could happen?

Posted: Thu Jun 23, 2005 6:50 pm
by programmermatt

Code: Select all

Update table set num_posts=num_posts+1
It works and you don't have to worry, because the database increments it.

..of course your query would look slightly different :)

...Also, make a utility that only you can run that double checks that the counts and run it every once and awhile.