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
