[PHP-DB] Assigning the current value of a sequence to a variable - Postgres 8.3.6
Hello,
I have a program that looks for a name in a database. If the name is
found the id of the record is assigned to a variable. Later in the
program that value is used to insert records associated with the name
into the database using a variable called person_id. If the name is
not found, then a new record is build from data entered into a form.
There is a name record and a contact information record and a bridge
table record to create the relationship between them. After that, the
program may create additional records related to the new person record
in the same way it creates records related to the person records that
might already exist in the database. It would make this process lots
easier if I can assign currval of the new person record to the same
variable that I use when I already have a record in the database. Can
I assign currval to a variable?
Below is the code that I'm using. The value that displays for person
id which I'm trying to capture at the bottom of this code is person
id is Resource id #6.
if (($submit_db_name == Submit) ($submit_new_name == Submit))
{
echop Contact Locator: $cont_loc/p;
echop Contact Type Rank:
$cont_rank/p;
echop Contact Info Type:
$contact_type/p;
echop New name string:
$f_name_new/p;
echop New name string:
$m_name_new/p;
echop New name string:
$l_name_new/p;
echop New ivl web string:
$ivl_web_peop/p;
echop New cns_web string:
$cns_web_peop/p;
echop New contact rank string:
$cont_rank/p;
echop New contact locator string:
$cont_loc/p;
echop New contact item string:
$contact_info1/p;
echop New contact type string:
$contact_type/p;
begin;
$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);
$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);
$query = SELECT
currval('\tblPeople_peopleId_seq\');
$person_id = pg_query($query) or die(Can't
execute 4th query);
echo person id is . $person_id . br /;
commit;
This is PostgreSQL 8.3.6, PHP 5, on Solaris 10.
Thanks for your time.
Carol
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Assigning the current value of a sequence to a variable - Postgres 8.3.6
Hello,
you're not fetching the query results.
$query = SELECT currval('\tblPeople_peopleId_seq\');
$result = pg_query($query) or die(Can't execute 4th query);
if ($result != false) {
$row = pg_fetch_row($result);
echo person id is . $row[0] . br /;
}
else {
// no curval() found or something bad happened...
}
This should work for you. If you run SELECT query, you have to fetch the
results before you can access the data. If you expect more than one row,
you can use
while($row = pg_fetch_row($result)) {
// code to process data from database
}
Hope this will help you :)
Carol Walter napsal(a):
Hello,
I have a program that looks for a name in a database. If the name is
found the id of the record is assigned to a variable. Later in the
program that value is used to insert records associated with the name
into the database using a variable called person_id. If the name is
not found, then a new record is build from data entered into a form.
There is a name record and a contact information record and a bridge
table record to create the relationship between them. After that, the
program may create additional records related to the new person record
in the same way it creates records related to the person records that
might already exist in the database. It would make this process lots
easier if I can assign currval of the new person record to the same
variable that I use when I already have a record in the database. Can
I assign currval to a variable?
Below is the code that I'm using. The value that displays for person
id which I'm trying to capture at the bottom of this code is person
id is Resource id #6.
if (($submit_db_name == Submit) ($submit_new_name == Submit))
{
echop Contact Locator: $cont_loc/p;
echop Contact Type Rank: $cont_rank/p;
echop Contact Info Type: $contact_type/p;
echop New name string: $f_name_new/p;
echop New name string: $m_name_new/p;
echop New name string: $l_name_new/p;
echop New ivl web string: $ivl_web_peop/p;
echop New cns_web string: $cns_web_peop/p;
echop New contact rank string:
$cont_rank/p;
echop New contact locator string:
$cont_loc/p;
echop New contact item string:
$contact_info1/p;
echop New contact type string:
$contact_type/p;
begin;
$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);
$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);
$query = SELECT
currval('\tblPeople_peopleId_seq\');
$person_id = pg_query($query) or die(Can't
execute 4th query);
echo person id is . $person_id . br /;
commit;
This is PostgreSQL 8.3.6, PHP 5, on Solaris 10.
Thanks for your time.
Carol
--
S pozdravem
Daniel Tlach
Freelance webdeveloper
Email: m...@danaketh.com
ICQ: 160914875
MSN: danak...@hotmail.com
Jabber: danak...@jabbim.cz
Fwd: [PHP-DB] Assigning the current value of a sequence to a variable - Postgres 8.3.6
From: Carol Walter walt...@indiana.edu
Date: May 18, 2009 4:12:16 PM GMT-04:00
To: danaketh danak...@gmail.com
Subject: Re: [PHP-DB] Assigning the current value of a sequence to a
variable - Postgres 8.3.6
Hello,
Oh, of course. Sorry for the silly question.
Thanks for your help.
Carol
On May 18, 2009, at 3:45 PM, danaketh wrote:
Hello,
you're not fetching the query results.
$query = SELECT currval('\tblPeople_peopleId_seq\');
$result = pg_query($query) or die(Can't execute 4th query);
if ($result != false) {
$row = pg_fetch_row($result);
echo person id is . $row[0] . br /;
}
else {
// no curval() found or something bad happened...
}
This should work for you. If you run SELECT query, you have to
fetch the results before you can access the data. If you expect
more than one row, you can use
while($row = pg_fetch_row($result)) {
// code to process data from database
}
Hope this will help you :)
Carol Walter napsal(a):
Hello,
I have a program that looks for a name in a database. If the name
is found the id of the record is assigned to a variable. Later in
the program that value is used to insert records associated with
the name into the database using a variable called person_id. If
the name is not found, then a new record is build from data
entered into a form. There is a name record and a contact
information record and a bridge table record to create the
relationship between them. After that, the program may create
additional records related to the new person record in the same
way it creates records related to the person records that might
already exist in the database. It would make this process lots
easier if I can assign currval of the new person record to the
same variable that I use when I already have a record in the
database. Can I assign currval to a variable?
Below is the code that I'm using. The value that displays for
person id which I'm trying to capture at the bottom of this code
is person id is Resource id #6.
if (($submit_db_name == Submit) ($submit_new_name == Submit))
{
echop Contact Locator: $cont_loc/p;
echop Contact Type Rank: $cont_rank/
p;
echop Contact Info Type:
$contact_type/p;
echop New name string: $f_name_new/p;
echop New name string: $m_name_new/p;
echop New name string: $l_name_new/p;
echop New ivl web string:
$ivl_web_peop/p;
echop New cns_web string:
$cns_web_peop/p;
echop New contact rank string:
$cont_rank/p;
echop New contact locator string:
$cont_loc/p;
echop New contact item string:
$contact_info1/p;
echop New contact type string:
$contact_type/p;
begin;
$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);
$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);
$query = SELECT
currval('\tblPeople_peopleId_seq\');
$person_id = pg_query($query) or
die(Can't execute 4th query);
echo person id is . $person_id .
br /;
commit;
This is PostgreSQL 8.3.6, PHP 5, on Solaris 10.
Thanks for your time.
Carol
--
S pozdravem
Daniel Tlach
Freelance webdeveloper
Email: m...@danaketh.com
ICQ: 160914875
MSN: danak...@hotmail.com
Jabber: danak...@jabbim.cz
