使用 PHP PDO 从 JSON 对象创建 SQLite 和 MariaDB (Mysql) 表。

会员 4206974

0/5 (0投票)

2023 年 3 月 31 日

CPOL

4分钟阅读

7600

101

一个使用 PDO 从 JSON 对象创建表的 PHP 类。

自己试试.

引言

在应用程序中，通常需要存储信息，尽管这些信息可以归类为通用类型，但它们包含不同的数据，例如，一个事件表，其中包含日志记录、对数据的特定活动等信息；一个最小的解决方案可以是一个包含三个字段的表：

事件类型
JSON 特定数据
时间戳（如果不在上述字段中）

但是，使用此解决方案，会出现恢复这些数据的问题，也许是以一种与关系工具兼容的形式，即表，因此这项工作实际上说明了使用 PHP Data Objects (PDO) 扩展解决此问题的一种可能方法。

背景

JSON 函数和运算符已在 SQLite 版本 3.37.2 中引入，并从版本 3.38.0 (2022-02-22)1 开始默认构建。

本文档中描述的 PHP 脚本不使用 SQLite 提供的功能，因此可以与上述版本以下的 SQLite 版本一起使用。

这是对产品的一次更新，以克服其局限性，尤其是在前一个版本中，JSON 对象必须具有相同的结构，这Incidentally 隐藏了一个概念性错误，没有造成后果。

该软件已在 Data Base SQLite 和 MariaDB (Mysql) 上进行尝试。

Using the Code

脚本 json2table.php 包含名为 J2t 的类；该类包含三个 public 函数：

json2table
execSQL
extractFields

前两个函数假定数据库已打开（即，通过创建新的 PDO 对象）。

第一个函数的作用是使用 JSON 对象数组或 SQL 语句创建临时表，第二个函数也在内部使用。

临时表的生成

json2table 函数的结构是：

json2table($dbh,$data,$tableName="TemporaryTable",$flds=array())

其中

$dbh 是 PDO 基类的实例。
$data 是 JSON 对象数组或 SQL SELECT 语句。
$tableName 是创建的临时表的名称。
$flds 是一个可选字段名数组，用于从输入中提取以创建表。

输入数据

如果 json2table 函数的第二个参数是 SQL SELECT 命令，则涉及的字段可以是 JSON 字符串以及普通字段，特别是 JSON 字段可以是 对象，即键值对的集合，或者是一个维度为 1 的包含 对象 的数组。

对象	`{"Author":"Alan Perlis","Source":"","Quote":"Syntactic sugar causes cancer of the semicolon."}`
数组中的对象	`[{"_id":"9sgo4-hjyyq","author":"The Buddha","content":"Radiate boundless love towards the entire world — above, below, and across — unhindered, without ill will, without enmity.","tags":["Wisdom","Love"],"length":122,"dateAdded":"2023-03-30"}]`

一个项可以是一个数组，例如，上面的 "tags":["Wisdom","Love"] 会变成一个名为 tags 字段，值为 Wisdom, Love。

表结构

生成的表的字段将是输入数据集中第一个元素的字段，或者是在方法可能的第四个参数中指定的字段，该参数是一个字段名数组，可能还包含数据类型。

include 'json2table.php';
$DBname = "json.sqlite";
$db = new PDO("sqlite:$DBname");
$j2table = new J2t;
...
$sql = "SELECT * FROM Advanced";
$ans = $j2table→json2table($db,$sql,$tTable,array("Store","Qty","Box"=>"TEXT");

字段数据类型是从输入数据中推断出来的，除非在第四个参数中指定；可接受的值为 TEXT、INTEGER 和 REAL。

第四个参数的用处在于能够

获取数据的一个子集，
可以指定数据类型，例如，将存储为字符的数据视为数字，
指定第一个行中不存在的字段。

输出数据

函数返回

如果表已创建，则返回一个空的 string。
如果 JSON 数组为空或 SELECT 语句返回空集，则返回 No Data!。

还会创建一个临时表来处理可能的错误。

示例 1：与数据数组一起使用并调用类函数

...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs';
$sth = $dbh->prepare($sql);
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_COLUMN, 0);    
$table = "tTable";
J2t::json2table($dbh,$result,$table);
...

执行 SQL 命令

该函数由 json2table 函数使用，但它也是一个方法，可用于执行 SQL 命令。

execSQL($dbh,$sql,$parms=[])

可选的 $parms 是一个值数组，其中包含在执行的 SQL 语句中绑定的元素，请参见下面的示例 2。

该函数返回一个 PDOStatement 对象或一个错误，如果 SQL 不正确；为了获取错误，该函数将错误模式设置为 SILENT；命令执行后会恢复之前的模式。

ExecSQL 函数

public static function execSQL($dbh,$sql,$parms=[]) {
    $errMode = $dbh→getAttribute(PDO::ATTR_ERRMODE); // save previous ATTR_ERRMODE
    $dbh->setattribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT);    // disable crash
    $sth = $dbh->prepare($sql);
    $a = $dbh->errorInfo();
    $dbh->setattribute(PDO::ATTR_ERRMODE,$errMode);  // restore previous ATTR_ERRMODE
    if (!$sth) {return "\n<br>$sql\n<br>".$a[2];}
    $sth->execute($parms);
    return $sth;
}

示例 2：与 SQL 一起使用并调用类方法

...
include 'common/json2table.php';
$dbh = new PDO("sqlite:DataBaseFile");
...
$sql = "SELECT Data FROM Loggin WHERE Type = 'MoveDrugs'";
$y2t = new J2t;
$answer = $y2t->json2table($dbh,$sql,"t");
if ($answer != "") exit($answer);		// Something went wrong
$result = $y2t->execSql($dbh,"SELECT Count(*) Count, SUM(Qty) Qty FROM t");
if (gettype($result) == "string") echo $result;  // SQL problem
else print_r($result→fetchAll(PDO::FETCH_ASSOC));
$sql = "SELECT * FROM t WHERE Qty > ?";
print_r($y2t->execSql($dbh,,Array(100))->fetchAll(PDO::FETCH_ASSOC));
...

历史

2023 年 3 月 31 日
- 初始版本。
2023 年 5 月 11 日
- 来自 SQL SELECT 的输入数据可以包含普通字段和一个或多个 JSON 字段，
- JSON 字段的项可以是一个数组。
2024 年 3 月 18 日
- 字段选择，
- 增强的类型管理，
- 增强的错误管理。