Thread

Jeff Davis <jdavis@starcrazy.net> — 2000-01-11T06:25:37Z

I'd like to report an error I found with the output of psql's select queries
in relation to the addition of rows using php 4.0 beta 3. When I insert a
row into the database using php's query function pg_exec() and then use a
terminal to run psql and use a select * from tablename query, the results
set is displayed incorrectly; however, the data stored in the database seems
to retain its integrity. I know this, because when I execute a php function
to grab the data row from postgresql (pg_fetch_array) the result is returned
to me correctly. I assumed that the problem existed within my query, so I
printed out the php query, copy and pasted it into psql, and when I
performed another select all, the results set was displayed coorectly. I
have included some examples of psql and what i did in PHP.

------------------------------------------------------------------------
This is the php code that adds the queries into the databases:
------------------------------------------------------------------------

$query="INSERT INTO products (itemnum, price, title, description, keywords,
img, categories, size) VALUES ('$itemnum', '$price', '$title',
'$description', '$keywords', '$img', '$categs', '$size');";
echo $query;
$pg_result = pg_exec($database_link, $query);

------------------------------------------------------------------------
This is an example of what was happening while in psql:
------------------------------------------------------------------------

Table    = products
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| itemnum                          | varchar[]                        |
var |
| price                            | money[]                          |
var |
| description                      | text                             |
var |
| keywords                         | text                             |
var |
| title                            | varchar()                        |
0 |
| categories                       | varchar[]                        |
var |
| img                              | varchar()                        |
6 |
| size                             | varchar[]                        |
var |
| pkey                             | int4 not null default nextval (  |
4 |
+----------------------------------+----------------------------------+-----
--+
Index:    products_pkey

groceries=> select * from products;
itemnum                        |price            |description     |keywords
|title   |categories               |img|size              |pkey
-------------------------------+-----------------+----------------+---------
--------------------------------+--------+-------------------------+---+----
--------------+----
","24 oz"}|  16  |{"12 oz,"$4.67"}|Like Wheat Thins|crackers, dry goods,
wheat thins, generic|Crackers|{"Dry Goods
(1 row)

groceries=> delete from products;
DELETE 1
groceries=> INSERT INTO products (itemnum, price, title, description,
keywords, img, categories, size) VALUES ('{"348574395873 ","34534534534"}',
'{"2.20 ","4.67"}', 'Crackers', 'Like Wheat Thins', 'crackers, dry goods,
wheat thins, generic', 'n', '{"Dry Goods ","Crackers"}', '{"12 oz ","24
oz"}');
INSERT 20128 1
groceries=> select * from products;
itemnum                        |price            |description     |keywords
|title   |categories               |img|size              |pkey
-------------------------------+-----------------+----------------+---------
--------------------------------+--------+-------------------------+---+----
--------------+----
{"348574395873 ","34534534534"}|{"$2.20","$4.67"}|Like Wheat Thins|crackers,
dry goods, wheat thins, generic|Crackers|{"Dry Goods ","Crackers"}|n  |{"12
oz ","24 oz"}|  17
(1 row)



------------------------------------------------------------------------
The first select * from was a php executed query, while the second was the
query string that php printed, which I copied into psql and did a second
select * from. As you can see, the later returned the correct result set.


Thank you, and I hope this helps,
Jeff Davis & Nathan Boley

Either of us can be contacted if anyone has any questions:
mailto:jdavis@genesiswd.com
mailto:nboley@genesiswd.com