fetching chat messages from mysql

Question

I am fetching the chat messages between two users and want to display the messages in two different color.

<?php
    session_start();  
?>
<html>
    <head></head>
    <body>
        <?php
            require("config.php");
            $id = $_POST['id'];
            $sql = "select * from users where id='$id'";
            $res = mysqli_query($con, $sql);
            $row = mysqli_fetch_array($res);
            $user_to = $row['name'];
            $sql1 = "select * from chats where user_from='$_SESSION[name]' AND user_to='$user_to' OR user_to='$_SESSION[name]' AND user_from='$user_to' order by id";
            $res1 = mysqli_query($con, $sql1);
            if(mysqli_num_rows($res1) > 0) {
                while($row = mysqli_fetch_array($res1)) {
                    if($row['user_from'] == $_SESSION['name'])
                        $color = 'red';
                    else
                        $color = 'purple';
                    echo '<i><p style="font-family:arial;color:' . $color . ';font-size:15px;">' . $row['msg'] . '</p>';
                }
            } else
                echo "No msgs <br />";
        ?>
    </body>
</html>

for example the user who is logged in , his messages should be in red and whose messages he is checking should be in purple. The trouble is that all the fetched messages is getting red.

[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a class for](https://github.com/GrumpyCrouton/GrumpyPDO) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](https://phpdelusions.net/pdo/mysqli_comparison) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Nov 10 '17 at 16:12
What does the contents of `$row['user_from']` look like? I assume it's a userid, not a name. If not, it should be. — GrumpyCrouton, Nov 10 '17 at 16:15
@grumpy The user_ from is the name of the user who is currently logged in — max rox, Nov 10 '17 at 16:16
I am helping you with this. Security should always be a main concern with any code you write. As it stands, you have a huge injection hole that any attacker WILL find, and WILL abuse. — GrumpyCrouton, Nov 10 '17 at 16:17
If `$row['user_from']` is _always_ the user who is logged in, and not the user who actually sent the message, then there is your problem. — GrumpyCrouton, Nov 10 '17 at 16:17
How about making `$row['user_from']` contain the userid of the user who sent the message, as the variable key would suggest? — GrumpyCrouton, Nov 10 '17 at 16:21
Look [pattyd's answer](https://stackoverflow.com/a/47227103/5827005), they basically explained what I was trying to say. — GrumpyCrouton, Nov 10 '17 at 16:25
But how do I will pass the id of the sender.I mean when the logged in user click on another username the I get The id of the clicked name and pass it here,but how to pass the id of the sender — max rox, Nov 10 '17 at 16:31

pattyd · Answer 1 · 2017-11-10T16:47:52.727

The issue here is in your logic with $sql1 where you say select * from chats where user_from='$_SESSION[name]' AND.....

You then do this check:

if($row['user_from'] == $_SESSION['name'])
    $color = 'red';

That check will always be true because you literally just asked mysql to give you all the chats where user_from is the logged in user, and now you are telling the program to set the color to red if the user_from is the logged in user, which it will always be, since that is what you asked for.

You should restructure your if statement which handles the colors, because right now your logic does not make any sense for what you are trying to do.

Edit:

What I don't understand is how you are getting any messages sent to the user, because right now your sql query is just looking at messages from the user (or so it would seem). If I am correct in assuming that, I would suggest that you restructure your $sql1 query to get all of the messages to the logged in user, and from the logged in user which are in communication with $user_to, because I'm also assuming you just want to focus on that specific thread between those two users.

Let's assume with this new query, you have two users chatting:

User1 is logged in on their end, trying to chat with user2. When they insert a chat into the database, user_from = user1 and user_to = user2
User2 is logged in on their end, chatting with user1. When they insert a chat, user_from = user2 and user_to = user1

Since you want both of these users to see the messages in the thread, you want a query that finds messages from user1 to user2, and from user2 to user1.

You should make the following assumptions in order to create the correct query:

user_from needs to be the logged in user or the $user_to user (the one they are chatting with), since those are the two users in
the thread)
user_to also needs to be one of these two users, since they are the only ones in the thread.

You could get messages in this thread with a query similar to this: select * from chats where user_from='$_SESSION[name]' OR user_from='$user_to' AND user_to='$_SESSION[name]' OR user_to='$user_to'

If my assumptions of your program are correct, you can integrate my logic and that query or one very similar to it should work well for what you are trying to accomplish, so long as all your other code is correct.

Once you are correctly retrieving messages between the two users, you should integrate the message color just like you had in your question, since your sql query is what is messing up the logic in your if statement.

fetching chat messages from mysql

1 Answers1