Can I use a prepared statement in Postgres to add multiple values? When I saw that things are added to the prepared statement with array($val), it sort of occurred to me that I should be able to supply an array of values to be put in my table. Is this wildly incorrect? When I tried, I saw in my db table only Array. I don't know if it is an actual array, but I'm guessing, just the word, as the column is a simple character variable.
$tag = array('item1', 'item2', 'item3');
// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", "INSERT INTO $table ($column) VALUES ($1)");
// Execute the prepared query. Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("$tag"));
Otherwise, why is the one value supplied as an array?
No it's not, You inserted the text Array... if the type of $column is text your code should read
$tag = array('item1', 'item2', 'item3');
// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", "INSERT INTO $table ($column) VALUES ($1)");
// Execute the prepared query. Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
foreach( $tag as $i )
$result = pg_execute($dbconn, "my_query", array($i));
/// alternatively you could try this if you really wanna insert a text as array of text without using text[] type - uncomment line below and comment the 2 above
// $result = pg_execute($dbconn, "my_query", array(json_encode($tag)));
or if you defined $column as text[] which is legal in postgresql as array the code should read
$tag = array('item1', 'item2', 'item3');
// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", "INSERT INTO $table ($column) VALUES ($1)");
// Execute the prepared query. Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$tmp = json_encode($tag);
$tmp[0] = '{';
$tmp[strlen($tmp) - 1] = '}';
$result = pg_execute($dbconn, "my_query", array($tmp));
You can try to serialize it:
$tag = array('item1', 'item2', 'item3');
$tag = serialize($tag);
// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", "INSERT INTO $table ($column) VALUES ($1)");
// Execute the prepared query. Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", $tag);
Then when you want to get it from the DB as a PHP array, unserialize it.
