First,I am a Hong Kong people so my english is not good.
Please don`t mind if You don`t know what I am talking about.
And this is my question:
We always write forum program as the code below:
$result_maincat = mysql_query("SELECT * FROM maincat ORDER BY seq");
while ($row_main = mysql_fetch_array($result_maincat)) {
.........

$result_subcat = mysql_query("SELECT * FROM subcat WHERE hid='$row_main[id]'");
while ($row_subcat = mysql_fetch_array($result_subcat)) {
......
}
}

The problem is ,if I have many categories and forum,It will query many many times,Can it improve in by another solution which have the same result but the query times can decrease?

In fact I have a idea,First I just query out all the categories and forum, then we just query two times and start to process the data into the correct way,But I just have the idea,I don`t know how to write it...
Can anyone help me?
Thanks at all~
:D

Well, it looks like you're going to select ALL the data in the database, so you might as well just make it one big query, and sort/organize the data in PHP using nested arrays.

The nested array you create will actually be a tree structure -- the top level of nesting will be the main categories, the second level will be the sub-categories, etc.

I can't imagine that you have a lot of categories, though, so your current approach shouldn't be TOO bad.

Usually, when dealing with a tree structure like parent/child relationships (aka main and sub categories), I use a single table. Include a parent_category column in that table. Categories who have a parent_category of 0 are the main categories. Sub-categories will use the primary key of their parent/main category in the parent_category column.

For example:

Table Categories:
id name parent
1 Books 0
2 Music 0
3 Movies 0
4 Harry Potter 5 1
5 Norah Jones 2
6 The Da Vinci Code 1
7 Casablanca 3

Does this schema make sense?


Using your current schema, you should be able to get everything in a single query. I can't guarantee that it will work, but it's a starting point:

SELECT * FROM maincat, subcat
 WHERE maincat.id = subcat.hid
 ORDER BY maincat.seq

This should also work (in fact it's an equivalent query)

SELECT * FROM maincat
 INNER JOIN subcat ON
       maincat.id = subcat.hid
 ORDER BY maincat.seq


Take care,

Nik
http://www.bigaction.org/

Ok!
Thanks
I will try~
^^

although i can appreciate the concept of having just one table with a parent column, i shall warn you about two points:
- this kinda structure will indeed make your calls faster
- you should really take care of the way you handle it in your code:
you shouldn't place a "where" clause using a join...it would be in fact the same (if your db is under Oracle, for example, you can use views), but instead, you should order your query by parentId,categoryId and then process the right tests in php.

may i add,
you should always avoid writing down a statement like "select * from table".
Let's say your table is maincat(id, name, isPublished).
you should always state "select id, name, isPublished from maincat..."
this way, you prevent yourself ( or others ) from having to rewrite code whenever the database would change. you really should get used to it by now already.

php/java developer
NTIC engineer

True, true. In fact, I generally don't have SQL calls floating around in my code. When harping on style, I always tell people to wrap all their SQL calls in accessor functions that returns data. That way, if the database changes at all, you at most would need to modify the SQL in your accessor functions and the rest of the site would continue to work.

This code:

$query = "SELECT id, username, email FROM users";
$result = mysql_query($query);
if ($result !== FALSE)
{
   while ($row = mysql_fetch_array($result))
   {
      echo "User $row[username] ($row[id]): $row[email]\n";
   }
}


Would actually be something more like:

// user.db.inc
<?php
require_once('db.inc'); // generic db wrappers

// returns an array of user data
function get_users()
{
   $query = "SELECT id, username, email FROM users";
   $result = db_query($query); // generic call

   if($result === FALSE)
       return FALSE;

   $users = array();
   while($row = db_fetch_array($result))
   {
       $users[] = array('id' => $row['id'],
                        'username' => $row['username'],
                        'email' => $row['email']);
   }

   return $users;
}

?>

<?php // main script
require_once('user.db.inc');

$users = get_users();
foreach($users as $user)
{
   echo "User {$user['username']} ({$user['id']}): {$user['email']}\n";
}


Hope it all makes sense...! For those with a lot of time on their hands, you can read a couple of the older mailing-list archives where I go into this concept in detail:

  http://p2p.wrox.com/archive/beginnin...2002-04/25.asp
  http://p2p.wrox.com/archive/beginnin...2002-09/40.asp



Take care,

Nik
http://www.bigaction.org/