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
