On Wed, 2007-03-21 at 16:40 -0600, ccspencer@xxxxxxxxxxxx wrote:
> Jim Moseby writes:
>
> >> However, altho I know that by making the random number big enough
> >> the likelyhood of collisions can be made vanishingly small, I was
> >> actually concerned with eliminating the possibility of collisions
> >> altogether by checking to see if the number had been used before.
> >>
> >> I just don't know how to do that properly with Mysql. Perhaps it
> >> is necessary to lock to table, check, make the insert and then
> >> unlock it. But I was hoping that there would be a simpler way.
> >
> > One way is to make your id field a unique key. MySQL will not let
> > you insert a record with a duplicate unique key, and will issue an
> > error. Your code should always check for errors on insert anyway,
> > so if you get an error, generate a new key and try again.
>
> Thanks. Yes, I check for errors. But there are other types of errors
> so I'd need to verify that it is a duplicate key error and, in my
> ignorance, I have not yet figured out how to do that programatically.
> I worry about getting into an infinite loop.
Bleh, you can solve this with at most 2 queries.
----
CREATE TABLE foo
(
id INT NOT NULL AUTO_INCREMENT,
uid char( 50 ),
PRIMARY KEY ( id )
);
----
<?php
$query = "INSERT INTO foo ( uid ) VALUES ( NULL )";
if( mysql_query( $query ) !== false )
{
$id = mysql_insert_id();
$uid = str_pad( $id, 10, 0 )
.sha1( uniqid( rand(), true )
.uniqid( rand(), true )
.uniqid( rand(), true ) );
//
// No need to escape, we know from whence the data came.
//
$query =
"UPDATE "
." foo "
."SET "
." uid = '".mysql_real_escape_string( $uid )."' "
."WHERE "
." id = '".mysql_real_escape_string( $id )."' ";
mysql_query( $query );
}
?>
There you go, a 50 character highly unguessable and guaranteed unique
ID. Sure everyone knows the ID of the entry now, but that shouldn't be
important. In fact it optimizes retrieval and validation by allowing the
lookup to occur on an integer then validation by matching the full UID
against the UID found for the ID. In all honesty though, hitting the
database with random generated md5() hashes is probably more efficient
since the likelihood of a collision is small and so in most cases you
will only make one database query.
Cheers,
Rob.
--
.------------------------------------------------------------.
| InterJinn Application Framework - http://www.interjinn.com |
:------------------------------------------------------------:
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`------------------------------------------------------------'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
