PHP/mysql, Using foreign keys to get data from another table

Question

I have 2 tables, one called posts and one called users. Inside posts I have several columns, one being a foreign key of userid. In my users table I have several columns including a userid and a username. I want to use the posts.userid to find the users.userid and show users.username. Currently I am using:

SELECT posts.postid, posts.title, posts.text, posts.link, posts.good, posts.bad, posts.userid
FROM posts

To get the data about each post and,

SELECT users.username, users.userid, posts.userid
FROM users, posts
WHERE users.userid = posts.userid

to get the data about each user, but when I do this the output of echo $row_therealuserid['username']; is always just the first user.

@PaulP.R.O. I'm using dreamweaver to add bindings and that is what it used. — Aaron Robeson
– Aaron Robeson, Commented May 7, 2012 at 15:59

Sandeep Bansal · Accepted Answer · 2012-05-07 15:53:22Z

9

You need to use an array to fetch the other rows, then loop through that array.

$query = 'SELECT users.username, users.userid, posts.userid
FROM users, posts
WHERE users.userid = posts.userid';

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['username'];
    echo "<br />";
}

answered May 7, 2012 at 15:53

Sandeep Bansal

6,44018 gold badges88 silver badges129 bronze badges

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

1 Comment

Aaron Robeson Over a year ago

Thanks, this looks like it'll work. I had to leave for Chemistry class right after posing this question, I should be back at my computer to test it in a couple of hours.

interface · Accepted Answer · 2012-05-07 17:33:33Z

0

For SELECT and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

A resource is a special variable, holding a reference to an external resource. Resources are created and used by special functions, and it can has many types created and used by different functions.

for mysql_query() the returned type a resource is "mysql result" which will be used by mysql_fetch_array();

I will give you another example, for the function mysql_connect() it returns a resource type "mysql link" which can be used by the function mysql_query()

answered May 7, 2012 at 17:33

interface

355 bronze badges

Collectives™ on Stack Overflow

PHP/mysql, Using foreign keys to get data from another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related