CODEWITHSUNDEEP
phpmysqlemailclientmessage
Michael
Michael

Reputation: 4390

Simple PHP MySQL message client

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.

Upvotes: 1

Views: 245

Answers (2)

KingGeekus
KingGeekus

Reputation: 36

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'

Upvotes: 2

Chella
Chella

Reputation: 1562

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

Upvotes: 0

Related Questions