timestamp message system

Hello,

I'm putting the message system in a game, but having trouble matching time stamps and runnign the queries. Basically I have a couple of tables. One that holds a timestamp for the character. The other holds the messages. The character's timestamp gets updated so old messages don't keep coming out. Right now I'm getting the same messages all the time, no matter how old they are.

Here's data from my actual tables:
character
1491806607

messages
1491799854
1491800178

It makes sense that I'm getting the same messages repeated. The character stamp is always newer than the message stamps... So what's the right way to set this up?

I wasn't certain about this query, but it seems it's doing the right thing.

'SELECT `channel`, `character`, `message`, `icon`, `token` FROM `world_messages_36933753` WHERE `roomToken` = ? AND `channel` = ? OR `channel` = ? AND `stamp` > ?'

Code (markup):

It's a bit funny.

Also the part where I update the stamp if it's old, before fetching anything.

 $sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
        $sqlStamp->execute(array($character->return_name()));
        $stamp = $sqlStamp->fetch();
    
        if($time->minutes_passed($stamp['stamp']) >= 2)
        {
        
            // update the stamp
        
            $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
            $sqlUpdate->execute(array(time(), $character->return_name()));
        
        }

Code (markup):

header('Content-Type: application/json');
session_start();

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
     
        include('../classes/Character.php');
        include('../classes/TimeObject.php');
        include(__DIR__ . '/../../../sqldata/sqldata.php');
     
        try{
         
            $db = new PDO($dsn, $dbUsername, $dbPassword);
         
        }catch(\PDOException $e){}
     
        $character = new Character;
        $character->set_db($db);
        $character->set_character();
        $character->set_location();
     
        $time = new TimeObject;

        $sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
        $sqlStamp->execute(array($character->return_name()));
        $stamp = $sqlStamp->fetch();
     
        if($time->minutes_passed($stamp['stamp']) >= 2)
        {
         
            // update the stamp
         
            $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
            $sqlUpdate->execute(array(time(), $character->return_name()));
         
        }else{
         
            // fetch all messages to the character, including OOC message if OOC is on
                // Fetch where messages stamp is newer than current character_message_stamps stamp
         
            $sqlFetch = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `token` FROM `world_messages_36933753` WHERE `roomToken` = ? AND `channel` = ? OR `channel` = ? AND `stamp` > ?');
            $sqlFetch->execute(array($character->return_room_token(), 'all', $character->return_name(), $stamp['stamp']));
            $messages = $sqlFetch->fetchAll();
         
         
            if($_POST['ocObj'] == 'on'){
                    // fetch occ too
                $sqlFetchOC = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `token` FROM `world_messages_36933753` WHERE `channel` = ? AND `stamp` > ?');
                $sqlFetchOC->execute(array('oc', $stamp['stamp']));
                $ocMessages = $sqlFetchOC->fetchAll();
                // splice this to other messages
             
                $messageList = array_merge($messages, $ocMessages);
             
            }else{
             
                $messageList = $messages;
             
            }
         
            // Reset the stamp to current
         
            $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
            $sqlUpdate->execute(array(time(), $character->return_name()));
         
        }
     
        echo json_encode($messageList);
     
}

Code (markup):

 

Okay,

I've done some editing. I've changed the character time stamp at the end to match the last timestamp for the hauled out messages, but there's a bit of a difference for some reason. I get messages with this query, but all the old ones. If I change the equality sign in the query I get no messages.

room: 50a80e41ccdba416b3b28390
character: Ragnok
character stamp: 1491816864

1491816588
1491816728
1491816779
1491816834 <-- last message stamp

<?php


