每次用户更改MySQL / WordPress数据库中的行时自动运行PHP脚本

I'm building a small web app to feed one website from a row of a database in another website (a WordPress based website).

我正在构建一个小型Web应用程序，从另一个网站（基于WordPress的网站）的一行数据库中提供一个网站。

Essentially I have three files: one called outputjson.php that will generate a JSON file called results.json (that live in my WordPress website) and in another website I will make an AJAX request for that file in order to display an announcers section for a radio station (with pictures of the announcer, socials, time).

基本上我有三个文件：一个名为outputjson.php，它将生成一个名为results.json的JSON文件（存在于我的WordPress网站中），在另一个网站中，我将为该文件发出一个AJAX请求，以显示一个播音员部分一个广播电台（播音员的照片，社交，时间）。

My problem is that I have to manually initiate the process but I would like to do it automatically, every time anyone will update my database, I want that my file will listen to the event and run the PHP script.

我的问题是我必须手动启动该过程，但我想自动执行，每次有人更新我的数据库，我希望我的文件将监听事件并运行PHP脚本。

So far my code is:

到目前为止我的代码是：

outputjson.php

outputjson.php

<?php
global $wpdb;
if(!isset($wpdb))
{
    require_once('wp-config.php');
    require_once('wp-includes/wp-db.php');
}
$result = $wpdb->get_results ( "SELECT * FROM " . $table_prefix . "radio_announcer_on_air" );

$fp = fopen('results.json', 'w');
fwrite($fp, json_encode($result, JSON_UNESCAPED_SLASHES));
fclose($fp);
?>

Which will generate a file like:

这将生成如下文件：

results.json

results.json

    [  
       {  
          "announcer_id":"19",
          "announcer_time_start":"07:00:00",
          "announcer_time_end":"08:59:59",
          "announcer_photo":"image.jpg",
          "announcer_name":"Name",
          "announcer_facebook":"",
          "announcer_twitter":"",
          "announcer_rss":"",
          "announcer_mail":"",
          "announcer_weekday":"7"
       }
]

My last file will live on another server and will make the magic:

我的上一个文件将存在于另一台服务器上，并将产生魔力：

announcers.js

announcers.js

$(function(){
    $.ajax({
      url: "http://otherwebsite.com/results.json",
      type: "GET",
      dataType: "JSON",
      cache: false,
      success: function(markers) {
//Do something
}

Any suggestion? I could even consider running this script every day for example, since running it every time someone is going to change my database row (the event) sounds very complicated, at least for my knowledge that I've got so far.

有什么建议吗？我甚至可以考虑每天运行这个脚本，例如，每次有人要更改我的数据库行（事件）时运行它听起来非常复杂，至少我知道我到目前为止。

1 个解决方案

#1

The great thing about consuming a JSON endpoint in a browser is that it does not have to come from a static file.

在浏览器中使用JSON端点的好处是它不必来自静态文件。

Consider your results.json example. If this is a static file, then your web server (usually Apache) will detect the json extension and send the appropriate MIME type header to identify its type to the browser. For JSON, that is:

考虑一下你的results.json例子。如果这是一个静态文件，那么您的Web服务器（通常是Apache）将检测json扩展并发送相应的MIME类型标头以向浏览器标识其类型。对于JSON，即：

application/json

So, as long as we do the same in PHP, an AJAX call in JavaScript won't notice the difference. In outputjson.php, just add this at the start:

因此，只要我们在PHP中执行相同操作，JavaScript中的AJAX调用就不会注意到差异。在outputjson.php中，只需在开头添加：

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

This is necessary since the web server will see that the file extension is php and will not know what MIME type that corresponds to (since a PHP script can generate a file of any type). Thus, we have to do that manually.

这是必要的，因为Web服务器将看到文件扩展名是php并且不知道对应的MIME类型（因为PHP脚本可以生成任何类型的文件）。因此，我们必须手动完成。

To wire it into your JavaScript, simply point the url parameter to your PHP script:

要将它连接到JavaScript中，只需将url参数指向PHP脚本：

url: "http://otherwebsite.com/outputjson.php",

I mentioned in the comments that a file could be called something.json.php. To clarify, this naming style is just a convention, and does not actually get the web server to set any headers automatically - you still have to do that in your PHP script. However, the naming makes it very easy for developers and site users to see (in a URL bar or file explorer) what type of content the file generates.

我在评论中提到一个文件可以叫做something.json.php。为了澄清，这种命名方式只是一种约定，实际上并没有让Web服务器自动设置任何标题 - 您仍然必须在PHP脚本中执行此操作。但是，命名使开发人员和站点用户可以非常轻松地查看（在URL栏或文件资源管理器中）文件生成的内容类型。

New script

So, your updated script will be something like this:

所以，你更新的脚本将是这样的：

<?php
header('Content-Type: application/json');

global $wpdb;
require_once 'wp-config.php';
require_once 'wp-includes/wp-db.php';

$result = $wpdb->get_results(
    "SELECT * FROM {$table_prefix}radio_announcer_on_air"
);

echo json_encode($result);
?>

Notice that it no longer has to write to a file - it should just output the data to the browser. I removed JSON_UNESCAPED_SLASHES since I did not understand the purpose of that - put it back if you are sure you need it, but the PHP defaults are usually good.

请注意，它不再需要写入文件 - 它应该只是将数据输出到浏览器。我删除了JSON_UNESCAPED_SLASHES，因为我不明白它的目的 - 如果你确定需要它，请把它放回去，但PHP的默认值通常很好。

I've removed the if statement as well, since there is no way for the global database variable to be set until the WP libraries are loaded.

我也删除了if语句，因为在加载WP库之前无法设置全局数据库变量。

Addendum

It would be worth considering how often this endpoint is to be consumed by AJAX, and how much of a load it puts on your database. If it were to contain more than a few hundred rows, you might want to think about pagination, since AJAX operations handling large amounts of data can slow the browser down.

值得考虑的是AJAX消耗这个端点的频率，以及它对数据库的负载程度。如果它包含超过几百行，您可能需要考虑分页，因为处理大量数据的AJAX操作会降低浏览器的速度。

However, it is wise to get it working first, and then you can optimise from there if it proves necessary.

但是，首先让它工作是明智的，然后如果证明有必要，你可以从那里进行优化。