[PHP/MySQL] Structure problems

[Masius]

Hey there.
Since I got some free time in hands, I decided to practise PHP and MySQL once more and so I decided to make a forum from scratch, but I'm having a bit of a problem trying to figure out my structure, relating the Categories and SubForums.

Example Structure:

- Category 1
- Forum 1
- Forum 2
- Category 2
- Forum 3
- Forum 4

What I'm not being able to structure inside my head is how am I going to display the different forums inside their own categories. My database structure is as follows:

table: forum_categories
id, category_name, category_rights, category_status

table: forum_category_forums
id, forum_name, forum_description, category_id, category_position, forum_icon, forum_rights

I've tried multiple loops but I still ain't achieving the results I'm looking for. (Damn it, too much time without practising!)

I'm not asking for any code examples, but it'd be nice if you would supply. I'm just looking to clarify myself on how am I going to display the forum with the structure I've planned. Any ideas?

Thanks.

 

[jM2.me]

Should be as simple as this

while ( select categories )
{
    echo category info
    while ( select forums where category id is XX order by position ASC)
    {
        echo forum info and link
    }
}

 

[iceman4154]

Should be as simple as this

while ( select categories )
{
    echo category info
    while ( select forums where category id is XX order by position ASC)
    {
        echo forum info and link
    }
}

That seems correct but order by is set to ascending by default so there is no need for "ASC"

 

[jM2.me]

That seems correct but order by is set to ascending by default so there is no need for "ASC"

Correct, I wrote it just in case

 

[iceman4154]

Oh, lol.

 

[jMerliN]

Do not query like this. What you need can be returned in a single query and then completely consumed instantly and looped through in client code. This will alleviate a lot of delays caused by loop-based querying and will make the SQL server capable of handling a lot more concurrency since a single complex query against a fairly static set of tables can be very well cached (either by the storage engine itself or an intermediary like Memcached).

SELEC'T <cols> FROM categories JOIN forums ON categories.id = forums.category_id ORDER BY <ordercols>

(select keyword purposely broken because vbulletin has serious parsing issues with certain keywords in code blocks)

Consider this as a simple template. You only need a simple cross join because you only want forums that have a category (some assumptions like foreign non-null keys can tell you that this cross will never actually eliminate any results and will speed up execution). This will let you choose any columns you want, and by using ORDER BY you can optimize results to be in an order by category then forum name (or position, if you wish that to be user-specified) allowing you to easily render them in the proper order and group by category. Processing the results in PHP should be quite simple.

 

[Masius]

Do not query like this. What you need can be returned in a single query and then completely consumed instantly and looped through in client code. This will alleviate a lot of delays caused by loop-based querying and will make the SQL server capable of handling a lot more concurrency since a single complex query against a fairly static set of tables can be very well cached (either by the storage engine itself or an intermediary like Memcached).

SELEC'T <cols> FROM categories JOIN forums ON categories.id = forums.category_id ORDER BY <ordercols>

(select keyword purposely broken because vbulletin has serious parsing issues with certain keywords in code blocks)

Consider this as a simple template. You only need a simple cross join because you only want forums that have a category (some assumptions like foreign non-null keys can tell you that this cross will never actually eliminate any results and will speed up execution). This will let you choose any columns you want, and by using ORDER BY you can optimize results to be in an order by category then forum name (or position, if you wish that to be user-specified) allowing you to easily render them in the proper order and group by category. Processing the results in PHP should be quite simple.

I tried both methods presented in this thread, and by far this was the best solution. Thanks, I had never used or worked with JOIN before, it's rather usefull function in MySQL which I'll be using from now on, since it helps alot with perfomance issues.

 

[jMerliN]

I tried both methods presented in this thread, and by far this was the best solution. Thanks, I had never used or worked with JOIN before, it's rather usefull function in MySQL which I'll be using from now on, since it helps alot with perfomance issues.

You should read this:

You must be registered to see links

)

 

[Funshine]

You must be registered to see links

Have you tried viewing other ones and looking at there codes?

Not stealing... Looking.
So its like looking at a diagram and trying to preform it.
if that made any sense.

Noob Speak:
Go there and look at what they do. Try it. Code it differently.