php - 刷新 SQL 查询以显示更改

Question

我有以下查询:

    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];

---

这将为任何新的邮箱消息返回一个数字，我找到了一个 ajax 代码来检查是否有变化。

代码:

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);

但我真的需要弄清楚如何在不刷新整个页面的情况下更新查询？我想也许 ajax 可以提供帮助，但我是 ajax 的新手，我不知道从哪里开始。

---

更新:好的，所以我为查询编写了一个 php 脚本，但不确定如何让 ajax 脚本使用我的“电子邮件”变量。

这是脚本。

    <?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 是正确的 - 使用 Ajax，您可以向网络服务器发送请求，网络服务器将执行您的查询并接收请求。这就像使用浏览器访问页面，只是它发生在后台并且不会重新加载浏览器选项卡/页面。

假设这是您的文件 query.php，您可以通过 my-domain.tld/query.php 访问它

查询.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!

现在在您的模板中或至少在您的 HTML 代码所在的位置:

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);
        }
    });
}

现在您只需要在发生特殊事件(例如单击按钮)时调用 checkForChange():

<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>

希望对您有所帮助。 :)