any of you ever wrote a get_next_id function?

function getNewID($fieldName, $tableName) { // INT "METHOD" FOR
NON-AUTO-INCREMENT FIELDS
global $dbConn;
$sql = "SELECT MAX($fieldName) FROM $tableName";
$query = mysql_query($sql, $dbConn) or die('Could not perform query: ' .
mysql_error());
if (!($row = mysql_fetch_row($query))) return 1;
return $row[0];
}

I'm trying to retrieve the max(ID) from a column that cannot be
auto_incremented (since mySQL version on this remote site only allows for
tables to have ONE auto_increment field - how stupid), the basic premise is
this:

I either return
1) the max ID
2) '1'

How have you guys done it, or what have I missed?

Phil

Phil Powell wrote:

> I either return
> 1) the max ID
> 2) '1'
>
> How have you guys done it, or what have I missed?

You have missed what happens when more than one request comes at the
same time.

Just a question - why do you need more than one auto-inc in a single
table ?

--
Spam:newsgroup(at)craznar.com@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

Hi Phil,

Agreed that simultaneous requests will cause problems, but what
mechanisms for ensuring unique column identifiers are commonly used
outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
available?

- TL

127.0.0.1 wrote:
> Phil Powell wrote:
>
>
>>I either return
>>1) the max ID
>>2) '1'
>>
>>How have you guys done it, or what have I missed?
>
>
> You have missed what happens when more than one request comes at the
> same time.
>
> Just a question - why do you need more than one auto-inc in a single
> table ?
>

Tom Lee <tl_nntp@webcrumb.com> schrieb:

> Agreed that simultaneous requests will cause problems, but what
> mechanisms for ensuring unique column identifiers are commonly used
> outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> available?

I use triggers on Oracle or MS-SQL Server.

Regards,
Matthias

Tom Lee wrote:

> Agreed that simultaneous requests will cause problems, but what
> mechanisms for ensuring unique column identifiers are commonly used
> outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> available?

Create a table called T_AUTO which has

___________
Table Field NextId


Then when you need a new ID, create a r/w connection to the table - get
the value and update it in one go.

Problem with this is that in MySQL it will be very slow as there is no
such thing as a row lock.

Seriously I have used the filesystem before for unique ids - by
creating a directory full of number.avail (e.g. 1.avail etc) ... then
my process tries to rename one of them to 1.unavail - when it succeeds
it uses it and puts some info in the file as to what it now points to.
The cool thing about file renames is they are one of the few fast
indivisible operations in almost all OSs.


--
Spam:newsgroup(at)craznar.com@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>

Could you go into that a bit more, I am not sure what you mean, sounds
interesting.

I need more than one "auto-increment" due to the schema of the table; in the
Vignette world I'm used to a separate "next_id" table but that would not
apply well in this version of mySQL due to performance.

Phil

"127.0.0.1" <newsgroup(at)craznar.com@verisign-sux-ijlkl.com> wrote in
message news:s4ggb.139093$bo1.2485@news-server.bigpond.net.au...
> Tom Lee wrote:
>
> > Agreed that simultaneous requests will cause problems, but what
> > mechanisms for ensuring unique column identifiers are commonly used
> > outside of MySQL - e.g. SQL Server, Postgres - where auto_inc is not
> > available?
>
> Create a table called T_AUTO which has
>
> ___________
> Table Field NextId
>
>
> Then when you need a new ID, create a r/w connection to the table - get
> the value and update it in one go.
>
> Problem with this is that in MySQL it will be very slow as there is no
> such thing as a row lock.
>
> Seriously I have used the filesystem before for unique ids - by
> creating a directory full of number.avail (e.g. 1.avail etc) ... then
> my process tries to rename one of them to 1.unavail - when it succeeds
> it uses it and puts some info in the file as to what it now points to.
> The cool thing about file renames is they are one of the few fast
> indivisible operations in almost all OSs.
>
>
> --
> Spam:newsgroup(at)craznar.com@verisign-sux-klj.com
> EMail:<0110001100101110011000100111010101110010011 010110
> 11001010100000001100011011100100110000101111010011 011100
> 11000010111001000101110011000110110111101101101001 00000>