CODEWITHSUNDEEP
phparrayspostgresqlmultidimensional-arrayparameter-passing
otorrillas
otorrillas

Reputation: 4773

Prepared statement with an array field PostgreSQL&PHP

Having the following table:

CREATE TABLE submission (
    num SERIAL,
    user_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    timestamp TIMESTAMP,
    lti_info TEXT[][],
    PRIMARY KEY(num),
    FOREIGN KEY (user_id) REFERENCES canvas_user,
    FOREIGN KEY (assignment_id) REFERENCES assignment
);

I want to make INSERTs on the table with a php function, something like the following (not working) code:

function create_submission($user_id, $assignment_id, $lti_info) {
        # INSERT everything except lti info
        $query = "INSERT INTO submission (user_id, assignment_id, timestamp) VALUES ($1, $2, LOCALTIMESTAMP) RETURNING num";
        pg_prepare($this->con, "createsub", $query);
        $rs = pg_execute($this->con, "createsub", array($user_id, $assignment_id));
        $row = pg_fetch_row($rs);
        $sub_num = $row[0];

        # update table to insert lti_info
        $serialized = serialize($lti_info);
        $query = "UPDATE submission SET lti_info = $1";
        pg_prepare($this->con, "insertlti", $query);
        pg_execute($this->con, "insertlti", array($serialized));
        return $sub_num;
    }

I have tried to serialize and do something like this answer on a similar question: Supply a prepared statement with an array, but with no success.

I would be very pleased if any of you can provide a solution to the create_submission function.

UPDATE:

When I execute create_submission it gives the following error at the update point:

pg_execute(): Query failed: ERROR: array value must start with "{" or dimension information

lti_info is an associative array of strings, similar to:

lti_info = array (
"oauth_consumer_key" => "example",
"oauth_consumer_secret" => "secret"
)

Note: I don't mind if everything is done in a single insert or with first insert "normal" fields and then the lti_info array.

Upvotes: 2

Views: 837

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

There are no associative arrays in Postgresql. To save it as a bi dimensional array it would be necessary to build a suitable string to pass to Postgresql and do the reverse on the way back. It is simpler to save it as JSON:

create table submission (
    num serial,
    user_id integer not null,
    assignment_id integer not null,
    timestamp timestamp,
    lti_info json,
    primary key(num)
);

Use json_encode

<?php
$lti_info  = array (
    "oauth_consumer_key" => "example",
    "oauth_consumer_secret" => "secret"
);

$query = "
    insert into submission (user_id, assignment_id, timestamp, lti_info) values
    ($1, $2, localtimestamp, $3)
    returning num
";

$conn = pg_connect ( "port=5432 user=cpn dbname=cpn");
pg_prepare($conn, "createsub", $query);
pg_execute($conn, "createsub", array(1, 1, json_encode($lti_info)));

$query = "select * from submission";
pg_prepare($conn, "select", $query);
$rs = pg_execute($conn, "select", array());

$lti_info = (array) json_decode(pg_fetch_result($rs, "lti_info"));

print_r ($lti_info);
?>

Outputs

    Array
(
    [oauth_consumer_key] => example
    [oauth_consumer_secret] => secret
)

Upvotes: 1

Related Questions