----- Original Message -----
From: "Roger 'Rocky' Vetterberg" <listsub@401.cx>
To: "kevin myers" <kevandy1386@msn.com>; <php-db@lists.php.net>
Sent: Monday, March 31, 2003 4:52 AM
Subject: Re: getting serial in PHP
[snip]
> >> From: "Roger 'Rocky' Vetterberg" <listsub@401.cx>
> >> To: php-db@lists.php.net
> >> Subject: 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
