改进定期查询SQLite数据库的代码

I have an sqlite database that I query from PHP periodically. The query is always the same and it returns me a string. Once the string changes in the database the loop ends.

我有一个sqlite数据库,我定期从PHP查询。查询始终相同,它返回一个字符串。一旦字符串在数据库中发生变化,循环结束。

The following code is working, but I am pretty sure this is not the optimal way to do this...

以下代码正在运行,但我很确定这不是执行此操作的最佳方式...

class MyDB extends SQLite3
{
    function __construct()
    {
        $this->open('db.sqlite');
    }
}

$loop = True;
while ($loop == True) {
    sleep(10);
    $db = new MyDB();
    if (!$db) {
        echo $db->lastErrorMsg();
    } else {
        echo "Opened database successfully\n";
    }
    $sql = 'SELECT status from t_jobs WHERE name=' . $file_name;

    $ret = $db->query($sql);
    $state = $ret->fetchArray(SQLITE3_ASSOC);
    $output = (string)$state['status'];
    if (strcmp($output, 'FINISHED') == 0) {
        $loop = False;
    }
    echo $output;
    $db->close();
}

1 个解决方案

#1

If you want to get an output immediately and a kind of interface, I think The best solution for your problem might be to use HTTP long polling. This way, it will not hold the connection for hours if the job is not done:

如果你想立即获得输出和一种接口,我认为你的问题的最佳解决方案可能是使用HTTP长轮询。这样,如果没有完成工作,它将无法保持数小时的连接:

you will need to code a javascript snippet (in another html or php page) that runs an ajax call to your current php code.

你将需要编写一个javascript代码段(在另一个html或php页面中),它运行ajax调用你当前的PHP代码。

Your web server (and so, your php code) will keep the connection opened for a while until the job is done or a time limit is reached (say 20-30 seconds)

您的Web服务器(以及您的php代码)将保持连接打开一段时间,直到作业完成或达到时间限制(比如说20-30秒)

if the job is not done, the javascript will make another ajax call and everything will start again, keeping the connection, etc... until you get the expected output status...

如果工作没有完成,javascript将进行另一个ajax调用,一切都将重新开始,保持连接等...直到你得到预期的输出状态...

BEWARE : this solution will not work on any hosting provider

请注意:此解决方案不适用于任何托管服务提供商

You will need to set the max_execution_time to a higher value than the default one see php doc for this.

您需要将max_execution_time设置为高于默认值的值,请参阅php doc。

I think you can find many things on http long polling with php/javascript on google / stack overflow...

我认为你可以在google / stack overflow上使用php / javascript在http长轮询中找到很多东西......

#1