PHP PDO PostgreSQL -> Insert/Update ARRAY of Date or Timestamp, and Boolean

Question

I'm trying to update a PostgreSQL database with PHP using PDO.

The 2 columns are of type bool[] and timestamp[], both ARRAYS.

I get this error for the boolean array:

exception: PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: "'true','true','true','true','true'" in /var/www/fussyfindings.com/public_html/dbtest.php:49 Stack trace: #0 /var/www/fussyfindings.com/public_html/dbtest.php(49): PDOStatement->execute() #1 {main}

And a similar one for the date.

I have spent 2 days trying to fix it and I don't know how.

If I manually run a query on the server, it works fine with similar syntax.

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

The database then contains:

{true,true,true,true,true} and {'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'}

Can anyone help? I am very frustrated... I have tried putting { } around the implodes and it didn't make a difference.

Thank you.

Here's a code example.

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

Answer 1

Essentially, you are attempted to bind single strings (not arrays) which are results of implode to the ARRAY[...] that receives multiple comma separated items. Consider building a prepared statement of multiple qmarks placeholders, ?, that are then binded iteratively with a for loop.

Array Builds

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

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