PHP postgresql: INSERT values from query array result

Question

I have some rows from this query statement:

SELECT * from jkd where idno='$id';

And I want to "copy" the values from those rows to other table:

INSERT INTO tmpjkd(pop, name, address) VALUES($pop, $name, $address);

I tried with this:

$jkd = SELECT * from jkd where idno='$id';
$sql = pg_query("SELECT * from jkd where idno='$id'");
while ($row = pg_fetch_object($sql)){
$pop = $jkd['pop'];
$name = $jkd['name'];
$address = $jkd['name'];
pg_query("INSERT INTO tmpjkd(pop, name, address) VALUES($pop, $name, $address)");}

But there's no luck. Please help me...

UPDATE: I wrote in drupals:

$sql = db_query("SELECT * from jkd where idno='$id'");
while ($row = db_fetch_object($sql)){
$pop = $jkd['pop'];
$name = $jkd['name'];
$address = $jkd['name'];
db_query("INSERT INTO tmpjkd(pop, name, address) VALUES($pop, $name, $address)");}

That's a bad way to build queries because you leave yourself wide open to SQL injection attacks. Try reading up on prepared statements, Postgres supports them and the PHP PDO Postgres driver does too — GordonM, Jun 08 '16 at 21:30
Also, why do you want to copy rows into another table? Perhaps you should read up on database normalization. You should be referencing the data you need from another table instead of copying it. Hope you receive this well. — Raphael Rafatpanah, Jun 08 '16 at 21:32
I need to copy values to temporary tables so I can process it further. About sql injection, is this safe to write on drupals? Actual code was upadted — bandungeuy, Jun 08 '16 at 21:41

score 2 · Accepted Answer · answered Jun 08 '16 at 21:39

Your code needs some changes

$sql = pg_query("SELECT * from jkd where idno=$id");
while ($row = pg_fetch_object($sql)) {
    $pop = $row->pop;
    $name = $row->name;
    $address = $row->address;
    pg_query("INSERT INTO tmpjkd(pop, name, address) VALUES ('$pop', '$name', '$address')");
}

pg_fetch_object is returning an object not an array, beside you you were using $jkd which doesn't do anything with actual values.

Also, the first line $jkd = SELECT * from jkd where idno='$id';, I don't know what it is used for, mostly you don't really need it.

score 0 · Answer 2 · answered Jun 10 '16 at 09:56

The solution proposed above does N + 1 queries. This will lead to performance problems. Why not using a CTE for that ?

$query = <<<SQL
WITH
  to_be_inserted AS (SELECT pop, name, address FROM jkd WHERE idno = $1)
INSERT INTO tmpjkd (pop, name, address)
  SELECT pop, name, address FROM to_be_inserted;
SQL;

pg_query_params($sql, [$id]);

Furthermore, this makes an atomic transaction.

PHP postgresql: INSERT values from query array result

2 Answers2