multiple where clause in sql

Question

I don't know if this is possible but i will give this a try.

I have 3 tables:

First table: (sender_id and receiver_id is a foreign key referencing to loginportal table)

messages
sender_id | receiver_id | message
    3     |      1      |   ...

Second Table: (req_id is a foreign key referencing request table)

loginportal
loginPortal_id | username | req_id
     1         |   admin  |    1
     3         |    user  |    2

Third table:

request
req_id | firstname | surname
   1   |    john   |   doe
   2   |    jane   |   me

Problem:

Whenever i used this query:

"SELECT id, message_sender_id, 
        message_title, message_body, sent_date, message_status,
        username, firstname, surname
        FROM messages m
        INNER JOIN loginportal l
        INNER JOIN request r
        ON m.message_receiver_id= l.loginPortal_id
        AND l.req_id=r.req_id
        WHERE m.message_receiver_id=(
        SELECT loginPortal_id FROM loginportal 
        WHERE username='".$_SESSION['user']."')";

What i got is the receiver username and receiver firstname and surname. What i want is i will have the receiver username but SENDER's firstname and surname. is this possible?

You don't want to accept arbitrary user input in your SQL Query. In your case, you really don't want a user that has the name `--; DROP TABLE USERS;`, do you? Use PHP best practices; and, parameterize your queries: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — George Stocker, Mar 16 '15 at 20:03

scubasteve623 · Answer 1 · 2015-03-16T20:56:33.927

I think you need to add a message_id field, then combine two subqueries finding the sender and reciever info on their new message_id. Something along these lines...haven't tested it

SELECT query1.username, query2.firstname, query2.surname

FROM (SELECT username, message_id FROM loginportal, messages WHERE loginPortal_id = receiver_id) AS query1,
(SELECT firstname, surname, message_id FROM request, loginportal, messages WHERE request.req_id = loginportal_req.id AND loginportal.loginPortal_id = messages.sender_id) AS query2

WHERE query1.message_id = query2.message_id;

multiple where clause in sql

1 Answers1