Marcjon Louwersheimer wrote:
I'm working on a forum. When it displays a forum index, it gets all the topics and depending on the offset, displays only ten at a time. Now that's a single query. But problem comes when I display how many replies each post has. So far, when it does the while loop, it does a query for each item. Here's the code:
...
while ($row = mysql_fetch_assoc($indexresult)) // Run through all the posts
{
$while_offset++;
if ($while_offset > $offset AND $while_count <= 10){ // if the post is in the range
$while_count++;
this would mean you fetch the whole result (not just 10 lines) and loop over all of it.
it would be much better to make use of the mySQL syntax:
[LIMIT [offset,] row_count | row_count OFFSET offset]
see: http://www.mysql.com/doc/en/SELECT.html
free advice: take the time to read manuals even if your not stuck on a particular problem, you often come across solutions to problems you haven't encountered yet! if you're serious about PHP and mySQL then reading both manuals back2front will save you time in the long run + open your eyes to a whole lot of stuff - if you don't understand something, skip it and move on - something you read later will probably
help to explain it.
...
Is there an easier way to do this, maybe with a sub select statement
yes.
maybe? I only know basic mysql. I hope this is understandable...
subselects are available in mySQL 4 I believe (don't hold me to it)... my current DB preference goes to Firebird 1.5
Oh yeah, you might wonder why I use the custom offset instead of using
the LIMIT and OFFSET clauses in the main MySQL query. The problem with
this is
that when I order the posts, it would only order them by the one gotten,
not them all, which is what I wanted.
which is the same thing, assuming you are using the same ORDER BY clause. ok, so in one case the result set doesn't contain certain rows but these are the rows that you wouldn't show anyway.
your original question about minimizing the number of SQL queries is answered in the reply(s?) to one of your other posts.
subject= Re: Forum Script
date = 3/7/2004 9:23 PM (god knows how accurate that is)
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
