Show Replies and Last Massage in Forum

rahaiffi

hi
i have a prob with my own forum..
i dont know how to show a replies and show name who send last replies..
i have a 2 tables..
topic and replies..

table topic
field:
1.Number (primary key)
2.topic
3.from
4.date

table replies
field:
1.Number(primary key)
2.number_topik
3.replies
4.from
5.date

i want to show data in 1 page

TOPIC | FROM | REPLIES | LAST POST

----------------------------------------------------------------------

to show the topic and from(table topic);

<?php
mysql_select_db($database_repi, $repi);
$query_topic = "SELECT * FROM topic";
$topic = mysql_query($query_topic, $repi) or die(mysql_error());
$row_topic = mysql_fetch_assoc($topic);
$totalRows_topic = mysql_num_rows($topic);
?>

But i dont know how to show from diffrent table in database(table replies)..
how to show with no URL Parameter in same page?
Please help me....

jheitz

Create a WHILE statement so, for each TOPIC you run a few selects to get the number of replies (that will be a count over the replies table for the current topic) and a last_post (that will be a max date from the replies table for the current topic). And then display all the data

rahaiffi

it is the select from replies table?

mysql_select_db($database_repi, $repi);
$query_replies = "SELECT * FROM replies WHERE NUMBER_TOPIC = echo $row_topic['NUMBER']";
$replies = mysql_query($query_replies, $repi) or die(mysql_error());
$row_replies = mysql_fetch_assoc($replies);
$totalRows_replies = mysql_num_rows($replies);

i dont really understand to create a while statement
i try it manytimes ang it doesnt work..
Please tell me a detail one...:-)

Please help me

jheitz

Hi,

You need to read more about PHP and learn a bit more. I am not trying to be condescendent here, but if you can't use WHILE or can't use a variable as part of a string you are going to struggle all the way. Buy a PHP book or read the documentation and check examples.

As per your line:

$query_replies = "SELECT * FROM replies WHERE NUMBER_TOPIC = echo $row_topic['NUMBER']";

This should be done like this:

$query_replies = "SELECT * FROM replies WHERE NUMBER_TOPIC = " . $row_topic['NUMBER'];

Roger_Ramjet

Since you are starting out with all of this you may as well learn how to do SQL properly from the start.

The wrong way is to run multiple queries inside a loop, as suggested here.

The right way is to get all the data from both tables with one query, which in this case is

SELECT t.topic, t.from, COUNT(r.Number) AS count, MAX(r.date) AS last
FROM topics AS t INNER JOIN replies AS r ON t.Number=r.number_topik
GROUP BY t.topic

That will return each topic, it's 'from', the count of replies for that topic, and the date of the last reply.

rahaiffi

thanks for advise

rahaiffi

SELECT t.topic, t.from, COUNT(r.Number) AS count, MAX(r.date) AS last
FROM topics AS t INNER JOIN replies AS r ON t.Number=r.number_topik
GROUP BY t.topic

the code is working
but it just for the topic have a replies..
how about the topic still dont have replies..
help me
im new in php mysql..

jheitz

It looks as if Roger did not get the "right way" correctly after all....

Haven't given too much thought to your query, but sometimes, as already mentioned, it is better to go with 2 or 3 queries that with one that doesn't work

Roger_Ramjet

OK, not knowing your db schema I just assumed that all topics would have an entry in the replies table, so went with an inner join. (actually I assumed that replies could have replies as well)

To join to a table that may or may not have entries you use a left join

 SELECT t.topic, t.from, COUNT(r.Number) AS count, MAX(r.date) AS last
FROM topics AS t LEFT JOIN replies AS r ON t.Number=r.number_topik
GROUP BY t.topic

Now that will list all topics. Those topics that do not have any replies will have NULL in the count and last columns so you test for NULL in your display code and deal with it.

One good thing to come out of this is that you have now seen the practical differences between INNER and LEFT joins.

NB SQL executes 10 - 20 times faster than code in any language on any platform so the right way is always to do it in SQL where possible.
Running 1 query instead of several is so obviously the correct way that I cannot understand anyone who disagrees. The occasions when several queries will execute faster than one are rare, but they do exist. They would be when the single query is extremely complex, involves lots of joins, calculations and aggregations. They are obvious when they do occur because the single query takes too long and so you are looking for a way to speed things up.

jheitz

Hi Roger, I agree with your comment that 1 query is always better and it seems as if in this case it wasn't that complex, but I have seen totally unreadable queries with several sub-selects used to try to get everything in one query. Although it might be faster, sometimes it is better to have readable code that can be understood.... (but again, it wasn't the case on this one it seems)...

dannylewin

Hey,

Im trying to put together a forum, and so far so good... exept when trying to get the last poster...

I have 2 tables, 1 for the Forums, and the 2nd is for posts AND replys:

id_msg
idtop_msg // Forum ID
idmsg_msg // Is NULL if it the FIRST thread, all replys get the value of id_msg
id_init_msg // Gets the value of id_msg (both first post and replys)
idusr_msg // user ID
date_msg
subject_msg
content_msg
subscribe_msg // receive email if its replied to

This is the sql ive tried to get the last post and total posts which does work:

SELECT COUNT(message_msg.id_msg) as total_posts, topic_top.id_top, topic_top.title_top, topic_top.description_top, topic_top.locked_top, DATE_FORMAT(MAX(message_msg.date_msg), '%D %b %Y') as last_post_date, MAX(message_msg.idusr_msg) as last_poster
FROM topic_top LEFT JOIN message_msg ON topic_top.id_top = message_msg.idtop_msg LEFT JOIN members ON message_msg.idusr_msg = members.m_id
WHERE topic_top.parent_top=123456789
GROUP BY topic_top.id_top
ORDER BY topic_top.id_top

In testing it gets the right date, and user ID... but how do i get the username from the members table? I tried taking the last_poster from the SQL above and made another recordset to get the username if message_msg.last_poster = members.m_id but nothing.

Any suggestions?

Thanks in advance,
Danny Lewin

Roger_Ramjet

You probably want to review your design before trying to solve problems that the wrong design creates. Search these forums, or google elsewhere, or read stroing hierarchical data .

Basic principle in programming - Most of your problems have already been solved by people with better brains than yours, so use their solutions.

If you think that the stuff in that article or your searches returns is too complex then that is because they address the problems you have not even realised exist.