refresh a SQL query to display changes

Question

I have the following query:

    if (!isset($profile_id) || !is_numeric($profile_id))
        return false;

    if ( isset(self::$newMessageCountCache[$profile_id]) )
    {
        return self::$newMessageCountCache[$profile_id];      

    $filter_cond = SK_Config::section('mailbox')->section('spam_filter')->mailbox_message_filter ? " AND `m`.`status`='a'" : '';

    $query = "SELECT COUNT(DISTINCT `c`.`conversation_id`)
        FROM `".TBL_MAILBOX_CONVERSATION."` AS `c`
        LEFT JOIN `".TBL_MAILBOX_MESSAGE."` AS `m` ON (`c`.`conversation_id` = `m`.`conversation_id`)
        WHERE (`initiator_id`=$profile_id  OR `interlocutor_id`=$profile_id)
        AND (`bm_deleted` IN(0,".self::INTERLOCUTOR_FLAG.") AND `initiator_id`=$profile_id OR `bm_deleted` IN(0,".self::INITIATOR_FLAG.") AND `interlocutor_id`=$profile_id)
        AND (`bm_read` IN(0,".self::INTERLOCUTOR_FLAG.") AND `initiator_id`=$profile_id OR `bm_read` IN(0,".self::INITIATOR_FLAG.") AND `interlocutor_id`=$profile_id)
        $filter_cond AND `m`.`recipient_id`=$profile_id
    ";

    self::$newMessageCountCache[$profile_id] = SK_MySQL::query($query)->fetch_cell();
    return self::$newMessageCountCache[$profile_id];

---

This will return a number for any new mailbox messages, I have found an ajax code for checking if there is a change.

Code:

var previousValue = null;

function checkForChange() {
    $.ajax({
        url: '',
        ...
        success: function(data) {
            if (data != previousValue) { // Something have changed!
                //Call function to update div
                previousValue = data;
            }
        }
    });
}

setInterval("checkForChange();", 1000);

But I really need to figure out how to update the query without refreshing the entire page? I figured maybe something with ajax can help but I am totally new to ajax and I don't have an no idea where to start.

---

Update: ok so I wrote a php script for the queries but not sure how to get ajax script to use my "emails" var.

here is the script.

    <?php
if($_SERVER['HTTP_X_REQUESTED_WITH'] != "XMLHttpRequest") {
      die();
 }

include("..\internals\config.php");

$host       = DB_HOST;
$user       = DB_USER;
$password   = DB_PASS;
$dbname     = DB_NAME;
$prefix     = DB_TBL_PREFIX;


$cxn = mysql_pconnect ($host, $user, $password);

mysql_select_db($dbname, $cxn);


function get_user_id() 
    {   
        $userid = NULL;

        if (!empty($_COOKIE['PHPSESSID'])) 
        {
            $result = $cxn->execute("
                SELECT profile_id 
                FROM " . TABLE_PREFIX . "profile_online 
                WHERE hash = '" . $cxn->escape_string($_COOKIE['PHPSESSID']) . "'
            ");

            if ($row = $cxn->fetch_array($result)) 
            {
                $userid = $row[0];
            }
        }

        return $userid;
    }

$profile_id  = get_user_id();   

 public static function newMessages( $profile_id )
    {
        if (!isset($profile_id) || !is_numeric($profile_id))
            return false;

        if ( isset(self::$newMessageCountCache[$profile_id]) )
        {
            return self::$newMessageCountCache[$profile_id];
        }

        // check config for filter condition
        $filter_cond = SK_Config::section('mailbox')->section('spam_filter')->mailbox_message_filter ? " AND `m`.`status`='a'" : '';

        $query = "SELECT COUNT(DISTINCT `c`.`conversation_id`)
            FROM `".TBL_MAILBOX_CONVERSATION."` AS `c`
            LEFT JOIN `".TBL_MAILBOX_MESSAGE."` AS `m` ON (`c`.`conversation_id` = `m`.`conversation_id`)
            WHERE (`initiator_id`=$profile_id  OR `interlocutor_id`=$profile_id)
            AND (`bm_deleted` IN(0,".self::INTERLOCUTOR_FLAG.") AND `initiator_id`=$profile_id OR `bm_deleted` IN(0,".self::INITIATOR_FLAG.") AND `interlocutor_id`=$profile_id)
            AND (`bm_read` IN(0,".self::INTERLOCUTOR_FLAG.") AND `initiator_id`=$profile_id OR `bm_read` IN(0,".self::INITIATOR_FLAG.") AND `interlocutor_id`=$profile_id)
            $filter_cond AND `m`.`recipient_id`=$profile_id
        ";

        self::$newMessageCountCache[$profile_id] = SK_MySQL::query($query)->fetch_cell();
        return self::$newMessageCountCache[$profile_id];
    }

mysql_close($cxn);

$emails  = newMessages();

?>

Answer 1

Ajax is correct - with Ajax you can send a request to the webserver which will execute your query and receive a request. Its like visiting a page with the browser except that it happens in the background and not reloading the browsertab/page.

Lets say this is your file query.php and you can access it via my-domain.tld/query.php

query.php:

//First make this file only executeable for AJAX-Requests but no regular visit via browser:
 if($_SERVER['HTTP_X_REQUESTED_WITH'] != "XMLHttpRequest") {
      die(); //User tried to enter query.php with a browser or similar...
 }

//call function where query is stored or put your query here and save result

//Its important to echo what you want to be returned in the ajax-request.
echo self::$newMessageCountCache[$profile_id];
die(); //Best is die after last echo to make sure there are no extra outputs!

Now in your template or atleast where your HTML code is:

function checkForChange() {
    $.ajax({

        url: 'my-domain.tld/query.php', //See, here is the URL to your file on server

        data: {}, //You need this only if you want to mpass any variables to your script. On PHP Server-side this data are available via $_POST array!

        success: function(data) { //data contains all echo'ed content from query.php

            $(".mailbox_messages").html(data); //The best is make your container to be updated to a class or ID to access. Its content can be overridden with .html() function. Simply echo all contents in query.php that you want to be displayed in your element and override the content with .html(data);
        }
    });
}

Now you just need to call checkForChange() when something special happens like a button click for example:

<input type="button" id="refresh-mailbox" value="Refresh my Mailbox" />

<script type="text/javascript">
    $("#refresh-mailbox").on("click", function() { 
        checkForChange(); //Execute your function on button click and done!
    });
</script>

I hope this helps. :)