Error when inserting data to postgresql with php

Question

I am trying to insert some data into a table in a PostgreSQL database. I use prepared statements for this purpose like:

$db = new PDO('pgsql:dbname=wind;user=postgres;password=1249;host=localhost;port=5432');
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

for ($x = 0; $x < sizeof($arrayAddress); $x++) {
   $stmt = $db->prepare("INSERT INTO geocoding_test (address,googlemaps) VALUES (address=:address, sucUn=:sucUn ) ");
   $stmt->execute(array(address => $arrayAddress[$x], sucUn => $arraySucUnsuc[$x] ));
}

...where arrayAddress is an array containing all the addresses and arraySucUnsuc is an array containing some string.

When I execute this I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 
    SQLSTATE[42703]: Undefined column: 7 ERROR: column 'address' does not exist 
    LINE 1: ... INTO geocoding_test (address,googlemaps) VALUES (address=$1... ^
    HINT: There is a column named 'address' in table 'geocoding_test', but it cannot be
    referenced from this part of the query.' in C:\MAMP\htdocs\Wind\predictVdslEligibilityWSLog-20150614\myWebCrawler.php:98
    Stack trace:
    #0 C:\MAMP\htdocs\Wind\predictVdslEligibilityWSLog-20150614\myWebCrawler.php(98): PDOStatement-&gt;execute(Array)
    #1 {main} thrown in C:\MAMP\htdocs\Wind\predictVdslEligibilityWSLog-20150614\myWebCrawler.php on line98

What am I doing wrong?

Also this is the definition of my table in the database:

CREATE TABLE geocoding_test
(
    id serial NOT NULL,
    address character varying(600),
    googlemaps character varying(50)
)

Marc B · Accepted Answer · 2015-07-15 14:48:32Z

4

Insert syntax is:

INSERT INTO tablename (fieldname1, ...) VALUES (value1, ...)

You have

... VALUES (address=:address, ...

where you're trying to COMPARE (=) an existing value address against the parameter you're passing. Since this is an INSERT query, that address1 value doesn't exist yet: you can't read from what doesn't exist.

The query should be just

.... VALUES(:address, :sucUn)

answered Jul 15, 2015 at 14:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

user1919 Over a year ago

Sometimes I wonder how people can make stupid mistakes like this...and spending one hour trying to figure out what's wrong. Thanks a lot!

Marc B Over a year ago

Mental roadblocks. I've spent HOURS staring at a few lines of code wondering why it won't work, then realizing that I'd put in a ; to terminate a statement, which is illegal in the particular language I was using.

Collectives™ on Stack Overflow

Error when inserting data to postgresql with php

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related