Simple PHP MySQL message client

Question

I am trying to create a simple PHP MySQL driven message client.

So far I have two tables:

users which holds information about each person registered on the site. userID, firstName, lastName etc.

mail which contains a MessageID, fromUserID, toUserID, messageSubject, messageBody , dateSent etc.

Users can register and then log in. Once they are logged in they can view their 'inbox'.

What I am trying to figure out is how to list all the messages, and a preview for each message saying who it is from. Obviously I can get the fromUserID field from the mail table but that doesn't mean anything to the user, it is just a number, I want to use the userID and query the users table and pull from that the sender's first and last name.

I know I can use JOIN but I am not sure how to go about it, or can I use a sub query? What is better?

Any suggestions are very welcome.

KingGeekus · Accepted Answer · 2012-11-21 18:14:05Z

2

You're going to want to join the users table, something like:

SELECT m.*, u.*
FROM mail m
  LEFT JOIN users u ON u.id = m.fromUserID
WHERE m.toUserID == 'LoggedInUserId'

answered Nov 21, 2012 at 18:14

KingGeekus

362 bronze badges

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

3 Comments

Michael Over a year ago

I have tried this, only changing the u.id to u.userID and it threw an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== '1001'' at line 1

. does it have to be ==? Or could it be LIKE?

KingGeekus Over a year ago

Excellent. Joins are nearly always going to be faster than subqueries.

Carlos Vergara Over a year ago

Just a detail: m.toUserID == 'LoggedInUserId' should be m.toUserID = 'LoggedInUserId' (note there's only one =).

Chella · Accepted Answer · 2012-11-21 18:11:42Z

0

Keep the userid in session when you validate the user. then you can get the data using the below query. select mail.* from mail m,users u where u.user_id=m.to_user_id and u.user_id=logged in userid

answered Nov 21, 2012 at 18:11

Chella

1,5624 gold badges23 silver badges43 bronze badges

2 Comments

Michael Over a year ago

I'm not sure if I understand your answer correctly, but I think it is not what I was after. I want to get the details of the sender, not of the person who is logged in.

Chella Over a year ago

I think you din't get me, we need to get not only sender infor but also messages from them. we pull the messages based on to_userid and he is nothing but the person logged in. I think I am clear this time.

Collectives™ on Stack Overflow

Simple PHP MySQL message client

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related