[SOLVED] JOIN QUERY HELP

[rubadub]

To start, I have two tables.

 

user_tbl (table name)

user_id      username          password        email

1                rubadub            blah              blah

2                billy                blah              blah

 

wall_tbl (table name)

wall_id      user_id      poster_id      message      time      date

1                  1                2            blah            x          x

2                  1                2            blah            x          x

3                  2                1            blah            x          x

 

 

NOW, what i'm trying to do is display the information like this.

When I goto rubadubs profile it shows all the post he has total (in this case, billy posted on rubadubs wall twice--- so it would need to display both posts)

 

I HAVE NO PROBLEM DISPLAYING THE POST

THE PROBLEM I'M HAVING IS TAKING THE user_id AND poster_id from wall_tbl and converting them to the actual USERNAME from the user_tbl.

 

In other words.

 

RUBADUB (1 [user_id]) has a message from BILLY (2 [poster_id]) with a message of blah

 

So in my mind I was thinking that I need to take "user_id" and match it with user_tbl "user_id" to retrieve the username. And i'd have to do the same thing with "poster_id" and match it up with the user_tbl "user_id" to get the username of the poster.

 

Don't get me wrong, I can probably do it with a few different queries and checks just comparing the two id's but I was just reading up on "JOIN" and it looks a lot more efficient however I can't get it to work right. I'm so confused when it comes to JOIN.

 

Anyone have any idea or information about what i'm trying to do or how to go about solving it?

[gevans]

If you're looking at someones profile I'd imagine you'd already have access to their user name, so there would be no need to get the user_id, you would only need the poster_id for each post, which would be a very simple query.

[rubadub]

If you're looking at someones profile I'd imagine you'd already have access to their user name, so there would be no need to get the user_id, you would only need the poster_id for each post, which would be a very simple query.

How is this supposed to be helpful?

 

I want to mesh the two tables so that I can retrieve both username and postername (username) in one table. Is this possible? If so, can you please point me in the direction instead of telling me "oh, it's simple". If it was so simple I wouldn't be posting here. I'm obviously confused and frustrated.

[gevans]

Calm yourself.

 

If you read what I said I was speculating...

 

Answer me this. If you're looking at someone's profile do you already have that user who's profile you're looking at in a variable?

[rubadub]

Calm yourself.

 

If you read what I said I was speculating...

 

Answer me this. If you're looking at someone's profile do you already have that user who's profile you're looking at in a variable?

Sorry, just frustrated.

 

$_GET['userid'] would get his userid and not his username.

I could then however do a query to search a WHERE user_id=$_GET[userid] to retrieve his username. Yes.

 

However, i'm looking to mesh the tables and learn the "Join" function.

[gevans]

I'd probably do something like this to return the messages and the usernames of the users that psoted the message;

 

SELECT `u`.`username`, `w`.`message`
FROM `user_tbl` AS u JOIN `wall_tbl` AS w
ON `w`.`poster_id` = `u`.`user_id`
WHERE `u`.`user_id` = $_GET['userid']

 

And then use a seperate query to get the username of the profile owner.

 

This will also give you access to the profile owners username across the profile without worrying about the wall.

[o3d]

select 
b.username as sender,
c.username as receiver,
a.message,
a.time,
a.date
FROM 
wall_tbl a JOIN 
user_tbl b on b.user_id = a.user_id JOIN 
user_tbl c on c.user_id = a.user_id
WHERE 
c.user_id = $_GET['userid']

 

that should return the sender's name, receiver's name and the message details for the user that messages are sent to

[rubadub]

I'd probably do something like this to return the messages and the usernames of the users that psoted the message;

 

SELECT `u`.`username`, `w`.`message`
FROM `user_tbl` AS u JOIN `wall_tbl` AS w
ON `w`.`poster_id` = `u`.`user_id`
WHERE `u`.`user_id` = $_GET['userid']

 

And then use a seperate query to get the username of the profile owner.

 

This will also give you access to the profile owners username across the profile without worrying about the wall.

 

Thanks for the help,

 

...but can you please explain to me what all that means?

What is the "u" and "w" during your select? I don't understand that and why it works. That's what i'm moreso interested in so that I can handle the situation myself, in the future.

[gevans]

SELECT `u`.`username`, `w`.`message`

 

I'll explain `u` and `w` in a second... All we're doing here is saying that we want to select the username and the message.

 

 

FROM `user_tbl` AS u JOIN `wall_tbl` AS w

 

We want to select those details from `user_tbl` and `wall_tbl`. The 'AS u' and 'AS w' parts means that `u` can be used in the rest of the query to reference `user_tbl` and `w` can be used in the rest of the query to reference `wall_tbl`. So `u`.`username` is the same as `user_tbl`.`username`, and `w`.`message` is the same as `wall_tbl`.`message`. The join is used to associate the tables.

 

 

ON `w`.`poster_id` = `u`.`user_id`

 

When using the JOIN syntax you must use ON in place of WHERE (WHERE can additionally be used afterwards). The conditions are that the `poster_id` is equal to the `user_id`. That allows us to get the poster's username.

 

 

WHERE `w`.`user_id` = $_GET['userid']

 

The final part, the WHERE clause is just to refine the search to the user you wanted. So all of the above is done for rows in `wall_tbl` where the `user_id` is equal to $_GET['userid'].

[rubadub]

SELECT `u`.`username`, `w`.`message`

 

I'll explain `u` and `w` in a second... All we're doing here is saying that we want to select the username and the message.

 

 

FROM `user_tbl` AS u JOIN `wall_tbl` AS w

 

We want to select those details from `user_tbl` and `wall_tbl`. The 'AS u' and 'AS w' parts means that `u` can be used in the rest of the query to reference `user_tbl` and `w` can be used in the rest of the query to reference `wall_tbl`. So `u`.`username` is the same as `user_tbl`.`username`, and `w`.`message` is the same as `wall_tbl`.`message`. The join is used to associate the tables.

 

 

ON `w`.`poster_id` = `u`.`user_id`

 

When using the JOIN syntax you must use ON in place of WHERE (WHERE can additionally be used afterwards). The conditions are that the `poster_id` is equal to the `user_id`. That allows us to get the poster's username.

 

 

WHERE `w`.`user_id` = $_GET['userid']

 

The final part, the WHERE clause is just to refine the search to the user you wanted. So all of the above is done for rows in `wall_tbl` where the `user_id` is equal to $_GET['userid'].

 

THANK YOU!!!