phplib-users

[Phplib-users] nextid - how does it work?

[Neha M. <ne...@sp...>]

Hello,

I'm trying to use nextid but even after reading the documentation notes on 
it, I'm still quite confused on how to use it.

I have a db_sql instanced and I need to find out the next primary key in 
sequence in a table in my database so I can insert a row with this data as 
such -

$db = new DB_SQL_SubClass(connection params);
$nextIDinSeq = nextid("???");	\\ i have no clue what goes within the parens 
there

$insertQuery = "insert into xyztable ('Next in Sequence') values 
($nextIDinSeq)";
$db->query($insertQuery);
etc etc...

How do I determine what the nextid is?

Help?  Please?

Maya

Re: [Phplib-users] nextid - how does it work?

[Liberty Y. <li...@em...>]

I can't answer your question..though i do have a few comments. 

Why are you inputting primary keys? Few RDBMS don't have the option to
auto-increment your primary key field. I rarely find myself in a
situation where i'm inputing the value for a primary key field. Foreign
keys, yes, but never primary keys!!! If you're inserting a new record,
let your server do that for you. I _know_ MySQL will do that for you 


On Wed, 2003-07-16 at 14:20, Neha Mathur wrote:
> Hello,
> 
> I'm trying to use nextid but even after reading the documentation notes on 
> it, I'm still quite confused on how to use it.
> 
> I have a db_sql instanced and I need to find out the next primary key in 
> sequence in a table in my database so I can insert a row with this data as 
> such -
> 
> $db = new DB_SQL_SubClass(connection params);
> $nextIDinSeq = nextid("???");	\\ i have no clue what goes within the parens 
> there
> 
> $insertQuery = "insert into xyztable ('Next in Sequence') values 
> ($nextIDinSeq)";
> $db->query($insertQuery);
> etc etc...
> 
> How do I determine what the nextid is?
> 
> Help?  Please?
> 
> Maya
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: VM Ware
> With VMware you can run multiple operating systems on a single machine.
> WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines at the
> same time. Free trial click here: http://www.vmware.com/wl/offer/345/0
> _______________________________________________
> Phplib-users mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phplib-users

Re: [Phplib-users] nextid - how does it work?

[Richard A. <rh...@ju...>]

At 15:02 -0700 16/7/03, Liberty Young wrote:

>Why are you inputting primary keys? Few RDBMS don't have the option to
>auto-increment your primary key field. I rarely find myself in a
>situation where i'm inputing the value for a primary key field. Foreign
>keys, yes, but never primary keys!!! If you're inserting a new record,
>let your server do that for you. I _know_ MySQL will do that for you

But MySQL doesn't make it very easy to work out what the ID of the
just-inserted record was. And this mechanism varies from DB to DB.

By using next_id() you know ahead of time what the ID is.

I use next_id() quite often for this reason.

 ...R.

Re: [Phplib-users] nextid - how does it work?

[Nathaniel P. <np...@te...>]

----- Original Message ----- 
From: "Neha Mathur" <ne...@sp...>
To: <php...@li...>
Sent: Wednesday, July 16, 2003 2:20 PM
Subject: [Phplib-users] nextid - how does it work?


> Hello,
>
> I'm trying to use nextid but even after reading the documentation notes on
> it, I'm still quite confused on how to use it.
>
> I have a db_sql instanced and I need to find out the next primary key in
> sequence in a table in my database so I can insert a row with this data as
> such -
>
> $db = new DB_SQL_SubClass(connection params);
> $nextIDinSeq = nextid("???"); \\ i have no clue what goes within the
parens
> there
>
> $insertQuery = "insert into xyztable ('Next in Sequence') values
> ($nextIDinSeq)";
> $db->query($insertQuery);
> etc etc...

Hmm, interesting question... I admit that in my apps, I just use the
sequence system provided by the database (PostgreSQL, in my case), so I
don't know much about it either...

Looking at the code for db_pgsql.inc and db_mysql.inc it looks like this is
what it does:

There is a table defined by the class variable $Seq_Table in DB_Sql, which
is by default set to use a table named 'db_sequence'. From what I can tell
from the code, it looks like this table would have a schema that looks like
this:

db_sequence
-------------
seq_name (varchar, primary key)
nextid (int)

Note that this table isn't set up with the SQL scripts included with the
PHPlib distribution. You'll have to set it up yourself. Once it's set up,
however, you should be able to do something like this in your code:

$db = new DB_Sql_subclass
$my_id = $db->nextid('unique_seq_name');
$db->query("insert into my_table values ($my_id, $my_data);");
$my_2nd_id = $db->nextid('unique_seq_name');
$db->query("insert into my_table values ($my_2nd_id, $more_data);");
...

Obviously, you can adapt it for loops or whatever to be more efficient, but
hey, I'm just demoing it here.

What will happen is the first time $db->nextid('unique_seq_name') is called,
PHPlib will check to see if a sequence by the name of 'unique_seq_name'
exists in the db_sequence table. If there isn't one, it will insert a record
into the db_sequence table to keep track of the id number, then return 1 as
its first id number. On subsequent calls, it will find the sequence name,
lock the table to prevent concurrent accesses which could corrupt your
sequences, increment the sequence by one, update it back to the database
with the incremented value, unlock the table, then return the incremented
value back to your script.

It seems to be a nice solution for developing cross-platform scripts that
don't depend on a specific database for sequences, but it lacks some of the
sequence manipulation functionality that is present in PostgreSQL, for
example; although I imagine that one could add back some of that
functionality by extending the class with additional methods. Or even
convert it to be an abstraction layer over DB-specific sequences.

