mysql Selecting from two different tables.

Question

$sql = "select body, stamp from posts where user_id = '$userid' order by stamp desc";

NOTE: the above query works fine. What I want to do is also select username from my users table, and display the username that matches the user_id.

I have edited the above statement like so, and it doesnt work. Can someone suggest the correct query? My goal is to also be able to display usernames. in addition to simply displaying user_id.

$sql = "select body, stamp from posts AND username from users where user_id = '$userid' order by stamp desc";

My goal is to also be able to display usernames. instead of simply user_id.

possible duplicate of SQL - Select rows from two different tables — Corbin
– Corbin, Commented Mar 29, 2012 at 20:42

simpleigh · Accepted Answer · 2012-03-29 20:43:39Z

4

You'll need to use a JOIN to bring the two tables together on the matching field, so something like:

$sql = "SELECT p.body, p.stamp, u.username FROM posts p INNER JOIN users u ON p.user_id=u.user_id WHERE p.user_id='$userid' ORDER BY p.stamp DESC";

answered Mar 29, 2012 at 20:43

simpleigh

2,91420 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Estefano Salazar · Accepted Answer · 2012-03-29 20:47:33Z

1

You can use table name to select the columns. Ex:

$Query = "select table1.body, table1.stamp, users.username from posts, users where user_id = '$userid' order by stamp desc";

But, this method is not good in performance.

The best method is:

$Query = "SELECT table1.body, table1.stamp, users.username 
FROM posts 
INNER/LEFT/RIGHT JOIN users 
ON users.user_id = '$userid' AND users.user_stamp = stamp.stamp_id

All the tables must be related.

Greetings,

answered Mar 29, 2012 at 20:47

Estefano Salazar

4194 silver badges16 bronze badges

Comments

Sujit Agarwal · Accepted Answer · 2012-03-29 20:42:09Z

0

$sql = "select body, stamp from posts , username from users where user_id = '$userid' order by stamp desc";

I just corrected the syntactical error in your query... that is the AND keyword should be replaced by ,.

answered Mar 29, 2012 at 20:42

Sujit Agarwal

12.5k11 gold badges50 silver badges79 bronze badges

1 Comment

Michael Berkowski Over a year ago

This will result in a cartesian product, many more rows than expected. You need a join condition between the two tables, and the implicit join syntax (comma-separated tables) is old and scheduled for deprecation.

Norse · Accepted Answer · 2012-03-29 20:44:25Z

0

$sql = "
select body, stamp from posts where user_id = '$userid' order by stamp desc
UNION ALL
select body, stamp from username where user_id = '$userid' order by stamp desc
";

http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Mar 29, 2012 at 20:44

Norse

5,75716 gold badges53 silver badges86 bronze badges

Comments

Jerome Ansia · Accepted Answer · 2012-03-29 20:44:37Z

0

You should use aliases or table name to avoid the duplicate problem like this

tablename.column

or

alias.column

you can set the alias in your where clause :

FROM table as alias_name

answered Mar 29, 2012 at 20:44

Jerome Ansia

6,89211 gold badges55 silver badges102 bronze badges

Comments

steve · Accepted Answer · 2012-03-29 20:45:33Z

0

$sql = "SELECT p.body,p.stamp,u.username from posts p LEFT JOIN users as u ON (p.user_id = u.user_id) WHERE user_id = '$user_id' ORDER BY p.stamp DESC";

should do it

answered Mar 29, 2012 at 20:45

steve

2901 silver badge11 bronze badges

Comments

rs. · Accepted Answer · 2012-03-29 20:47:59Z

0

Try this:

select body, stamp, username 
from posts p JOIN users a ON p.user_id = a.user_id 
WHERE p.user_id = '$userid' order by stamp desc

answered Mar 29, 2012 at 20:47

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Comments

Astarot · Accepted Answer · 2012-03-29 21:18:23Z

0

This should work fine:

SELECT posts.body, posts.stamp, users.username 
FROM posts, users 
WHERE posts.user_id = '$userid' AND posts.user_id = users.user_id
ORDER BY posts.stamp DESC

answered Mar 29, 2012 at 21:18

Astarot

732 silver badges11 bronze badges

Comments

Nikola K. · Accepted Answer · 2012-07-22 12:08:07Z

0

 select body, stamp, username 
    from posts,users 
    where users.user_id = post.user_id 
        and users.user_id = '$userid' 
    order by stamp desc;

edited Jul 22, 2012 at 12:08

Nikola K.

7,15313 gold badges33 silver badges39 bronze badges

answered Mar 29, 2012 at 20:44

Emmanuel N

7,4592 gold badges30 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql Selecting from two different tables.

9 Answers 9

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related