Thread: kinda newbie - ish question
hi all, Im trying to insert a record with a php script. I insert a NULL value to get my auto increment unique key to automatically populate as usual. Then I want to be able to insert another value in this same row which is a URL. This URL is basically a path plus a filename which I want to have the exact same name as the unique key that was just generated for the row. Is there a quick way is SQL to do this or will I have to handle it in my script? Thanks for helping! Mike
I'm taking a guess, but it sounds like you want to insert a row with a URL plus the ID of the record the URL is stored under? If so, there are a couple ways you can accomplish this. Take a look at create sequence in the docs for an explanation. One method is not to create the table with a serial ID. Create it with an int and use a manual sequence. That way you get the ID value before inserting the row. A bit more work to set up the table, but less work later. If you want to stick with the serial type, you'll have to use pg_getlastoid and look up the ID of that row. Less work setting up the table, but more work in your scripts. Here's an example $check=pg_Exec($connection,"create table mytable(my_id serial primary key,my_url char(50);"); $check=pg_Exec($connection,"INSERT INTO mytable(my_url) values(null);"); $oid=pg_GetLastOid($check); $idrow=pg_Exec($connection,"SELECT my_id FROM mytable WHERE oid='$oid';"); $id=pg_Result($idrow,0,'my_id'); $url=$url.$id; $check=pg_Exec($connection,"UPDATE mytable SET my_url='$url' WHERE my_id='$id';"); Of course, you could also use WHERE oid='$oid' instead. Just remember, you can't count on an OID remaining the same all the time. Don't try to use OIDs like a primary key. Every time postgres is started the oid could point to a different object or even no object at all. Quite safe to use in the above manner though. "Mike Judkins" <mmacie@earthlink.net> wrote in message news:d44ca591.0110081831.e820dba@posting.google.com... > hi all, > > Im trying to insert a record with a php script. I insert a NULL value > to get my auto increment unique key to automatically populate as > usual. Then I want to be able to insert another value in this same row > which is a URL. This URL is basically a path plus a filename which I > want to have the exact same name as the unique key that was just > generated for the row. Is there a quick way is SQL to do this or will > I have to handle it in my script? > > Thanks for helping! > > Mike
Use select nextval('sequence_name') to get the unique ID from the sequence,
then put it in both queries via a PHP variable.
----- Original Message -----
From: "Mike Judkins" <mmacie@earthlink.net>
To: <pgsql-general@postgresql.org>
Sent: Monday, October 08, 2001 10:31 PM
Subject: [GENERAL] kinda newbie - ish question
> hi all,
>
> Im trying to insert a record with a php script. I insert a NULL value
> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?
>
> Thanks for helping!
>
> Mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
mmacie@earthlink.net (Mike Judkins) writes: > to get my auto increment unique key to automatically populate as > usual. Then I want to be able to insert another value in this same row > which is a URL. This URL is basically a path plus a filename which I > want to have the exact same name as the unique key that was just > generated for the row. Is there a quick way is SQL to do this or will > I have to handle it in my script? Why not leave the unique ID part out of the stored URL and then combine them when you fetch from the DB? It would be something like SELECT urlprefix || id::text FROM mytable WHERE <whatever> (That's assuming the ID is non-text, like a SERIAL) -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Mike,
Perhaps you could just insert the url into the record and then
use a string function to return "url"+"id" when needed. This would
eliminate the extra insert and save space in the row.
Dale
> -----Original Message-----
> From: mmacie@earthlink.net [mailto:mmacie@earthlink.net]
> Sent: Monday, October 08, 2001 9:32 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] kinda newbie - ish question
>
>
> hi all,
>
> Im trying to insert a record with a php script. I insert a NULL value
> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?
>
> Thanks for helping!
>
> Mike
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
On Mon, Oct 08, 2001 at 07:31:36PM -0700, Mike Judkins wrote:
> Im trying to insert a record with a php script. I insert a NULL value
> to get my auto increment unique key to automatically populate as
> usual. Then I want to be able to insert another value in this same row
> which is a URL. This URL is basically a path plus a filename which I
> want to have the exact same name as the unique key that was just
> generated for the row. Is there a quick way is SQL to do this or will
> I have to handle it in my script?
update tbl set fn = fileprefix || currval('tbl_id_seq')
where id = currval('tbl_id_seq')
This must be in the same TX as first insert.
--
marko
http://php.net/pg_getlastoid This is the *ONLY* time you should actually use OIDs -- To get the unique value you just inserted. -- WARNING richard@zend.com address is an endangered species -- Use ceo@l-i-e.com Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm Volunteer a little time: http://chatmusic.com/volunteer.htm ----- Original Message ----- From: Mike Judkins <mmacie@earthlink.net> To: <pgsql-general@postgresql.org> Sent: Monday, October 08, 2001 9:31 PM Subject: [GENERAL] kinda newbie - ish question > hi all, > > Im trying to insert a record with a php script. I insert a NULL value > to get my auto increment unique key to automatically populate as > usual. Then I want to be able to insert another value in this same row > which is a URL. This URL is basically a path plus a filename which I > want to have the exact same name as the unique key that was just > generated for the row. Is there a quick way is SQL to do this or will > I have to handle it in my script? > > Thanks for helping! > > Mike > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org