Automatically fetch new contact from database

[Shattered]

So I am working on a messanger, and I am having problems automatically fetching new messages from the database.
If anyone can fix me up with a peice of code which can fetch new information from the database after every 2 seconds or so.

I am very new to working with PHP and databases, but this is the code I got so far.

 <?php $loadRecent = $db->fetchAll("SELECT * FROM messages WHERE fromUser = ? AND toUser = ? OR fromUser = ? AND toUser = ? ORDER BY dateTime, id DESC", array($ownerID, $myID, $myID, $ownerID)); ?> 
    <?php
        if($loadRecent) {
            foreach($loadRecent as $recent) {
                $messageID = $recent['id'];    
                $message = substr($recent['message'], 0, 200);
                $dateTime = $recent['dateTime'];
                $fromUser = $recent['fromUser'];
                $fromName = $recent['fromName'];
                $readUnread2 = $recent['readUnread'];
                if($readUnread2 == 0) {
                    $readUnread = '(Unread)';
                    } else {
                    $readUnread = '';
                }
                if($fromUser == $myID) {
                echo "
                <div class=\"msg\">$message</div>
                <div class=\"msgsentby\">{$fromName} @ {$dateTime}</div>
                ";
                } else {
                echo "<div class=\"msg msgfrom\">$message</div>
                <div class=\"msgsentby msgsentbyfrom\">{$fromName} @ {$dateTime}</div>";
                }

            }
            } else {
            echo "<center>Your recently messages will appear here!</center>";
        }
    ?>

[diafol]

I'm assuming you want an ajax call every 2 seconds. Don't ask for code though - show what you have tried and then we have a starting point for discussion. However, I will give a few pointers WRT how I'd tackle things:

SELECT * FROM messages WHERE fromUser = ? AND toUser = ? OR fromUser = ? AND toUser = ? ORDER BY dateTime, id DESC

This is a big ambiguous. Don't you mean:

SELECT * FROM messages WHERE (fromUser = ? AND toUser = ?) OR (fromUser = ? AND toUser = ?) ORDER BY dateTime, id DESC

Keep a JS variable with the last datetime called:

var lastDateTime = 0;

Add this to the ajax call to the PHP file:

$.getJSON( file.php, {
    lastDT: lastDateTime,
    id: friendID
})
.done(function( data ) {
    $.each( data, function( i, v ) {
         //append data to DIV or whatever holds the msgs 
    });
});

You can set a timer to run this ajax code if you place the ajax code within a function. Don't forget to update the lastDateTime with the first DateTime (since you are using DESC) from the retrieved record.

SELECT * FROM messages WHERE dateTime > ? AND ((fromUser = ? AND toUser = ?) OR (fromUser = ? AND toUser = ?)) ORDER BY dateTime, id DESC

This will retrieve records correct to the nearest second. As such, you may lose simultaneous posts - the chances of this happening are very low, but real nonetheless. You may find storing microtime better? Alternatively, you could store the last message id instead of the datetime. I'm assuming that the messages are stored via autoincrement ID, so that should provide an more reliable parameter.

SELECT * FROM messages WHERE id > ? AND ((fromUser = ? AND toUser = ?) OR (fromUser = ? AND toUser = ?)) ORDER BY id DESC

So use:

var msgID = 0;

And...

$.getJSON( file.php, {
    messageID: msgID,
    id: friendID
})
.done(function( data ) {
    $.each( data, function( i, v ) {
         //append data to DIV or whatever holds the msgs
         //update msgID to first ID in 'data'.
    });
});

[Shattered]

Okay I am alittle bit confused on everything if you can expain the getJSON thing alittle bit more.

[diafol]

http://api.jquery.com/jquery.getjson/

[Shattered]

Still kinda confused...

Can anyone get in a skype call with me and help?

[diafol]

Saying you're confused with EVERYTHING is not useful. Can you not articulate specific bits that you're having difficulty with?

[Shattered]

All of the Ajax.
I tried setting it all up and it didnt work... Can you set up the code so all I have to do is paste it in? I dont know how to set any of it up, I know nothing about javascript and ajax.