combining queries

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

combining queries

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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'];
}
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

...

Post 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?
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
programmermatt
Forum Commoner
Posts: 65
Joined: Tue Mar 15, 2005 5:03 pm
Contact:

Post 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.
Post Reply