Re: PHP & Postgres - query not writing to database.

danaketh <danaketh@xxxxxxxxx> · Tue, 12 May 2009 00:16:37 +0200

Glad that you found the problem :)

Carol Walter napsal(a):
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 ('foz@xxxxxxxxxxx','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 
('foz@xxxxxxxxxxx','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: mail@xxxxxxxxxxxx
ICQ: 160914875
MSN: danaketh@xxxxxxxxxxx
Jabber: danaketh@xxxxxxxxx

--

S pozdravem

Daniel Tlach
Freelance webdeveloper

Email: mail@xxxxxxxxxxxx
ICQ: 160914875
MSN: danaketh@xxxxxxxxxxx
Jabber: danaketh@xxxxxxxxx