Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Kurt111780 (TechnicalUser) (OP)
14 Dec 05 11:38
Hello,

Does mySql have an auto number similar to access?  I need to generate a unique random alphanumeric number for each new record in the database.  The id will be used in a link and should not be guessable so an incrementing number will not work.

Do I have to generate the number using php, then check for it in the DB, and then insert the record?  Is there a tutorial on this somewhere?  I'm new to php.

Thanks,
Kurt

It's only easy when you know how.
www.seedandgarden.com

Helpful Member!  vacunita (Programmer)
14 Dec 05 12:11

Quote (Kurt111780):


Do I have to generate the number using php, then check for it in the DB, and then insert the record?  Is there a tutorial on this somewhere?  I'm new to php.

That would be the simplest way of doing it.

Check the php online manual for RAND. http://www.php.net/manual/en/function.rand.php

However if there already alot of records in the DB it might take some time to generate one that is not there, but it should still work.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

sleipnir214 (Programmer)
14 Dec 05 12:24
Although I agree that the best way to generate the string is in PHP and not MySQL, I disagree on the functions to use.  It is difficult to generate the unique random strings you will need.

I would use something like sha() (http://www.php.net/manual/en/function.sha1.php) on a string that consists of several concatenated values what will change regularly.  For example, concatenating the client's IP adddress, the server's time, and a random number.

Want the best answers? Ask the best questions!

TANSTAAFL!!

vacunita (Programmer)
14 Dec 05 12:36

Quote (Sleipnir):


concatenating the client's IP adddress, the server's time, and a random number.
I agree  with sleipnir on the uniqueness and unguessability of a stirng formed in such a way. Much better than my Rand suggestion.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

sleipnir214 (Programmer)
14 Dec 05 12:54
Sorry, the function is sha1(), not sha().  Here's a quick-and-dirty example script:

CODE

<?php
print sha1($_SERVER['REMOTE_ADDR'] . microtime() . (string)rand());
?>

If you're running PHP on Linux, you might look at executing  the command 'uuidgen' externally.

If you're running PHP in Win32, you might look at com_create_guid() (http://ww.php.net/com_create_guid)

Want the best answers? Ask the best questions!

TANSTAAFL!!

Kurt111780 (TechnicalUser) (OP)
14 Dec 05 14:30
Hello,  I like your suggestion.  Your code sample works great.

Now I need to check for this in the database before inserting a new record.  Is there a simple way to do this?

Thanks,
Kurt

It's only easy when you know how.
www.seedandgarden.com

vacunita (Programmer)
14 Dec 05 14:40
Just make a select statement

CODE

SELECT *FROM yourtable where yourfield=yourgeneratedstring

If the select statement returns rows at least 1 you know it exists. if it returns 0 rows you know it doesn't exist.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

sleipnir214 (Programmer)
14 Dec 05 14:57
I would modify vacunita's query a little.  I would perform:

CODE

SELECT COUNT(*) FROM tablename WHERE yourfield='<your generated string>'
With this, you are fetching the absolute minimum amout of data from the database server.

Want the best answers? Ask the best questions!

TANSTAAFL!!

vacunita (Programmer)
14 Dec 05 15:03
Unless you believe there is going to be more than one ocurrence of the string, which we already established we dont want, limiting the query to the minimum result will do nothing more than bring the one and only result that it should find in any event.

There should be no event in which it will return more than one row.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

sleipnir214 (Programmer)
14 Dec 05 15:18
What represents less information, one row of data that containts at least one 40-character column plus other columns or one derived 32-bit integer column of data?

Besides, I was always taught that one should ask a relational database server only for what one wants.  We don't want to know the contents of the rows that match our filter.  We want to know the number of rows that match our filter.

Want the best answers? Ask the best questions!

TANSTAAFL!!

sleipnir214 (Programmer)
14 Dec 05 15:37
You could also set the table's hash-column to have a unique index and have your script just try to insert the new row.

If the unique constraint is violated (i.e. the hash index is duplicated), mysql_query() will return a FALSE and mysql_errno() will return the number 1062.

See:

CODE

<?php
$dbh = mysql_connect ('localhost', 'test', 'test');
mysql_select_db ('test', $dbh);

$query = "INSERT INTO foo values ('a', 1, 2)";

$result = mysql_query ($query);
if ($result == FALSE)
{
    $error_code = mysql_errno($dbh);
    if ($error_code == 1062)
    {
        print 'The hash value you attempted to insert already exists in the table';
    }
    else
    {
        print 'ome other error: ' . mysql_error();
    }
}
else
{
    print 'success';
}
?>

Starting with an empty table "foo", the first column of which has a unique constraint, on the first run, the script will enter the record into the table.  On the second, the script will fail, and will specifically tell you that the insertion of the record would violate the unique constraint on the first column.

Want the best answers? Ask the best questions!

TANSTAAFL!!

Kurt111780 (TechnicalUser) (OP)
14 Dec 05 16:04
I was thinking I could use the result from the DB but didn't know how to get it.  Thanks.  I'll try to explain what I am trying to do.  It is a bit more complex.  There are actually two hash values.  pageID and fileID.  fileID is unique.  

right now I'm working on the page that creates the records in the db.  so first I create the $pageID hash, as long as it doesn't exist already it will be used for each record in the insert statement.  The fileId has to be  unique for each record.  The process will repeat for each file name in an array created from a form submission on a previous page.

Thanks for the help.  Hope you can understand that.
Kurt

It's only easy when you know how.
www.seedandgarden.com

Kurt111780 (TechnicalUser) (OP)
14 Dec 05 16:36
So basically,

1) Generate pageID
2) Make sure it doesn't exist in the db if exist repeat
3) Generate fileID
4) Make sure it doesn't exist.  If exist repeate
5) get 1st file name from array.
6) insert pageID, FileID, FileName etc.
7) repeate step 3-6 for each file name in the array.

Kurt

It's only easy when you know how.
www.seedandgarden.com

vacunita (Programmer)
14 Dec 05 17:54
For Each Id you want to generate, create a query that looks in the appropriate field. if it is not there then it moves on.

It doesnt matter if it is there 5 times, as long as it finds that it has been used at least once it will return a row.  the more times its been used the more rows it will return.

When you get both to be unused then you can proceed to the insertion. in which you select the item from the array. Just keep a counter so you know where in the array you left off so the next iteration will use that number.

How you decide how many rows will use the pageID is up to you while you are doing the inserts you use the same Id you generated before until you need a new one. So if you want say 10 rows per page ID you would implelemt either a for loop or a While that does the insertion 10 times with the generated ID and then you generate a new one.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

jpadie (TechnicalUser)
15 Dec 05 6:40
i'd suggest that the chance of you hitting a duplicate ID with the randomising function suggested by sleipnir is about the same chance as picking a discrete and intended molecule from our atmosphere.

Therefore I'd save the strain on the database calls and just assume that the id is unique and then test for a mysql_error 1062 on the insert statement (and this is what i do in practice on databases with large numbers of rows).

I personally use "md5(uniqid(rand(),TRUE))" to generate my numbers (as suggested by php.net).  I also always ensure that I prepend an alpha string (such as "id") as some abstraction layers do not put ticks around table and field names and you don't want a field name starting with a number (or i don't anyway).

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close