Solved

Generate $number, check MySQL -> if same $number, generate new and check again

Posted on 2010-09-13
25
309 Views
Last Modified: 2013-12-13
Hi,

I have script which generates md5 hash.

I'd like to have a checker which will work like:
1. $hash = md5();
2. Check if $hash exists in MySQL
3. If so, generate another $hash and check SQL again
4. If no match, echo $hash;

I guess it could be easy with foreach() and/or use custom function() but no idea how to do it.

Thanks for any help!
$hash = md5($something);
		
$findSameHash = mysql_query("SELECT * FROM table WHERE hash = '$hash'");
$matches = mysql_num_rows($findSameHash);
		
if(!$matches > 0) {
echo "yes";
} else {
//generate new hash and check again in SQL
}

Open in new window

0
Comment
Question by:Aidam-Unlimited
  • 8
  • 8
  • 5
  • +1
25 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 33662677
What are you going to change with regard to the $something if the hash already exists?

You can run md5() against the a particular value as often as you want. It will always return the same value.

e.g.

<?php
foreach(range(1, 1000) as $test) echo md5('Richard Quadling'), PHP_EOL;
?>

will generate 1000 14a91fb59ba5b0ee0265c7987b332cb3

0
 
LVL 40

Accepted Solution

by:
RQuadling earned 250 total points
ID: 33662714
Something like ...
do // do implies that the loop will run at least once.
	{
	$hash = md5($something); // You would need to change this bit every time through the loop.
		
	$findSameHash = mysql_query("SELECT 1 FROM table WHERE hash = '$hash' LIMIT 1");
	}
while(1 !== mysql_num_rows($findSameHash));

// When you get here, $hash is unknown.

Open in new window

0
 

Author Comment

by:Aidam-Unlimited
ID: 33662717
RQuadling sorry for misunderstanding, maybe let's forget about md5(), say $hash = 2158622;
0
 

Author Comment

by:Aidam-Unlimited
ID: 33662744
RQuadling thanks for the second comment, I'm gonna test it and let you know.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33662819
The same thing applies. What data is going to change? A hash is, for the main, an interpretation of the original data. So, the hash won't change unless the data does.

You may be using the wrong term.

Are you just looking for a unique random number?

mt_rand() would be my suggestion as it allows VERY big numbers (2147483647), compared to rand() (32767).


0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33662850
Here is a script that I have used as a basis to accomplish what you want.
<?php // RAY_UNIQUE.php

// THIS IS A TEACHING EXAMPLE, NOT COMPLETE CODE.
//
// THE RANDOM NUMBER COULD BE A RANDOM ALPHA-NUMERIC STRING, TOO
// THE LENGTH OF THE RESULTING STRING AND THE NUMBER OF CHARACTERS IN THE INPUT STRING
// GEOMETRICALLY AFFECT THE NUMBER OF POSSIBLE UNIQUE COMBINATIONS.  FOR EXAMPLE,
// POSSIBLE COMBINATIONS = pow($n,strlen($a2z)); = 1.2E+11 IF $n IS 12
//
// IF YOU USE LETTERS AS WELL AS NUMBERS, YOU WANT TO OMIT THE LETTERS THAT LOOK
// LIKE NUMBERS, SO REMOVE THE UPPER-CASE O AND THE LOWER-CASE L, ETC.
//
// YOU MIGHT WANT TO REMOVE THE VOWELS, AS WELL, IF YOU WANT TO REDUCE THE RISK OF
// GENERATING A NATURAL LANGUAGE WORD.
//
// THE SEED ALPHABET IS IN THE FUNCTION VAR '$a2z'



// A FUNCTION TO CREATE A RANDOM N-DIGIT NUMBER
function random_numeric_string($n=12)
{
// POSSIBLE COMBINATIONS = pow($n,strlen($a2z)); = 1.2E+11 IF LENGTH IS 12
//          1...5...10...15...20...
    $a2z = "0123456789";
    $str = "";

    // ADD RANDOM CHARACTERS TO THE STRING UNTIL IT IS OF THE CORRECT LENGTH
    while(strlen($str) < $n)
    {
        $str .= substr($a2z, mt_rand(0,(strlen($a2z))), 1);
    }
    return $str;
}



