Generate hundreds of random unique coupon codes and insert in database

Question

I'm using PHP laravel 7 I have a coupons table which has a field called code, and this code is 8 characters (numbers and characters) this code should be unique among coupon records, In admin panel I have a button for admin user, which he can click on and generate as many coupons as he want. I'm gonna share with you the best solution came to my mind and ask you if there is a better and more performant solution.

I'm gonna generate unique strings in my php code using following function :

substr(uniqid(), 0, 8)

then I'll execute a query to check if there is any coupons in my table which it's code is equal to one of my newly generated codes

$model->newQuery()->whereIn('code', $generated_codes)->get();

if the result list is empty, that's nice I'm gonna insert all of my codes in database. if the list is not empty, I'm gonna delete repeated codes from my generated codes, and also again based on number of repeated codes again generate new codes and again repeat the process, I'm gonna repeat the process as long as none of generated codes exist in database then I insert them in database.

my question is different from this question: PHP: How to generate a random, unique, alphanumeric string?

As I'm generating lots of unique codes, not just on code, and then I'm gonna insert these codes in database and I need to have minimum number of queries.

Use time() + uniqid id so there will never have a duplication — Huy Trịnh, May 14 '20 at 09:09
I have just 8 characters for this purpose, coupon codes have to be 8 characters. — Majid Abdolhosseini, May 14 '20 at 09:11
u can get all codes from db in one query, then check if code (generated by admin) in_array of codes from db — Odin Thunder, May 14 '20 at 09:16
You can make the code your id and then do `select count(*) from table` then `insert ignore ...` and then `select count(*) from table` again and if you inserted less than what you needed run again. This will be much faster than first doing a `whereIn` for potentially millions of records, however I suggest using `Str::random(8)` to get random codes since they are more random than a substring of uniqid — apokryfos, May 14 '20 at 09:19
@Odin Thunder, please consider I may have thousands of coupons so I think it's not a good solution, my php process is going to face memory leak. — Majid Abdolhosseini, May 14 '20 at 09:25
Does this answer your question? [PHP: How to generate a random, unique, alphanumeric string?](https://stackoverflow.com/questions/1846202/php-how-to-generate-a-random-unique-alphanumeric-string) — Dmitry Leiko, May 14 '20 at 09:26
It's different from my question, as my question is also about database and less query. — Majid Abdolhosseini, May 14 '20 at 09:28

apokryfos · Accepted Answer · 2020-05-14T12:53:30.237

Here's something something that might work:

function generateRandomCodes($number) {
    $codes = Collection::times($number, function () { Str::random(8); });

    $affected = DB::table('your_table')
       ->insertOrIgnore($codes->map(function ($code) {
             return [ 'code' => $code, /* more fields? */ ];
       });
    if ($affected < $number) {
        generateRandomCodes($number-$affected);  
    }
}

insertOrIgnore will ignore duplicate keys when inserting and overwrite data. This is useful because it avoids a lookup before the insert which can be expensive, this should return number of affected rows, i.e. new rows created. If it does not manage to insert all desired codes then it should run it again for the remaining codes. This all assumes that code is the primary key.

I have not tested this myself so I suggest you test it before using it in any production code.

Some additional notes:

Str::random internally uses a base64 encoded string from random_bytes which is cryptographically secure (though not sure if the base64 version of it would technically count as secure nor if the final result is actually secure). This means there are 62^8 = 2*10^14 combinations you can get characters (+, = and / are removed). That is an insanely high number and you shouldn't really expect collisions to happen until you've generated about 10% of those numbers (about 2*10^13) however this assumes you are storing these in a case sensitive column in your database, and by default string columns are not case sensitive, which cuts your unique combinations by about two thirds (which is still a quite large number) meaning you will not really need to call this function more than once unless you really are that "lucky"

Thanks, I think this is the only answer which is more convenient and performant than my own solution. — Majid Abdolhosseini, May 14 '20 at 10:49

score 0 · Answer 2 · answered May 14 '20 at 09:16

0

You can use this type of function to get unique coupon code.

function generateCode(){

    $code = substr(uniqid(), 0, 8);
    $exists = YourModel::where('code', $code)->count();
    if($exists > 0){
        $this->generateCode();
    }
    return $code;
}

answered May 14 '20 at 09:16

Nishabh Mistry

59
1
7

I think, what I implemented my self is more performant than this function. It may require to do lots of queries. – Majid Abdolhosseini May 14 '20 at 09:23
its the same, but more complicated – Odin Thunder May 14 '20 at 09:40

Odin Thunder · Answer 3 · 2020-05-14T09:41:29.750

0

U can use firstOrCreate(), but in fact it may execute a lot of query to. Hope it help U to find good solution

public function initCoupon()
{
    $code = substr(uniqid(), 0, 8);
    $new = false;
    do {

        $coupon = CouponModel::firstOrCreate(compact('code'));
        $new = $coupon->wasRecentlyCreated

    } while(!$new);

    return $coupon;
}

Than U can set additional Coupon data

edited May 14 '20 at 09:41

answered May 14 '20 at 09:31

Odin Thunder

3,284
2
28
47

thanks, but I think my own provided solution is more performant. – Majid Abdolhosseini May 14 '20 at 09:59

Generate hundreds of random unique coupon codes and insert in database

3 Answers3