Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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

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
Aidam-Unlimited
Asked:
Aidam-Unlimited
  • 8
  • 8
  • 5
  • +1
2 Solutions
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Aidam-UnlimitedAuthor Commented:
RQuadling sorry for misunderstanding, maybe let's forget about md5(), say $hash = 2158622;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Aidam-UnlimitedAuthor Commented:
RQuadling thanks for the second comment, I'm gonna test it and let you know.
0
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Ray PaseurCommented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Ray PaseurCommented:
MySQL_ErrNo() == 1062 is a very convenient thing!

;-)

best to all, ~Ray
0
 
Richard QuadlingSenior Software DeverloperCommented:

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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Ray PaseurCommented:
With a MySQL "UNIQUE" column, the chance of a collision is reduced asymptotically toward zero.
0
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Ray PaseurCommented:
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
 
Aidam-UnlimitedAuthor Commented:
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
 
Richard QuadlingSenior Software DeverloperCommented:
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
 
Aidam-UnlimitedAuthor Commented:
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
 
Ray PaseurCommented:
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
 
birwinCommented:

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
 
birwinCommented:
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
 
Ray PaseurCommented:
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
 
birwinCommented:
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
 
Ray PaseurCommented:
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
 
birwinCommented:
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
 
Ray PaseurCommented:
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
 
Aidam-UnlimitedAuthor Commented:
Thanks guys
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now