On Mon, 31 Mar 2003, Roger 'Rocky' Vetterberg wrote:
> [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?"
>
>
> For future reference, here is the code snippet that finally
> worked for me:
> --
> $res = pg_query($connection, "select
> nextval('public.logs_log_id_seq'::text);");
>
> $row = pg_fetch_array($res);
> $log_id = $row['nextval'];
>
> $query = "insert into logs values
> ($log_id,$id,'$logtext','now','$_SERVER[PHP_AUTH_USER]');";
> $result = pg_query($connection, $query) or die("Error in query:
> $query. " .pg_last_error($connection));
> --
>
> The data is stored, and the counter value is saved in $log_id and
> available for use.
>
> Thanks to all those that helped!
>
> --
> R
Let me suggest a shorthand for the above. Since the query only
returns one result, there is no need to fetch an array. Just use
pg_fetch_result to get the value. In fact, you can even nest the
calls, like so:
$log_id = pg_fetch_result(pg_query($connection, "select
nextval('public.logs_log_id_seq'::text);"), 0, 0)
or die(pg_result_error());
This saves a little bit of memory too, since you aren't making two extra
assignments to get your $log_id.
Next, just turn this into a nice little PHP function, so you can do
$log_id = getNextVal('sequencename');
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