// A FUNCTION TO ENSURE THAT THE RANDOM NUMBER IS UNIQUE
function make_new_coupon_code()
{
    $code = '';
    while ($code == '')
    {
        // GENERATE A UNIQUE AND RANDOM TOKEN
        $code = random_numeric_string();

        // ATTEMPT TO INSERT THE CODE STRING
        $isql = "INSERT INTO coupons ( couponcode ) VALUES ( '$code' )";

        // IF THE INSERT QUERY FAILS
        if (!$i = mysql_query($isql))
        {
            // GET THE ERROR NUMBER FROM MYSQL
            $err = mysql_errno();

            // DUPLICATE - DISCARD THIS CODE AND TRY AGAIN
            if ($err == 1062)
            {
                $code = ''; // REPEAT THE WHILE LOOP
            }
            // SOMETHING ELSE, BUT NOT DUPLICATE UNIQUE FIELD
            else
            {
                /* HANDLE FATAL QUERY ERROR */
            }
        } // END INSERT
    } // END WHILE
    return $code;
}



// CALLING SEQUENCE:
$my_unique_string = make_new_coupon_code();



// THE DATA BASE DEFINITION
$sql = "CREATE TABLE coupons ( couponcode VARCHAR($n) UNIQUE NOT NULL )";

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33662865
Maybe ...
// Generate a random value.
$hash = md5(unquid(mt_rand(), true));

do // do implies that the loop will run at least once.
	{
	// Use the last known value as the data for the next hash.
	$hash = md5($hash);
		
	$findSameHash = mysql_query("SELECT 1 FROM table WHERE hash = '$hash' LIMIT 1");
	}
while(1 !== mysql_num_rows($findSameHash));

// When you get here, $hash is unknown.

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33662894
MySQL_ErrNo() == 1062 is a very convenient thing!

;-)

best to all, ~Ray
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33663040

If you are generating coupons, then I'd do it the following way.

Insert into the coupon table all the data you need to hold regarding the coupon, along with a hash that is based upon the data with a known (but private) key.

So, if your data is like ...

$a_Coupon = array
      (
      'Issued' => '2010-09-13 15:57:00.000',
      'Value'  => 200.00,
      'Expiry' => '2011-12-31 23:59:59.999',
      );

Then I'd insert the data into the DB, get the UniqueID (the identity or autoinc value) and apply that to the data to get the hash.

$a_Coupon['Id'] = 34123;


<?php
$a_Coupon = array
      (
      'Issued' => '2010-09-13 15:57:00.000',
      'Value'  => 200.00,
      'Expiry' => '2011-12-31 23:59:59.999',
      'Id'     => 34123,
      );

$s_Hash = md5(serialize($a_Coupon));
echo $s_Hash;
?>

Outputs ...

582d846d7da106b36d9200c00256c991

Now I'd add this to the row I just inserted.

The chances of collision is low. Very very low.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33663052
The coupon's table uniqueID is the known but private value.

If you had all the data on the coupon (issue date, value, expiry) and a barcode for the hash, you wouldn't be able to hack the code to make a different value.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33663078
With a MySQL "UNIQUE" column, the chance of a collision is reduced asymptotically toward zero.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33663237
But, if this is for a coupon, putting the unqiueID on the coupon means I COULD supply any ID within reason and I could have a match.

If I've got coupon 123456, then maybe 123455 and 123457 would be a good guess to make.

A "hash" is the perfect way to obfuscate the data. As long as there is something unique to base the hash on.

If there is a collision when inserting the hash, then just update the issue date. It won't make any difference to the data (in terms of usage - what's a microsecond or so between friends), but the hash will be radically different.



0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33663626
In fact, I have used the algorithm I posted here to generate unique codes for clients.  The snippet below has it cut-and-paste from my production app code.

The meaning of 3.8E29, in practical terms, suggests that your chances of winning the lottery, getting struck by lightning or meeting a person with identical DNA are all greater than your chances of guessing someone else's coupon code.

Best to all, over and out, ~Ray
function random_string($length=9) 
{
// POSSIBLE COMBINATIONS = pow($length,strlen($chr)); = 3.8E29 IF LENGTH IS 9
//             1...5...10...15...20...25...30.
    $chr    = "ABCDEFGHJKMNPQRSTUVWXYZ23456789";
    $str    = "";
    while(strlen($str) < $length) 
    {
        $str .= substr($chr, mt_rand(0,(strlen($chr))), 1);
    }
    // ADD HYPHENS FOR READABILITY
    if ($length == 9) 
    { 
        $str = substr($str,0,3) . '-' . substr($str,3,3) . '-' . substr($str,6,3);
    }
    return($str);
}

