使用PDO将行插入PGSQL

Question

I cannot get php postgre to do an insert a row from a php array new_address, using this code:

    $customer_id = '2319';
    $use_frequency = 1;

    $sth = $dbh->prepare("
    INSERT INTO address 
            ('storeid', 
             'classtypeid', 
             'modifiedbyuser', 
             'modifiedbycomputer', 
             'modifieddate', 
             'seqid', 
             'issystem', 
             'isactive', 
             'streetaddress1', 
             'streetaddress2', 
             'city', 
             'state', 
             'county', 
             'postalcode', 
             'country', 
             'formattedtext', 
             'taxclassid', 
             'isvalidated', 
             'validatedaddress', 
             'hasvalidationerror', 
             'validationerror', 
             'customer_id', 
             'use_frequency') 
   VALUES   ( NULL, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
             :address_1, 
             :address_2, 
             :city, 
             :state, 
              NULL, 
             :zip, 
             :country, 
             :formatted_text, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
              NULL, 
             :customer_id, 
             :use_frequency");



$sth->execute(array(
    ':address_1' => $new_address['address_1'],
    ':address_2' => $new_address['address_2'],
    ':city' => $new_address['city'],
    ':state' => $new_address['state'],
    ':zip' => $new_address['zip'],
    ':country' =>$new_address['country'],
    ':formatted_text' => $formatted_text,
    ':customer_id' => $customer_id, 
    ':use_frequency' => $use_frequency
    );           


$sth->execute();

The last column in the table is id and it is a serial so I have omitted it, thinking it will auto-increment, but please tell me if I'm wrong.

I am getting the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "'storeid'" LINE 3: ('storeid', ^' in

print_r($new_address); shows me:

Array (
[0] => stdClass Object (
[customer_id] => 9319
)
[1] => stdClass Object (
[address_1] => 1515 example st
)
[2] => stdClass Object (
[address_2] => box 1
)
[3] => stdClass Object (
[city] => town
)
[4] => stdClass Object (
[state] => ST
)
[5] => stdClass Object (
[zip] => 12345
)
[6] => stdClass Object (
[country] => US
)
)

Thanks for any advice!

Answer 1

According to 4.1. Lexical Structure, you must escape the column names with double quotes "

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

INSERT INTO address 
        ("storeid", 
         "classtypeid", 
         ...

Additionally, if you set the default values for the columns to NULL, you can omit them from the column list and use only those you really need

insert into address
    ("streetaddress1", 
     "streetaddress2", 
     "city", 
     "state", 
     ...)
values (:address_1, 
       :address_2, 
       :city, 
       :state, 
       ...)

From your comment, you must modify the $new_address array. It is indexed numerically and not by name.

If you can change the JSON to

{ "customer_id": 9319,
  "address_1": "1515 example trail",
  "address_2": "box 1",
  "city": "town city",
  "state": "MI",
  "zip": "12345",
  "country": "US" }

you can use

$new_address = json_decode($json, true);

to get an associative array.

If you cannot change the JSON, you must map it to an associative array

$json = json_decode('[ { "customer_id": 9319 }, { "address_1": "1515 example trail" }, { "address_2": "box 1" }, { "city": "town city" }, { "state": "MI" }, { "zip": "12345" }, { "country": "US" } ]');

foreach ($json as $element) {
    foreach ($element as $key => $val) {
        $new_address[$key] = $val;
    }
}