Re: [PHP-DB] getting serial in PHP
kevin myers wrote:
well right after you insert it you can pull it right back out again, or
i think you can do :
$query = insert into logs values
((nextval('public.logs_log_id_seq'::text) as
$log_id),$id,'$logtext','now','$_SERVER[PHP_AUTH_USER]');;
and then use it that way, or maybe you can do this:
$log_id = nextval('public.logs_log_id_seq'::text);
and then insert it that way
From: Roger 'Rocky' Vetterberg [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [PHP-DB] getting serial in PHP
Date: Fri, 28 Mar 2003 13:46:36 +0100
Hi list.
I have no real education in PHP or postgre, so forgive me if I dont
use the correct language or expressions.
I have a small problem that is probably easily solved if you just know
how to do it.
I have a PHP interface over a postgreSQL database, where I can search,
add, delete or modify records.
Now, when adding a new record, a query could look something like this:
$query = insert into logs values
(nextval('public.logs_log_id_seq'::text),$id,'$logtext','now','$_SERVER[PHP_AUTH_USER]');;
The first value, the nextval thingie (if someone could give me the
correct term for such a feature I would appriciate it) gives the added
row a unique serial number. How do I figure out the serial my newly
added row was assigned?
What I want to do is something like
'echo Your submission was accepted and assigned log ID $log_id; '
directly below the $query line. How do I get the correct value into
$log_id?
Hopefully, someone understands my confused rantings and can give me
directions to some documentation or maybe even the name of a variable
I can use. :)
TIA
--
R
I just cant figure this out.
Another user directed me to
http://www.php.net/manual/en/function.pg-last-oid.php, which gave me a
few pointers.
If I understand it correctly, something like this should work:
if (empty($id)) {
die(No id specified, can not continue);
}
else {
$query = insert into logs values
(nextval('public.logs_log_id_seq'::text),$id,'$logtext','now','$_SERVER[PHP_AUTH_USER]');
}
if($result = pg_exec($connection, $query)) {
echo oid : .pg_last_oid($result);
}
However, it doesnt work. The data is added with a correct serial, but
it still prints just oid:
About the second suggestion, just pulling the data back out; I could
do that, but the only thing that is unique in the string is the
timestamp. Theoretically, on a busy site even that could be identical
in two rows. (Since timestamp is in one millionth of a second, its not
very likely but could happen)
So if I do a search on the data I just inserted, I could get two or
more hits, and there would be no way for me to tell which one is the
one Im after.
Im new to both php and sql, but I keep thinking things like this must
be done every day on thousands of sites. I just cant figure out how to
do it, any suggestions?
TIA
--
R
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] getting serial in PHP
- Original Message -
From: Roger 'Rocky' Vetterberg [EMAIL PROTECTED]
To: kevin myers [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, March 31, 2003 4:52 AM
Subject: Re: [PHP-DB] getting serial in PHP
[snip]
From: Roger 'Rocky' Vetterberg [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [PHP-DB] getting serial in PHP
Date: Fri, 28 Mar 2003 13:46:36 +0100
Hi list.
I have no real education in PHP or postgre, so forgive me if I dont
use the correct language or expressions.
I have a small problem that is probably easily solved if you just know
how to do it.
I have a PHP interface over a postgreSQL database, where I can search,
add, delete or modify records.
Now, when adding a new record, a query could look something like this:
$query = insert into logs values
(nextval('public.logs_log_id_seq'::text),$id,'$logtext','now','$_SERVER[PHP_
AUTH_USER]');;
The first value, the nextval thingie (if someone could give me the
correct term for such a feature I would appriciate it) gives the added
row a unique serial number. How do I figure out the serial my newly
added row was assigned?
What I want to do is something like
'echo Your submission was accepted and assigned log ID $log_id; '
directly below the $query line. How do I get the correct value into
$log_id?
Hopefully, someone understands my confused rantings and can give me
directions to some documentation or maybe even the name of a variable
I can use. :)
TIA
--
R
[Snip]
About the second suggestion, just pulling the data back out; I could
do that, but the only thing that is unique in the string is the
timestamp. Theoretically, on a busy site even that could be identical
in two rows. (Since timestamp is in one millionth of a second, its not
very likely but could happen)
So if I do a search on the data I just inserted, I could get two or
more hits, and there would be no way for me to tell which one is the
one Im after.
Im new to both php and sql, but I keep thinking things like this must
be done every day on thousands of sites. I just cant figure out how to
do it, any suggestions?
What I do, when I need to reference an ID from a sequence after I do the
INSERT, is I get the next number in the sequence /before/ inserting the data
into the table, like so:
$res = pg_query($conn, select nextval('my_seq'););
$row = pg_fetch_array($res);
$id = $row['nextval'];
$res = pg_query($conn, insert into my_table values ($id, 'value',
'value'););
//Error checking here...
echo Your submission was accepted with ID $id;
Since you are just getting the next value of the sequence, your data is safe
from having non-unique id numbers, as long as you always call nextval() in
order to get your id number. Any subsequent calls to nextval() will return
the next number, regardless of whether you use the $id returned by the first
query.
Hope that helps.
Nathaniel Price http://www.thornvalley.com
Who is General Failure and why is he reading my hard drive?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] getting serial in PHP
Nathaniel Price wrote:
- Original Message -
From: Roger 'Rocky' Vetterberg [EMAIL PROTECTED]
To: kevin myers [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, March 31, 2003 4:52 AM
Subject: Re: [PHP-DB] getting serial in PHP
*snip*
What I do, when I need to reference an ID from a sequence after I do the
INSERT, is I get the next number in the sequence /before/ inserting the data
into the table, like so:
$res = pg_query($conn, select nextval('my_seq'););
$row = pg_fetch_array($res);
$id = $row['nextval'];
$res = pg_query($conn, insert into my_table values ($id, 'value',
'value'););
//Error checking here...
echo Your submission was accepted with ID $id;
Since you are just getting the next value of the sequence, your data is safe
from having non-unique id numbers, as long as you always call nextval() in
order to get your id number. Any subsequent calls to nextval() will return
the next number, regardless of whether you use the $id returned by the first
query.
Hope that helps.
Nathaniel Price http://www.thornvalley.com
Who is General Failure and why is he reading my hard drive?
So even if I just do a select on nextval, it increases in value?
I was under the impression that a select would just return the
current value, and that I would have to do a insert to actually
increase it?
I will certainly test this, it sounds like this could be the
solution Im looking for.
Thanks!
--
R
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
