What am I doing wrong with pg_copy_from ?

[James Long <php@xxxxxxxxxxxxx>]

Hello!  I'm new to the list.  I'm learning php and PostgreSQL
basics.

I'm having a hard time finding a clean way to 

retrieve a table row;
edit it;
write it back to the table;

Ideally, I'd like to read the row as an associative array, display
an html page that will allow the user to edit thoe array cells (don't
have a handle on that yet), and then update the table row based on
the array as edited by the html client.

Right now, I'd settle for making this work:

I create a table:

DROP TABLE test;

CREATE TABLE test (
	field1		varchar(16),
	field2		integer,
	field3		date
);

INSERT INTO test VALUES ('field 1', 2, '3/3/2003' );

INSERT INTO test VALUES ('FIELD 1', 22, '3/3/2033' );

Great.  I now have:

 field1  | field2 |   field3   
---------+--------+------------
 field 1 |      2 | 2003-03-03
 FIELD 1 |     22 | 2033-03-03
(2 rows)

I point my web browser at this .php script:


<?php

$database = pg_connect( "dbname=james user=james" );

$row_id = "field 1";

$query_string = "select * from test where (field1 = '$row_id')";

echo $query_string . "<br>";

$result = pg_query( $database, $query_string );

if ($result) {

// How many fields are there?  Let there be $j fields
    $j = pg_num_fields( $result );
    echo $j . " fields found<br>";

// build an array containing that many fields, corresponding in order
// to the order in the SQL table row.  Let that array be called $rec
    $rec = array();

    $rec[ "field1" ] = $row_id;
    $rec[ "field2" ] = 20;
    $rec[ "field3" ] = '3-3-2003';
    print_r( $rec );
    echo "<br>";

// Delete the old record with this row_id
    $query_string = "delete from test where (field1 = '$row_id')";
    $result = pg_query( $database, $query_string );

// If delete succeeded, insert a row from our array 
    if ($result) {
      if (pg_copy_from( $database, "test", $rec )) {
        echo "pg_copy_from worked!<br>";
      } else {
        echo "pg_copy_from failed";
      }
    } else {
      echo "delete from test failed";
    }

} else {
    echo "select * from test failed";
}

pg_query( $database, "end" );

?>

... and it gives the output:

select * from test where (field1 = 'field 1')
3 fields found
Array ( [field1] => field 1 [field2] => 20 [field3] => 3-3-2003 ) 

Warning: pg_copy_from(): endcopy failed: . in /usr/home/james/public_html/test6.php on line 35
pg_copy_from failed


Now my table looks like:

 field1  | field2 |   field3   
---------+--------+------------
 FIELD 1 |     22 | 2033-03-03
(1 row)


So the delete happened okay, but I can't insert a row from my array.

What am I doing wrong?  I'm also game to try updating in place, but
I want to be cmofortable with both methods, either deleting (or otherwise
marking as obsolete) the old and inserting the new, or by just updating 
in place.  There's pg_update, but it is loudly flagged as experimental.
However, I don't see anything that purports to be the "traditional"
method for updating table rows.

All clues will be appreciated.

Regards,

Jim Long

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php