PHP Mysql random number 1

[Kurt111780]

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.

http://www.seedandgarden.com

 

[vacunita]

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]

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]

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 [blue]Rand[/blue] 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]

Sorry, the function is sha[red]1[/red](), not sha(). Here's a quick-and-dirty example script:

<?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]

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.

http://www.seedandgarden.com

 

[vacunita]

Just make a select statement

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]

I would modify vacunita's query a little. I would perform:

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]

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]

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]

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:

<?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]

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.

http://www.seedandgarden.com

 

[Kurt111780]

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.

http://www.seedandgarden.com

 

[vacunita]

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]

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).