Open in new window

0
 

Author Comment

by:Aidam-Unlimited
ID: 33664075
Guys, thanks for your suggestions but it looks we missed the original question by getting stuck in generating original hashes, which is probably mi mistake as haven't clarified my question enough.

So let's forget about any hash or code.
Let's say I've got a function which generate different number each time is called but sometimes this number can repeat.

$value = getMeNumber(); //Number0024

Now I check if $value is already in the database. If it is, I need to call getMeNumber() once again and check it in database, etc.

If $value is really unique I just process it in next php code.

Please ask if anything unclear. Your help is much appreciated!
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33664166
If you are going to insert the unique number into the database, then I'd just insert a row and get the unique ID from the database.

Turning the source around. The DB can provide you with a unique number every time.
0
 

Author Comment

by:Aidam-Unlimited
ID: 33664235
Unique number is calculated by custom php function merging three parameters including user input, It's basically as it is but sometimes can repeat because of user input.

Let's sort out the db checking code please.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33664980
Did you read the posted code here at ID:33662850?

The entire strategy is this:

1. Mark the data base table column UNIQUE.  
2. INSERT the value into the table, into the UNIQUE column.  
3. MySQL will throw error number 1062 when you attempt to insert a duplicate value into a UNIQUE column.
4. If INSERT works, the number was unique.
5. If INSERT fails and mysql_errno() == 1062, the number was a duplicate.

It really is that simple!

Best, ~Ray
0
 
LVL 6

Expert Comment

by:birwin
ID: 33666217

for($i=1;$i<100;$i++){
$password=getMeNumber(); /// create user name for email entry as user
$query_test_unique="select user_no from $db.empinfo where user_no='$password' ";
$result_test_unique=mysql_query($query_test_unique, $conn)
if(!mysql_num_rows($result_test_unique)){ break;} // if no duplicate found, break
}
0
 
LVL 6

Expert Comment

by:birwin
ID: 33666309
Note that my code above was written to test unique user numbers, rather than a password, so ignore any apparent confusion between password and user_no, but the logic and code works.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33666534
No need for a SELECT statement.  You already have the character string, so you know what it is.  What you want to know is, "is this unique?"  The insert works if it is unique and fails if it is not.  Moreover, the next insert has the benefit of "learning" from the prior inserts.  Easy!
0
 
LVL 6

Expert Comment

by:birwin
ID: 33667480
Ray. your solution, which, of course, works, may be ideal for some situations, however, you are assuming that he is ready to enter the user into the database. Certainly in my case, this is just the first of a series of tests that I need to perform before deciding to actually insert the user into the database.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33667539
No need to "enter the user" at all -- what we want to know is whether the character string is unique.  This algorithm tells us that.
0
 
LVL 6

Expert Comment

by:birwin
ID: 33667776
Am I missing something here? If he uses the test of:
INSERT the value into the table, into the UNIQUE column.  
If INSERT works, the number was unique.
If INSERT fails and mysql_errno() == 1062, the number was a duplicate.
If it "works", hasn't it been inserted into his live database?
As I mentioned, there are circumstances where you may want to do that, but I am not sure that is the ideal scenario if you still need to do other tests on the input.


0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 33673127
Forgot to mention, there is a DELETE capability available in MySQL.  It can be used to reverse an INSERT.  ;-)

But that is only of concern if you care about the precise orthogonal integrity of the UNIQUE table.  I mostly do not care about the UNIQUE table, since it is only for my own assertion of uniqueness.  In other words, I might generate some unique codes that eventually never get used.  So what?  With billions of billions of different unique strings to choose from I think I can waste a few.

Who know what other tests would be needed on the input, or when these other tests would be done?  A sensible design might put the validation at the front of the processing and the data base updates at the end of the processing once we knew that we had a live one.
0
 

Author Closing Comment

by:Aidam-Unlimited
ID: 33737044
Thanks guys
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PHP Variable into a number 3 35
PHP and Soap 3 30
Should you use {} for a variable in heredoc 3 27
Datepicker in PHP 9 21
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now