如何从多个表中选择数据并使用PHP显示它？

Question

I'm trying to make a Private Message Application. And I need the Inbox to show both "main messages" and "answers on messages".

My tables are as listed:

post

id - header - content - from_user - to_user - receiver_opened - user_bywho - timestamp.

post_answer

id - answer_from_user - answer_to_user - answer_user_bywho - answer_header - answer_content - answer_id - timestamp_svar.

And now I'm having troubles. Because I don't know how to make this possible.

My mysql_query looks like this right now:

$user = $_SESSION['username'];    

$sql = mysql_query("SELECT * FROM post,post_answer WHERE ".
                  "answer_to_user='$user' AND to_user='$user'")
       or die(mysql_error()); 

Thanks in advance.

Answer 1

The query is not valid as it will be ambiguous about the id field.

USE JOIN

To do this, you can either use JOIN

SELECT * 
    FROM post 
    INNER JOIN post_answer
    WHERE post.to_user = post_answer.answer_to_user

THE STUPID WAY (easy to understand)

SELECT post.id, 
       header, 
       content, 
       from_user, 
       to_user, 
       receiver_opened, 
       user_bywho, timestamp,
       post_content.id, 
       answer_from_user, 
       answer_to_user, 
       answer_user_bywho, 
       answer_header, 
       answer_content, 
       answer_id, 
       timestamp_svar
    FROM post, 
         post_content
    WHERE answer_to_user=$user AND 
          to_user=$user

Hope this helps

UPDATE

Lets suppose your table contains fields like-

1. firstname
2. lastname
3. number

See if you get the result in an array say $results.

foreach ($results as $result)
{
    echo $result->firstname;
    echo $result->lastname;
    echo $result->number;
}