header('Content-Type: application/json');
session_start();

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
   
     include('../classes/Character.php');
     include('../classes/TimeObject.php');
     include(__DIR__ . '/../../../sqldata/sqldata.php');
   
     try{
     
       $db = new PDO($dsn, $dbUsername, $dbPassword);
     
     }catch(\PDOException $e){}
   
     $character = new Character;
     $character->set_db($db);
     $character->set_character();
     $character->set_location();
   
     $time = new TimeObject;

     $sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
     $sqlStamp->execute(array($character->return_name()));
     $stamp = $sqlStamp->fetch();
   
     if($time->minutes_passed($stamp['stamp']) >= 2)
     {
     
       // update the stamp
     
       $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
       $sqlUpdate->execute(array(time(), $character->return_name()));
     
     }else{
     
       // fetch all messages to the character, including OC message if OC is on
         // Fetch where messages stamp is newer than current character_message_stamps stamp
     
       // fetch messages to character
     
       $sqlFetchMessages = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                        WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                         UNION
                         SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                         WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                         UNION
                         SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                         WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?');
                       
       $sqlFetchMessages->execute(array($character->return_room_token(),
                       $character->return_name(),
                       $stamp['stamp'],
                       $character->return_room_token(),
                       'all',
                       $stamp['stamp'],
                       $character->return_room_token(),
                       'system',
                       $stamp['stamp']));
                       
      $messages = $sqlFetchMessages->fetchAll();
 
       // Fetch OC messages
       if($_POST['ocObj'] == 'on'){
         
         $sqlFetchOC = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753` WHERE `channel` = ? AND `stamp` > ?');
         $sqlFetchOC->execute(array('oc', $stamp['stamp']));
         $ocMessages = $sqlFetchOC->fetchAll();
       
         // splice this to other messages
       
         $messageList = array_merge($messages, $ocMessages);
       
       }else{
       
         $messageList = $messages;
       
       }
     
       $count = count($messageList);
           
       // Reset the stamp to current
     
       $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
       $sqlUpdate->execute(array($messageList[$count - 1]['stamp'], $character->return_name()));
     
     }
   
     echo json_encode($messageList);


?>

Code (markup):

 

If you only want the last message at any time, why don't you just do something like SELECT MAX(timestamp)?

 

It's a chat system for a game that runs like free-form role playing with mud elements. It's a cool blend. The only problem with your suggestion is I need every message after the last message gathered. So I need messages with a time stamp bigger than the last fetched message.

I'll explain the message table:

channel - Recipient character's name, all (all in room will see it), system (this is from the system), or oc (out of character)
character - character sending the message.
message - the chat text
icon - dice for skill checks, magical eye for spell casting, ect
stamp - the time stamp the message was sent
token - unique token.

Here's the new code, with a bit of a break down.

When the game starts I run this code, which gives the character a time stamp if they haven't got one, or updates their old one. It's run on document load:

<?php


session_start();

include(__DIR__ . '/../../../sqldata/sqldata.php');
include_once('../classes/Character.php');
include_once('../classes/TimeObject.php');

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{

    try{
    
        $db = new PDO($dsn, $dbUsername, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    
    }catch(\PDOException $e){}

    $character = new Character;
    $character->set_db($db);
    $character->set_character();

    // SELECT LAST STAMP

    $sqlSelect = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
    $sqlSelect->execute(array($character->return_name()));
    $fetchedStamp = $sqlSelect->fetch();

    if(empty($fetchedStamp))
    {
    
        // Create first stamp
        $sqlInsertStamp = $db->prepare('INSERT INTO `characters_message_stamps_36933753` (`character`, `stamp`) VALUES (?, ?)');
        $sqlInsertStamp->execute(array($character->return_name(), time()));
    
    }else{
    
        // update stamp
        $sqlInsertStamp = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
        $sqlInsertStamp->execute(array(time(), $character->return_name()));
    
    }

}

?>

Code (markup):

This is the code that runs on game loop, fetching messages.

First gather that time stamp set on document load:

$sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
        $sqlStamp->execute(array($character->return_name()));
        $stamp = $sqlStamp->fetch();

Code (markup):

Then fetch all messages from the basic channels:

$sqlFetchMessages = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                               WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?');
                                           
            $sqlFetchMessages->execute(array($character->return_room_token(),
                                            $character->return_name(),
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'all',
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'system',
                                            $stamp['stamp']));
                                           
            $messages = $sqlFetchMessages->fetchAll();
            

Code (markup):

That query works, because I tested it. Also these values in execute hold proper data. I landed on the script with some var_dumps.

Select out of character messages if OC channel is on, and merge everything into a new messageList array.

    if($_POST['ocObj'] == 'on'){
               
                $sqlFetchOC = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753` WHERE `channel` = ? AND `stamp` > ?');
                $sqlFetchOC->execute(array('oc', $stamp['stamp']));
                $ocMessages = $sqlFetchOC->fetchAll();
           
                // splice this to other messages
           
                $messageList = array_merge($messages, $ocMessages);
           
            }else{
           
                $messageList = $messages;
           
            }

Code (markup):

And finally update the character stamp in the other table with the stamp of last fetched message. This seems to be one of the fishy parts. It never matches the last fetched stamp. It's off by fractions of a second, which makes no sense.

 // Reset the stamp to last message stamp if stamp doesn't already =  last message stamp
            $count = count($messageList);
            $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
            $sqlUpdate->execute(array($messageList[$count - 1]['stamp'], $character->return_name()));
       
            echo json_encode($messageList);

Code (markup):

Full code below.

header('Content-Type: application/json');
session_start();

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
    
        include('../classes/Character.php');
        include('../classes/TimeObject.php');
        include(__DIR__ . '/../../../sqldata/sqldata.php');
    
        try{
        
            $db = new PDO($dsn, $dbUsername, $dbPassword);
        
        }catch(\PDOException $e){}
    
        $character = new Character;
        $character->set_db($db);
        $character->set_character();
        $character->set_location();
    
        $sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
        $sqlStamp->execute(array($character->return_name()));
        $stamp = $sqlStamp->fetch();
    
            // fetch all messages to the character, including OC message if OC is on
                // Fetch where messages stamp is newer than current character_message_stamps stamp
        
            // fetch messages to character
        
            $sqlFetchMessages = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                               WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?');
                                            
            $sqlFetchMessages->execute(array($character->return_room_token(),
                                            $character->return_name(),
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'all',
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'system',
                                            $stamp['stamp']));
                                            
            $messages = $sqlFetchMessages->fetchAll();
        

            // Fetch OC messages
            if($_POST['ocObj'] == 'on'){
                
                $sqlFetchOC = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753` WHERE `channel` = ? AND `stamp` > ?');
                $sqlFetchOC->execute(array('oc', $stamp['stamp']));
                $ocMessages = $sqlFetchOC->fetchAll();
            
                // splice this to other messages
            
                $messageList = array_merge($messages, $ocMessages);
            
            }else{
            
                $messageList = $messages;
            
            }
        
            // Reset the stamp to last message stamp if stamp doesn't already =  last message stamp
            $count = count($messageList);
            $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
            $sqlUpdate->execute(array($messageList[$count - 1]['stamp'], $character->return_name()));
        
            echo json_encode($messageList);
    
}

Code (markup):

 

I found something semi-enlightening. I changed the code a bit so I could go directly to the page. Sometimes I get result in $messagesList, and sometimes I get php errors

I'm not sure what is causing that. I guess the stamp does match last message in character stamp. Except sometimes it gets set to 0 for some reason. Also when it gets set to 0 it doesn't always line up with when these errors appear.

Errors:

Notice: Undefined offset: -1 in C:\xampp\htdocs\php\game\fetch_messages.php on line 78

Notice: Undefined offset: -1 in C:\xampp\htdocs\php\game\fetch_messages.php on line 80
NULL array(0) { }

When there are results it's like this:

string(10) "1491816834"
array(12) {
[0]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(303) "Looks about the kitchen, wondering how people can live in such despair. He contemplates the actions of city magistrate, town council. How can it be so sad here, while the rest of the city bustles? "Good thing these poor souls have Sel." He says, as he watches the old cleric pass free meals to the poor."
[2]=>
string(303) "Looks about the kitchen, wondering how people can live in such despair. He contemplates the actions of city magistrate, town council. How can it be so sad here, while the rest of the city bustles? "Good thing these poor souls have Sel." He says, as he watches the old cleric pass free meals to the poor."
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491799854"
[4]=>
string(10) "1491799854"
["token"]=>
string(24) "79d04b35176c96db3dbf26ca"
[5]=>
string(24) "79d04b35176c96db3dbf26ca"
}
[1]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(307) "Looks about the kitchen, wondering how people can live in such
despair. He contemplates the actions of city magistrate, town
council. How can it be so sad here, while the rest of the city
bustles? "Good thing these poor souls have Sel." He says, as he
watches the old cleric pass free meals to the poor."
[2]=>
string(307) "Looks about the kitchen, wondering how people can live in such
despair. He contemplates the actions of city magistrate, town
council. How can it be so sad here, while the rest of the city
bustles? "Good thing these poor souls have Sel." He says, as he
watches the old cleric pass free meals to the poor."
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491800178"
[4]=>
string(10) "1491800178"
["token"]=>
string(24) "17b5597b4116f2f5d2d7bd7e"
[5]=>
string(24) "17b5597b4116f2f5d2d7bd7e"
}
[2]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(96) "Look about, wondering how everyone can be so happy. "How can it be?" He says, "Is this working?""
[2]=>
string(96) "Look about, wondering how everyone can be so happy. "How can it be?" He says, "Is this working?""
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491809262"
[4]=>
string(10) "1491809262"
["token"]=>
string(24) "76f257cbd014ccfc9d7df0d1"
[5]=>
string(24) "76f257cbd014ccfc9d7df0d1"
}
[3]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(23) "This is a message test."
[2]=>
string(23) "This is a message test."
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491810294"
[4]=>
string(10) "1491810294"
["token"]=>
string(24) "1589c2024856433e725b3af0"
[5]=>
string(24) "1589c2024856433e725b3af0"
}
[4]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(6) "bummer"
[2]=>
string(6) "bummer"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491811144"
[4]=>
string(10) "1491811144"
["token"]=>
string(24) "13a68b69a07130489bef7fce"
[5]=>
string(24) "13a68b69a07130489bef7fce"
}
[5]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(11) "New Message"
[2]=>
string(11) "New Message"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491811992"
[4]=>
string(10) "1491811992"
["token"]=>
string(24) "b5faad0b5a364544fa600b48"
[5]=>
string(24) "b5faad0b5a364544fa600b48"
}
[6]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(18) "Cool, this is fun."
[2]=>
string(18) "Cool, this is fun."
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491812158"
[4]=>
string(10) "1491812158"
["token"]=>
string(24) "8656635afb93f2d581a3ec43"
[5]=>
string(24) "8656635afb93f2d581a3ec43"
}
[7]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(21) "Adding a new message."
[2]=>
string(21) "Adding a new message."
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491812298"
[4]=>
string(10) "1491812298"
["token"]=>
string(24) "5e32c47a078a6279fb4e03cd"
[5]=>
string(24) "5e32c47a078a6279fb4e03cd"
}
[8]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(4) "Test"
[2]=>
string(4) "Test"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491816588"
[4]=>
string(10) "1491816588"
["token"]=>
string(24) "550f643dc6cf22f4c5755d17"
[5]=>
string(24) "550f643dc6cf22f4c5755d17"
}
[9]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(11) "new message"
[2]=>
string(11) "new message"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491816728"
[4]=>
string(10) "1491816728"
["token"]=>
string(24) "d8c36e41f2b303c9ce45ce24"
[5]=>
string(24) "d8c36e41f2b303c9ce45ce24"
}
[10]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(4) "goob"
[2]=>
string(4) "goob"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491816779"
[4]=>
string(10) "1491816779"
["token"]=>
string(24) "fb6b7f51ccb6ae3ada847b50"
[5]=>
string(24) "fb6b7f51ccb6ae3ada847b50"
}
[11]=>
array(12) {
["channel"]=>
string(3) "all"
[0]=>
string(3) "all"
["character"]=>
string(6) "Ragnok"
[1]=>
string(6) "Ragnok"
["message"]=>
string(7) "flubber"
[2]=>
string(7) "flubber"
["icon"]=>
string(4) "none"
[3]=>
string(4) "none"
["stamp"]=>
string(10) "1491816834"
[4]=>
string(10) "1491816834"
["token"]=>
string(24) "c49db4b437fff4bb5affd8e0"
[5]=>
string(24) "c49db4b437fff4bb5affd8e0"
}
}

 

Case cracked! I just had to shuffle some things. Maybe just tested $messagesList for empty.

Time stamp set run once on window load:

<?php


session_start();

include(__DIR__ . '/../../../sqldata/sqldata.php');
include_once('../classes/Character.php');
include_once('../classes/TimeObject.php');

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
  
    try{
      
        $db = new PDO($dsn, $dbUsername, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
      
    }catch(\PDOException $e){}
  
    $character = new Character;
    $character->set_db($db);
    $character->set_character();
  
    // SELECT LAST STAMP
  
    $sqlSelect = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
    $sqlSelect->execute(array($character->return_name()));
    $fetchedStamp = $sqlSelect->fetch();
  
    if(empty($fetchedStamp))
    {
      
        // Create first stamp
        $sqlInsertStamp = $db->prepare('INSERT INTO `characters_message_stamps_36933753` (`character`, `stamp`) VALUES (?, ?)');
        $sqlInsertStamp->execute(array($character->return_name(), time()));
      
    }else{
      
        // update stamp
        $sqlInsertStamp = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
        $sqlInsertStamp->execute(array(time(), $character->return_name()));
      
    }
  
}

?>

Code (markup):

Fetch message script

<?php

header('Content-Type: application/json');
session_start();

if(isset($_SESSION['ID']) && isset($_SESSION['serial']))
{
      
        include('../classes/Character.php');
        include('../classes/TimeObject.php');
        include(__DIR__ . '/../../../sqldata/sqldata.php');
      
        try{
          
            $db = new PDO($dsn, $dbUsername, $dbPassword);
          
        }catch(\PDOException $e){}
      
        $character = new Character;
        $character->set_db($db);
        $character->set_character();
        $character->set_location();
      
        $time = new TimeObject;

        $sqlStamp = $db->prepare('SELECT `stamp` FROM `characters_message_stamps_36933753` WHERE `character` = ?');
        $sqlStamp->execute(array($character->return_name()));
        $stamp = $sqlStamp->fetch();
      
            // fetch all messages to the character, including OC message if OC is on
                // Fetch where messages stamp is newer than current character_message_stamps stamp
          
            // fetch messages to character
          
            $sqlFetchMessages = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                               WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?
                                                UNION
                                                SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753`
                                                WHERE `roomToken` = ? AND `channel` = ? AND `stamp` > ?');
                                              
            $sqlFetchMessages->execute(array($character->return_room_token(),
                                            $character->return_name(),
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'all',
                                            $stamp['stamp'],
                                            $character->return_room_token(),
                                            'system',
                                            $stamp['stamp']));
                                              
            $messages = $sqlFetchMessages->fetchAll();
              
                $_POST['ocObj'] = 'on';
  
            // Fetch OC messages
            if($_POST['ocObj'] == 'on'){
                  
                $sqlFetchOC = $db->prepare('SELECT `channel`, `character`, `message`, `icon`, `stamp`, `token` FROM `world_messages_36933753` WHERE `channel` = ? AND `stamp` > ?');
                $sqlFetchOC->execute(array('oc', $stamp['stamp']));
                $ocMessages = $sqlFetchOC->fetchAll();
              
                // splice this to other messages
              
                $messageList = array_merge($messages, $ocMessages);
              
            }else{
              
                $messageList = $messages;
              
            }
              
            $count = count($messageList);  
              
            if($count >= 1)
            {
          
                // Reset the stamp to last message stamp if stamp doesn't already =  last message stamp
              
                $sqlUpdate = $db->prepare('UPDATE `characters_message_stamps_36933753` SET `stamp` = ? WHERE `character` = ?');
                $sqlUpdate->execute(array($messageList[$count - 1]['stamp'], $character->return_name()));
              
          
            }
                  
            echo json_encode($messageList);
      
}


?>

Code (markup):

Basically if anyone wants to rig this up use two tables. One to hold messages, and one to hold the last fetched time stamp for each player or character.

Edit:
Don't forget to sanitize output.

foreach($messageList as $key => $val)
            {
               
                $messageList[$key]['message'] = htmlentities($messageList[$key]['message']);
               
            }

Code (markup):

 

I think a better approach overall would be to run a webservice that pushes messages to the users, instead of fetching from the users, but that would involve a whole different approach. Glad you figured it out, though.

 

Thanks. What would I be looking at to do your method? Push messages from the server to the user? What kind of tech is it? Something that does the opposite of ajax is what it sounds like, lol.

 

It's basically a webservice, using service workers. If you Google you should be able to find some anwers (or more questions)