Page 1 of 1

[HELP] Join 3 Table, and output based on category and topic.

Posted: Thu Jul 09, 2009 11:50 am
by joe_black
I tried to make a small forum, using php and mysql. But I have problems with the output. It seem`s the output was mess. This is my query+script and the database:
DataBase:

tbl>1 posting_forum

id_post
id_category
id_topic
title_posting
post
--------------------------
tbl>2 category_forum

id_category
title_category
-------------------------
tbl>3 topic_forum

id_topic
title_topic
intro

-------------------------------------------------------------------------------
$sql=mysql_query("SELECT * FROM posting_forum
LEFT JOIN topic_forum ON posting_forum.id_topic = topic_forum.id_topic
LEFT JOIN category_forum ON posting_forum.id_category = category_forum.id_category
WHERE topic_forum.id_topic=posting_forum.id_post
ORDER BY id_post");

$x=mysql_fetch_array($sql);

echo "Topic: $x[title_topic]";
echo "<br><br>";
echo "Category: $x[title_category]";
echo "<br><br>";
echo "Title: $x[title_posting]";
The right output that I want is like this:
>> Category: Music
--------------------

>> Topic: Rock Music

>> New Post: Do you know the chord of Blackholesun?

****************************************************************************

>> Topic: Pop Music

>> New Post: I`ll miss the king of pop...

============================================================================

>> Category: IT
--------------------

>> Topic: Hardware

>> New Post: Do you know how to fix a mother board?

****************************************************************************

>> Topic: Software

>> New Post: What is web 2.0?

============================================================================
All I want is the output is more like this forum(phbb). I wanna shows the category, topic and the new post from the database. But now all I can is show all the category and show all topic and show all the latest and new post, everything showed up on the browser. I was using "coalesce" script in query, but the result in get more confusing, the category and the topic showed up but some category shows the newest post but the other category shows the latest post......

Please help me.....I tried everything in a whole week...I tried googling but...none on the result...please help...

Thanks...

Re: [HELP] Join 3 Table, and output based on category and topic.

Posted: Thu Jul 09, 2009 12:02 pm
by Christopher
I don't think you don't need your WHERE statement ("WHERE topic_forum.id_topic=posting_forum.id_post") because that is already in the JOIN ON. The WHERE should constrain the results to dates after a certain date, or ORDER BY post date DESC and then LIMIT the results, etc.

Re: [HELP] Join 3 Table, and output based on category and topic.

Posted: Thu Jul 09, 2009 1:42 pm
by joe_black
arborint wrote:I don't think you don't need your WHERE statement ("WHERE topic_forum.id_topic=posting_forum.id_post") because that is already in the JOIN ON. The WHERE should constrain the results to dates after a certain date, or ORDER BY post date DESC and then LIMIT the results, etc.

Thats why I was using "where" clause, becuse if I get rid of it, all data in the database will shows on the browser. But I just want to show some topic based on the category. Because in one category there are more than one topic, so I just want to show only topic with the newest post message /category.(Like just in the schema up there...[first post]). Can you help?

Thanks...

Re: [HELP] Join 3 Table, and output based on category and topic.

Posted: Thu Jul 09, 2009 1:51 pm
by VladSun
I wanna shows the category, topic and the new post from the database.
!=[sql]SELECT * FROM posting_forumLEFT JOIN topic_forum ON posting_forum.id_topic = topic_forum.id_topicLEFT JOIN category_forum ON posting_forum.id_category = category_forum.id_category[/sql]

You need to use a RIGHT JOIN *OR* you need to inverse the join statements starting from category, going to topics and finally to posts, by using a LEFT JOIN

Re: [HELP] Join 3 Table, and output based on category and topic.

Posted: Thu Jul 09, 2009 4:45 pm
by joe_black
You need to use a RIGHT JOIN *OR* you need to inverse the join statements starting from category, going to topics and finally to posts, by using a LEFT JOIN
Yes I did, I tried before...let me explain more details. There are some topics in posting_forum, example:[topic a, topic b, topic c]. The topic is made by administrator(The category also). In topic a there are post message from the visitor and so in others topic. So what I want to show is:

Category: A
Topic: a
New Post: [Newest post title]

Category: A
Topic: b
New Post: [Newest post title]

** If we look the output up there, there are different topic in the same category. Actually that`s all I wanna do... :)