Re: [PHP-DB] PHP & Postgres - query not writing to database.
[email protected] (Carol Walter)
| Newsgroups | php.db |
|---|---|
| Message-ID | <[email protected]> |
To all who helped.
Thank you very much. I found the problem. There is a leading blank
on the last name field that I couldn't see. It was coming from my
program code.
Thanks for all your help.
Carol
On May 11, 2009, at 4:23 PM, Carol Walter wrote:
> I have copied the queries into psql and wrapped them in a BEGIN and
> COMMIT. Even from psql the queries appear to work but don't store
> the information. There don't appear to be errors in the log either.
>
> Thanks for your help,
>
> Carol
>
> km_tezt=# begin;
> BEGIN
> km_tezt=# INSERT INTO "tblPeople"("fName","mName","lName", ivlweb,
> cnsweb) VALU
> ES ('Frank', 'D',' Oz', 't', 't');
> INSERT 0 1
> km_tezt=# INSERT INTO
> "tblContactInformation"("contactItem","contactType") VALU
> ES ('[email protected]','0010');
> INSERT 0 1
> km_tezt=# INSERT INTO
> "brdgPeopleContactInformation" ("peopleId","contactInform
> ationId",rank, type) VALUES
> (currval('"tblPeople_peopleId_seq"'),currval('"tblC
> ontactInformation_contactInformationId_seq"'), '1', '100');
> INSERT 0 1
> km_tezt=# commit;
> COMMIT
> km_tezt=# select * from "tblPeople" where "lName" like 'O%';
> peopleId | fName | mName | lName | ivlweb | cnsweb
> ----------+---------+-------+--------+--------+--------
> 404 | Ilka | | Ott | t | t
> 410 | Elinor | | Ostrom | t | t
> 374 | Gregory | | O'Hare | t | t
> 33 | Terry | J. | Ord | t | t
> (4 rows)
>
> On May 10, 2009, at 4:41 AM, danaketh wrote:
>
>> I'd suggest you to copy the echoed queries and run them directly in
>> terminal (if you have access). Also if you have remote access to
>> the database and can use tools like pgAdmin or Navicat, that could
>> help you with testing. Or send me the table structure and I'll try
>> them myself ;)
>>
>> Carol Walter napsal(a):
>>>
>>> Hello,
>>>
>>> I have a PHP program that contains a number of postgres queries.
>>> At the end of the program, it needs to write data to a database.
>>> You can see the code that I'm using below. I have "die" clauses
>>> on all the queries and I have the program echoing the queries that
>>> it runs to the screen. The "die" clause does not execute. The
>>> queries are echoed to the screen, but nothing is being written to
>>> the database. There don't appear to be any errors in the postgres
>>> log or the php log. Is there a function that I can use that will
>>> tell me exactly what is going on here? If there is, can you give
>>> me the syntax?
>>>
>>> Thanks in advance for your time.
>>>
>>> Carol
>>>
>>> P.S. This PHP 5 and PostgreSQL 8.3.6 on Solaris 10.
>>>
>>> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>> +++++++++++++++++++
>>> I've written a query that needs to insert data into two base
>>> tables and a bridge table. The code looks like...
>>>
>>> /* Echo data for database to the screen */
>>> echo"<p> Contact Locator: $cont_loc</p>";
>>> echo"<p> Contact Type Rank: $cont_rank</p>";
>>> echo"<p> Contact Info Type: $contact_type</p>";
>>> echo"<p> New name string: $f_name_new</p>";
>>> echo"<p> New name string: $m_name_new</p>";
>>> echo"<p> New name string: $l_name_new</p>";
>>> echo"<p> New ivl web string: $ivl_web_peop</p>";
>>> echo"<p> New cns_web string: $cns_web_peop</p>";
>>> echo"<p> New contact rank string: $cont_rank</p>";
>>> echo"<p> New contact locator string: $cont_loc</p>";
>>> echo"<p> New contact item string: $contact_info1</p>";
>>> echo"<p> New contact type string: $contact_type</p>";
>>>
>>> /* Connect to database */
>>> include "connect_km_tezt.php";
>>> /* Run
>>> queries */
>>> $query = "INSERT INTO \"tblPeople\"(\"fName\",\"mName\",
>>> \"lName\", ivlweb, cnsweb)
>>> VALUES ('$f_name_new',
>>> '$m_name_new',' $l_name_new', '$ivl_web_peop', '$cns_web_peop')";
>>> echo "First query: " . $query . "<br />";
>>> $pg_peop_ins = pg_query($query) or die("Can't execute first
>>> query");
>>> // echo pg_last_error("Last Error " .
>>> $pg_peop_ins);
>>> // echo pg_result_error($pg_peop_ins);
>>>
>>> $query = "INSERT INTO \"tblContactInformation
>>> \"(\"contactItem\",\"contactType\")
>>> VALUES
>>> ('$contact_info1','$contact_type')";
>>> echo "Second query: " . $query . "<br />";
>>> $pg_contact_ins = pg_query($query) or die("Can't execute
>>> 2nd query");
>>> $query = "INSERT INTO \"brdgPeopleContactInformation\"
>>> (\"peopleId\",
>>> \"contactInformationId\",rank, type)
>>> VALUES
>>> (currval('\"tblPeople_peopleId_seq
>>> \"'),currval('\"tblContactInformation_contactInformationId_seq
>>> \"'), '$cont_rank', '$cont_loc')";
>>> echo "Third query: " . $query . "<br />";
>>> $pg_peop_cont_ins = pg_query($query) or die("Can't execute
>>> 3rd query");
>>>
>>>
>>> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>> ++++++++++++++++++++++++++++++++++++++++++++++
>>> The postgres log looks like this ...
>>>
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]STATEMENT: INSERT
>>> INTO "tblPeople"("fName","mName","lName", ivlweb, cnsweb)
>>> VALUES ('Frank',
>>> 'D',' Oz', 't', 't')
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: duration:
>>> 105.005 ms
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: PLANNER
>>> STATISTICS
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]DETAIL: ! system
>>> usage stats:
>>> ! 0.000051 elapsed 0.000050 user 0.000006 system sec
>>> ! [0.064533 user 0.013546 sys total]
>>> ! 0/2 [2976/197116] filesystem blocks in/out
>>> ! 0/0 [44325584/1] page faults/reclaims, 0
>>> [465469248] swaps
>>> ! 0 [0] signals rcvd, 1/3 [2/5] messages rcvd/sent
>>> ! 10/0 [-64186124/0] voluntary/involuntary context
>>> switches
>>> ! buffer usage stats:
>>> ! Shared blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Local blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Direct blocks: 0 read, 0 written
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]STATEMENT: INSERT
>>> INTO "tblContactInformation"("contactItem","contactType")
>>> VALUES ('[email protected]
>>> ','0010')
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: duration:
>>> 10.856 ms
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: PLANNER
>>> STATISTICS
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]DETAIL: ! system
>>> usage stats:
>>> ! 0.000028 elapsed 0.000027 user 0.000003 system sec
>>> ! [0.066803 user 0.014689 sys total]
>>> ! 0/4 [670957800/197124] filesystem blocks in/out
>>> ! 0/0 [453276800/1] page faults/reclaims, 0 [46] swaps
>>> ! 0 [0] signals rcvd, 1/3 [2/5] messages rcvd/sent
>>> ! 13/0 [-63801094/0] voluntary/involuntary context
>>> switches
>>> ! buffer usage stats:
>>> ! Shared blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Local blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Direct blocks: 0 read, 0 written
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]STATEMENT: INSERT
>>> INTO "brdgPeopleContactInformation"
>>>
>>> ("peopleId","contactInformationId",rank, type)
>>> VALUES
>>> (currval
>>> ('"tblPeople_peopleId_seq
>>> "'),currval('"tblContactInformation_contactInformationId_seq"'),
>>> '1', '100')
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: PLANNER
>>> STATISTICS
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]DETAIL: ! system
>>> usage stats:
>>> ! 0.000952 elapsed 0.000560 user 0.000392 system sec
>>> ! [0.069269 user 0.016204 sys total]
>>> ! 387067968/4 [404115672/537002508] filesystem blocks
>>> in/out
>>> ! 480502400/0 [506016384/1] page faults/reclaims,
>>> -44323495 [58] swaps
>>> ! 0 [0] signals rcvd, 1/3 [2/5] messages rcvd/sent
>>> ! 63137568/1474900 [13/1474900] voluntary/involuntary
>>> context switches
>>> ! buffer usage stats:
>>> ! Shared blocks: 7 read, 0 written,
>>> buffer hit rate = 46.15%
>>> ! Local blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Direct blocks: 0 read, 0 written
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]CONTEXT: SQL
>>> statement "SELECT 1 FROM ONLY "public"."tblContactInformation" x
>>> WHERE "contactInformationId" OPERATOR(pg_catalog.=) $1 FOR SHARE
>>> OF x"
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]STATEMENT: INSERT
>>> INTO "brdgPeopleContactInformation"
>>>
>>> ("peopleId","contactInformationId",rank, type)
>>> VALUES
>>> (currval
>>> ('"tblPeople_peopleId_seq
>>> "'),currval('"tblContactInformation_contactInformationId_seq"'),
>>> '1', '100')
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: PLANNER
>>> STATISTICS
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]DETAIL: ! system
>>> usage stats:
>>> ! 0.000193 elapsed 0.000168 user 0.000025 system sec
>>> ! [0.070047 user 0.016455 sys total]
>>> ! 0/4 [17047704/537002516] filesystem blocks in/out
>>> ! 0/0 [25513984/1] page faults/reclaims, 0 [44323553]
>>> swaps
>>> ! 0 [0] signals rcvd, 1/3 [2/5] messages rcvd/sent
>>> ! 24576/0 [-62990099/0] voluntary/involuntary context
>>> switches
>>> ! buffer usage stats:
>>> ! Shared blocks: 1 read, 0 written,
>>> buffer hit rate = 80.00%
>>> ! Local blocks: 0 read, 0 written,
>>> buffer hit rate = 0.00%
>>> ! Direct blocks: 0 read, 0 written
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]CONTEXT: SQL
>>> statement "SELECT 1 FROM ONLY "public"."tblPeople" x WHERE
>>> "peopleId" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]STATEMENT: INSERT
>>> INTO "brdgPeopleContactInformation"
>>>
>>> ("peopleId","contactInformationId",rank, type)
>>> VALUES
>>> (currval
>>> ('"tblPeople_peopleId_seq
>>> "'),currval('"tblContactInformation_contactInformationId_seq"'),
>>> '1', '100')
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: duration:
>>> 23.150 ms
>>> [km_app_admin:km_tezt:2009-05-08 10:34:49 EDT]LOG: disconnection:
>>> session time: 0:00:00.310 user=km_app_admin database=km_tezt
>>> host=ella.slis.indiana.edu port=40356
>>> bash-3.00#
>>>
>>>
>>
>> --
>>
>> S pozdravem
>>
>> Daniel Tlach
>> Freelance webdeveloper
>>
>> Email: [email protected]
>> ICQ: 160914875
>> MSN: [email protected]
>> Jabber: [email protected]
>