Anyway, hope this helps.

_________________________________
Nathaniel Price <np...@te...>
Webmaster

Re: [Phplib-users] nextid - how does it work?

[Nathaniel P. <np...@te...>]

----- Original Message ----- 
From: "Liberty Young" <li...@em...>
To: "Neha Mathur" <ne...@sp...>
Cc: <php...@li...>
Sent: Wednesday, July 16, 2003 3:02 PM
Subject: Re: [Phplib-users] nextid - how does it work?


> I can't answer your question..though i do have a few comments.
>
> Why are you inputting primary keys? Few RDBMS don't have the option to
> auto-increment your primary key field. I rarely find myself in a
> situation where i'm inputing the value for a primary key field. Foreign
> keys, yes, but never primary keys!!! If you're inserting a new record,
> let your server do that for you. I _know_ MySQL will do that for you

There are some instances where it is useful or even necessary to know ahead
of time what primary key will be assigned to a given record when it is
inserted. This function provides one means of doing that.

I don't know about MySQL, but in PostgreSQL you can use built-in sequences
and find out the primary key that will be used by querying the database
using "select nextval('sequence_name');", however, since this is not part of
the SQL standard, it is likely not portable to other databases. This method
would be portable across all the databases PHPlib supports. It also results
in a bit cleaner code; using PostgreSQL, I have to do this to get a primary
key from the database:

$db->query("select nextval('sequence_name');");
$db->next_record();
$my_id = $db->f('nextval');

whereas with PHPlib it would become:

$my_id = $db->nextid('sequence_name');

_________________________________
Nathaniel Price <np...@te...>
Webmaster

Re: [Phplib-users] nextid - how does it work?

[Layne W. <la...@dr...>]

> There are some instances where it is useful or even necessary to know 
> ahead
> of time what primary key will be assigned to a given record when it is
> inserted. This function provides one means of doing that.

Here are some other uses for nextid() from personal experience:

1. Reference IDs based on date in the format YYYYMMxxxx (where xxxx 
provides for enough IDs). At the beginning of each month I can set my 
counter to e.g. 2003070000 and increment from there, skipping unused 
200306xxxx IDs.
2. Non-primary keys. For instance, the primary key for an employee 
table is the SSN, but if I also want to know that I was the first 
employee, Fred was the second employee, etc. I need to keep another 
counter.
3. Force Unique IDs. MySQL's auto-increment feature creates an ID one 
greater than the largest ID currently in the table. If I create a new 
record and then delete it before another record is created, MySQL will 
reuse the ID. Occasionally I will duplicate table structures in two 
databases (one for editing, one for holding currently published data). 
MySQL's behavior eliminates any chance of data integrity when 
publishing newly edited data.

I'm sure there are more reasons out there...

Layne Weathers

Re: [Phplib-users] nextid - how does it work?

[Peter T. <pte...@te...>]

There is similar functionality in MySQL.
The equivalent for the PostgreSQL /Oracle sequence is the AUTO_INCREMENT 
column type. (Although the implementation works slightly different).
The LAST_INSERT_ID() function is tied to AUTO_INCREMENT and could be 
helpful building a 'next sequence' mechanism with MySQL. Check the MySQL 
docs for the specifics.

If your goal is building a database independent solution, I would opt for 
the PHPLIB solution.

Peter

  At 00:29 17-07-2003, Nathaniel Price wrote:

>----- Original Message -----
>From: "Liberty Young" <li...@em...>
>To: "Neha Mathur" <ne...@sp...>
>Cc: <php...@li...>
>Sent: Wednesday, July 16, 2003 3:02 PM
>Subject: Re: [Phplib-users] nextid - how does it work?
>
>
> > I can't answer your question..though i do have a few comments.
> >
> > Why are you inputting primary keys? Few RDBMS don't have the option to
> > auto-increment your primary key field. I rarely find myself in a
> > situation where i'm inputing the value for a primary key field. Foreign
> > keys, yes, but never primary keys!!! If you're inserting a new record,
> > let your server do that for you. I _know_ MySQL will do that for you
>
>There are some instances where it is useful or even necessary to know ahead
>of time what primary key will be assigned to a given record when it is
>inserted. This function provides one means of doing that.
>
>I don't know about MySQL, but in PostgreSQL you can use built-in sequences
>and find out the primary key that will be used by querying the database
>using "select nextval('sequence_name');", however, since this is not part of
>the SQL standard, it is likely not portable to other databases. This method
>would be portable across all the databases PHPlib supports. It also results
>in a bit cleaner code; using PostgreSQL, I have to do this to get a primary
>key from the database:
>
>$db->query("select nextval('sequence_name');");
>$db->next_record();
>$my_id = $db->f('nextval');
>
>whereas with PHPlib it would become:
>
>$my_id = $db->nextid('sequence_name');
>
>_________________________________
>Nathaniel Price <np...@te...>
>Webmaster
>
>
>
>-------------------------------------------------------
>This SF.net email is sponsored by: VM Ware
>With VMware you can run multiple operating systems on a single machine.
>WITHOUT REBOOTING! Mix Linux / Windows / Novell virtual machines at the
>same time. Free trial click here: http://www.vmware.com/wl/offer/345/0
>_______________________________________________
>Phplib-users mailing list
>Php...@li...
>https://lists.sourceforge.net/lists/listinfo/phplib-users


----------
TERIS Information Services BV
Peter Termaten             Phone: +31 23 565 2682
Aagje Dekenstraat 43    Fax:     +31 23 565 2676
2135 RJ  Hoofddorp      Mobile: +31 653 65 8686
                           www.teris.nl

----------