问题:PHP PDO PostgreSQL -> 插入/更新日期或时间戳的数组,以及布尔值

我正在尝试使用 PDO 使用 PHP 更新 PostgreSQL 数据库。

2 列的类型为 bool[] 和 timestamp[],均为 ARRAYS。

我收到布尔数组的此错误:

异常:PDOException:SQLSTATE[22P02]:无效的文本表示:7 错误:布尔类型的无效输入语法:“'true','true','true','true','true'” in /var/ www/fussyfindings.com/public_html/dbtest.php:49 堆栈跟踪:#0 /var/www/fussyfindings.com/public_html/dbtest.php(49): PDOStatement->execute() #1 {main }

和一个类似的日期。

我花了 2 天时间试图修复它,但我不知道如何解决。

如果我在服务器上手动运行查询,它可以使用类似的语法正常工作。

UPDATE ff_search_data
SET someboolarray = ARRAY['true','true','true','true','true']::bool[], sometsarray = ARRAY['2009-12-01 00:00:00','2009-12-01 00:00:00','2009-12-01 00:00:00','2009-12-01 00:00:00','2009-12-01 00:00:00','2009-12-01 00:00:00']::timestamp[]
WHERE search_term = 'test';

然后数据库包含:

{true,true,true,true,true} 和 {'2009-12-01 00:00:00.000','2009-12-01 00:00:00.000','2009-12-01 00:00:00.000 ','2009-12-01 00:00:00.000','2009-12-01 00:00:00.000','2009-12-01 00:00:00.000'}

任何人都可以帮忙吗?我很沮丧...我尝试将 { } 放在内爆周围,但没有任何区别。

谢谢你。

这是一个代码示例。

<?php
    require_once("../config.php");

    ini_set('display_errors', 1);
    ini_set('display_startup_errors', 1);
    error_reporting(E_ALL);

    $servername = PQSQL_DB_HOST;
    $database = PQSQL_DB_NAME;
    $username = PQSQL_DB_USERNAME;
    $password = PQSQL_DB_PASSWORD;
    $sql = "pgsql:dbname=$database;host=$servername;user=$username;password=$password"; 
    $dsn_Options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

    try { 
        $db_connection = new PDO($sql, $username, $password, $dsn_Options);
        $db_connection->exec("SET NAMES 'UTF8';");
    } catch (PDOException $error) {
        http_response_code(503);
        return false;
    }

    $boolvalue = "'true'"; // I want to use the string version, unless you can store the other in array below
    $timestamp = date('Y-m-d H:i:s');
    $searchterm = "test";

    $boolarray = array();
    $tsarray = array();

    for ($i=0; $i<5; $i++) {
        array_push($boolarray, $boolvalue); 
        array_push($tsarray, $timestamp);
    }



    $tsarray = implode(",", $tsarray);
    $boolarray = implode(",", $boolarray);

    $update_statement = $db_connection->prepare("UPDATE " . PQSQL_DB_TABLE_SEARCH_DATA . "
    SET someboolarray = ARRAY[:bool_array]::bool[], sometsarray = ARRAY[:ts_array]::timestamp[]
    WHERE search_term = :search_term");

    $update_statement->bindParam(":bool_array", $boolarray, PDO::PARAM_BOOL);
    $update_statement->bindParam(":ts_array", $tsarray);    
    $update_statement->bindParam(":search_term", $searchterm);
        try {
            if ($update_statement->execute()) {
                echo "updated";
                http_response_code(200);
                return true;
            } 
            else {
                echo "update failed";
                http_response_code(403);
                return false;
            }
        }
        catch (Exception $e) {
            http_response_code(503);
            echo "exception: " . $e;
            return false;
        }
?>

解答

本质上,您试图将作为implode结果的单个字符串(不是数组)绑定到接收多个逗号分隔项的ARRAY[...]。考虑构建一个包含多个 qmarks 占位符?的准备好的语句,然后使用for循环迭代地绑定这些占位符。

阵列构建

$boolvalue = "true";
$timestamp = date('Y-m-d H:i:s');
$searchterm = "test";

$boolqmarks = array();                        # NEW Q MARK ARRAY
$tsqmarks = array();                          # NEW Q MARK ARRAY

$boolarray = array();
$tsarray = array();

for ($i=0; $i<5; $i++) {
  array_push($boolqmarks, '?'); 
  array_push($boolarray, $boolvalue); 

  array_push($tsqmarks, '?'); 
  array_push($tsarray, $timestamp);
}

SQL 参数化

# SINGLE STRINGS
$boolqmarkstr = implode(", ", $boolqmarks);
$tsqmarkstr = implode(", ", $tsqmarks);

# PREPARE STATEMENT WITH ? PLACEHOLDERS
$sql = "UPDATE mytable
        SET someboolarray = ARRAY[". $boolqmarkstr ."]::bool[], 
            sometsarray = ARRAY[". $tsqmarkstr ."]::timestamp[]
        WHERE search_term = :search_term";

$update_statement = $db_connection->prepare($sql);

# BIND ? AND NAMED PARAMS (bindValue is 1-indexed)
foreach (array_merge($boolarray, $tsarray) as $k => $v)
    $update_statement->bindValue(($k+1), $v);

$update_statement->bindParam(":search_term", $searchterm);

# EXECUTE QUERY
$update_statement->execute();