PHP/MySQL - How to retrieve data from one column relative to another

Question

Possible Duplicate:
SQL SELECT across two tables

I want to build a little forum in php and mysql. I have two tables:

forum_posts: id(Primary_Key),user_id(Foreign_Key),text

and

user: id(Primary_key), username

Now I want to list all forum posts and to each post I want to display the author.

My try:

$mySQL_getPosts = mysql_query('SELECT * FROM forum_posts ORDER BY id ASC');

This of course gives me just all posts. But How do I make a connection to my user table?

In the end I want to display a post like this:

Username wrote: | Here goes the text from forum_posts

http://en.wikipedia.org/wiki/Join_%28SQL%29 – Marc B Apr 12 '12 at 18:25 — Marc B, Apr 12 '12 at 18:25

score 2 · Answer 1 · answered Apr 12 '12 at 18:27

2

What you need to do is make a join between the 2 tables. In your case it would be like this:

SELECT f.text, u.username FROM forum_posts f JOIN user u ON (f.user_id = u.id)

Learn more about SQL, especially joins. There are many types of joins and you will stumble accross some of them. If you're trying to build a forum-like website in PHP + MySQL, most queries that you will write will contain at least 1 join.

answered Apr 12 '12 at 18:27

Radu Murzea

10,724
10
47
69

+1 joining is preferred to my answer http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where – Jonathan DS Apr 12 '12 at 18:31

Jonathan DS · Answer 2 · 2019-08-15T12:15:52.213

1

This is an option:

SELECT FP.id,U.Username
FROM forum_posts FP, users U 
WHERE FP.user_id = U.id ORDER BY id ASC

am i missing something?

but joining is better

In MySQL queries, why use join instead of where?

edited Aug 15 '19 at 12:15

answered Apr 12 '12 at 18:26

Jonathan DS

2,050
5
25
48

score 1 · Answer 3 · answered Apr 12 '12 at 18:30

This is where the JOIN command comes in handy. LEFT JOIN will get records from forum_posts even if there is no associated user. If that cannot happen, or if you don't want to post anonymous posts, drop the LEFT part.

SELECT f.*, u.username FROM forum_posts AS f LEFT JOIN user AS u ON f.id = u.id ORDER BY id ASC

score 1 · Answer 4 · answered Apr 12 '12 at 18:34

You can use a basic inner join to link the two tables together, like so:

SELECT username, text 
FROM forum_posts 
INNER JOIN user ON forum_posts.user_id = user.id 
ORDER BY id ASC

Note: It's also better to select only the columns you need from your tables instead of everything.

score 1 · Answer 5 · answered Apr 12 '12 at 18:38

For giving relationship between two tables u have to use mysql joins.

forum_posts: id(Primary_Key),user_id(Foreign_Key),text[Main Table]

user: id(Primary_key), username[connecting this table with the help of main table ids]

$sql_query_string = "Select fp.* from forum_posts as fp
>                      left join user as u on (u.id = fp.user_id)
>                      order by fp.id asc";
> 
> $result = mysql_query($sql_query_string);
> 
> While($data = mysql_fetch_row($result)){
>     echo "<pre>";print_r($result);"</pre>"; }

This query will helpful for u to display the author

PHP/MySQL - How to retrieve data from one column relative to another

5 Answers5