Prepared statement with an array field PostgreSQL&PHP

Question

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.

So what is the problem? What happens when you run that? What should happen instead? What is lti_info and why it must be updated after the insert? — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 26, 2014 at 12:42
I have updated the question, trying to answer to your questions. — otorrillas
– otorrillas, Commented Aug 26, 2014 at 14:33

Clodoaldo Neto · Accepted Answer · 2014-08-26 17:35:13Z

1

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
)

edited Aug 26, 2014 at 17:35

answered Aug 26, 2014 at 17:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

otorrillas Over a year ago

The answer will be great if it wasn't for my current version of PostgreSQL (8.4) and JSON data types columns were introduced in 9.2. I have no possibility to upgrade PostgreSQL...any solution?

Clodoaldo Neto Over a year ago

@otorrillas If the data will only be used at the PHP side then save it as lti_info text, and the PHP code will be the same. If it will be used at the Postgresql side then save as two separate columns. Why do you want the key and the secret combined in a single column? Why not two?

otorrillas Over a year ago

It will just be used only at the PHP side, the interaction with the db in this case is just for storage. Thanks for your help.

Collectives™ on Stack Overflow

Prepared statement with an array field PostgreSQL&PHP

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related