-1

I want to create a messaging system, here is my table:

(customers) userid int 11 primary key auto increment, username varchar 255, password varchar 255

(messages) mid int 11 primary key auto increment, from_id index customer.userid, to_id index customer.userid, message text

My question is if my session userid is 11 how to display all the messages that are sent to my id?

What is the right code to use?

$stmt = $con->prepare("SELECT 
                                       message.*, customer.username AS from_id
                                  FROM 
                                        message
                                  INNER JOIN
                                        customer
                                  ON
                                        customer.userid = message.from_id
                                  ");

        $stmt->execute();

        $rows = $stmt->fetchAll();
Gherbi Hicham
  • 2,416
  • 4
  • 26
  • 41
ace francis eli
  • 31
  • 5
  • You probably want to tag this as SQL as the question is really about the query. It looks like you have done the join correctly but you have not added a filter. What you probably need to do is add WHERE customer.userid = 11 at the end. – Cobusve Oct 09 '16 at 21:56
  • How about the other userid for example i log in using anothet userid how to display all the messages that is sent to that userid – ace francis eli Oct 10 '16 at 03:47
  • The quick and dirty way is to construct the query in a string like you did there, the better way is to use a prepared statement with a parameter to avoid SQL injection vulnerabilities . You could add to the end of your string there . "where message.userid = " . "11"; and replace 11 with whatever your id is. You did not supply enough code for me to use your actual variables. – Cobusve Oct 10 '16 at 05:42

1 Answers1

0

What you describe you need is a "Where" clause in your query. Adding "where userid = 11" at the end will do exactly what you asked in the question above.

If the userid is a parameter you should look at this PAQ for how to do that, because that is a different question.

How can I prevent SQL injection in PHP?

Community
  • 1
  • 1
Cobusve
  • 1,572
  • 10
  • 23