PHP chat problem

[Micard]

Hey there! So I've written a bundle php+mysql for a very simple chat system. I use it as a script to another application so don't be surprised when you don't see any sessions and stuff like that, the thing is that the script will only be run if the authentication in the app has been completed, so I'm not worried about that. Oh and yeah, the app uses AJAX so that's how I retrieve the data (AJAX.LastData) - it basically gets the echo-ing content.

 

What's going on is users cannot see each other's messages. They obviously can see their own messages. In my app I update the chatbox every tick.

 

I have a `chat` table in my db and it has the following columns: pk_Id (primary key, unique), Nick (varchar 20), Message (varchar 140), and TimeSent (varchar 20);

 

So here's the script itself:

<?php
header('Access-Control-Allow-Origin: *');
 
$mysql_host = "some mumbo-jumba";
$mysql_database = "some mumbo-jumba";
$mysql_user = "some mumbo-jumba";
$mysql_password = "some mumbo-jumba";
 
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password) or die("Error: SQL query generic fault!" );
mysql_select_db($mysql_database, $link) or die ('Error: Cannot connect to Database!');


if(isset($_POST['username'])) $nick = mysql_real_escape_string($_POST['username']);
if(isset($_POST['message'])) $message = mysql_real_escape_string($_POST['message']);


$q11 = "INSERT INTO `chat` SET `Nick`='{$nick}', `Message`='{$message}', `TimeSent`=CURTIME()";
$result = mysql_query($q11, $link);


$chat_thingie = mysql_query("SELECT * FROM `chat` ORDER BY pk_Id DESC LIMIT 1");
$result3 = mysql_fetch_array($chat_thingie);

/*
$timesent = mysql_query("SELECT `TimeSent` FROM `chat` WHERE `Nick`='{$nick}' AND `Message`='{$message}'");
$result1 = mysql_fetch_array($timesent);
$Nick_rec = mysql_query("SELECT `Nick` FROM `chat` WHERE `Nick`='{$nick}' AND `Message`='{$message}'");
$result2 = mysql_fetch_array($Nick_rec);
$Message_rec = mysql_query("SELECT `Message` FROM `chat` WHERE `Nick`='{$nick}' AND `Message`='{$message}'");
$result3 = mysql_fetch_array($Message_rec);
*/

$time=$result3['TimeSent'];
$nick_chat=$result3['Nick'];
$message_chat=$result3['Message'];

echo $time . " " . $nick_chat . ": " . $message_chat;
?>

I know there are some redundant variables but again, this is not my primary concern at this point. Thank you in advance!

Edited July 21, 2015 by Micard

[Micard]

I retrieve the whole table content to a message, I am not sure why the users still can't see messages.

[Micard]

Whoops. I just noticed what I said in the reply above. What I meant to say was: I retrieve the last data recorded in the table and try to display it column by column, so I'm not sure why it doesn't work like that and as I said in the application it updates the chatbox (placeholder for chat messages)  every tick. The thing is also that all the messages ACTUALLY go into the database. The "retrievation" method is wrong and I am not sure why.

Edited July 21, 2015 by Micard

[Ch0cu3r]

The PHP code you posted, looks fine. It will echo the last message that was just inserted, although the select query is probably not needed, you could just echo out the message that was just submitted.

 

Seem like the problem is to do with your javascript code. What is the "retrievation" method?

[scootstah]

You're inserting a message and then selecting the message that you just inserted. Thus, you're only ever going to see the message that you just sent.

 

You should have separate scripts for reading and writing, and you should be selecting more than one row. Probably something like, fetch all messages since the last time I checked.

 

Also, this is a really inefficient way to make a chat. Hopefully you have a low number of users. A chat should be a persistent TCP connection to push messages as they come in, rather than infinitely polling whether there are new messages or not.

[Micard]

Thanks for the replies!

 

Chocu3r, basically on the completion of the event I just set text of the chatbox like this: "chatbox.Text + AJAX.LastData" <- pseudo-code

 

scootah, I am not sure how I am pulling only the one I posted out. See, the chatbox is being updated automatically every 0.1 seconds. If any new messages were added, shouldn't they be shown? And you're right, this is super ineffective as a lot of other things in Construct 2. Unfortunately this is how it works. I will try to switch to sockets but AJAX is just so much easier even though it's slower. 

Edited July 21, 2015 by Micard

[Micard]

You should have separate scripts for reading and writing, and you should be selecting more than one row. Probably something like, fetch all messages since the last time I checked.

 

 

Could I have a query example please? 

[scootstah]

Because you INSERT and then immediately SELECT the newest message. Obviously the newest message will be the one that you just added.

 

createMessage.php

if (!empty($_POST)) {
    $username = trim($_POST['username']);
    $message = trim($_POST['message']);

    if (!empty($username) && !empty($message)) {
        // insert into database
    }
}

getMessages.php

 

$query = mysql_query("SELECT * FROM `chat`");
$messages = array();
while ($row = mysql_fetch_assoc($query)) {
    $messages[] = $row;
}

echo json_encode($messages);

Not complete code but should give you the idea. Notice that I am returning JSON from getMessages.php; which you should be doing for simplicity's sake when dealing with AJAX. Let Javascript format the output onto the screen.

 

getMessages.php will currently return all chat messages in the database, which you probably don't want, as that's going to get crazy when you have a lot of messages. An improvement would be to add a "since" parameter in your AJAX call, and then you only fetch records that were created after that time.

 

Also, you're going to definitely run into problems calling your script every 100ms. You'd be lucky to even get that kind of response time from your average server. I'd probably poll every 1 seconds max.

Edited July 21, 2015 by scootstah

[Micard]

Alrighty then! That's a very good code example! Thanks a lot!