insert into postgres db with for php statement

Question

I'm writing a code with this function:

function foo() {

    $input = array('bar');

    for ($i=0;$i<1000;$i++) {

        $db=connection_pgsql() or die('Connessione al DBMS non riuscita');

            pg_prepare( "run","INSERT INTO tab1(name) VALUES  ($1)");
            pg_execute("run",$input);

            pg_prepare( "run","INSERT INTO tab2(name) VALUES  ($1)");
            pg_execute("run",$input);

            pg_prepare( "run","INSERT INTO tab3(name) VALUES  ($1)");
            pg_execute("run",$input);

        pg_close($db);


    }
}

After the execution of this function there are only

20 rows in tab1, 20 rows in tab2 and 20 rows in tab3

and not

1000 rows in tab1, 1000 rows in tab2 and 1000 rows in tab3.

Is it a problem of php or postgresql configuration?

I don't know php but you need to inspect the results of those inserts. Could be failing a constraint or something — JoshRagem
– JoshRagem, Commented Dec 11, 2016 at 2:27

Community · Accepted Answer · 2017-05-23 12:30:32Z

0

You're not checking for errors.

Check the return value of functions and use the error info functions provided by PHP.

See:

You can also check the PostgreSQL error logs.

It's much clearer if you use PDO; see e.g. How is a Postgres "RAISE EXCEPTION" converted into a PDOException? .

Also, don't open and close connections each loop iteration like this, the performance will be awful. Preferably do all the work in a single transaction on a single connection.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Dec 11, 2016 at 4:35

Craig Ringer

328k83 gold badges740 silver badges818 bronze badges

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

4 Comments

Michele M Over a year ago

I printed for each row last error, result error and affecter rows. Last error and result error are nulls, affected rows is 1. I try with only 1 table insert in a for of 1000 statements. Only 130 are execute...

Vao Tsun Over a year ago

please update your question then (preferably with smaller iteration number - eg loops over 20 times, inserts only 19 rows) and result error is null or so.

Craig Ringer Over a year ago

Don't print the results, check for errors exit the loop. You can easily miss something when you're reading 1000 lines of output.

Michele M Over a year ago

there aren't error in loop. It only stops after n rows.

Vao Tsun · Accepted Answer · 2016-12-11 11:08:32Z

0

check the resource returned or at least if it is not false. Look at docs example, you should not just pg_execute("run",$input);, but

$result = pg_execute("run",$input);

so you can run

var_dump($result);

This way you will see full dump of value returned on your html page.

answered Dec 11, 2016 at 11:08

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

1 Comment

Michele M Over a year ago

var_dump($result) returns: resource(number of $i) of type (pgsql result)

Collectives™ on Stack Overflow

insert into postgres db with for php statement

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related