How to generate voucher code, check the DB if it's unique, generate new one if not

Question

I'm having a loop issue in my script. I've spent a lot of time trying to fix it but I still don't know how to fix the problem. I need your help and suggestions regarding this.

My goal is to create a voucher code generator script where the user enters the number of voucher codes to be generated.

Then, the script will generate the required number of vouchers in the database table, and each voucher code will be checked if it is unique - if not, a new voucher code will be generated and the script will proceed until all vouchers are saved.

The problem is that if voucher already exists in the DB, a new one needs to be generated. This newly generated voucher code needs to be checked again if it's already in the DB, if it's unique it will be saved to the DB and if not, the process will go on again. This is where the loop problem lies. I hope you get what i mean.

By the way, the voucher code is in this format: XXXX-XXXX-XXXX (uppercase letters only)

Here's the current codes that I have:

include 'conn.php';

function WriteCSV($flname,$values) {
    $Filename = "./vouchers/$flname.csv";
    $fh = fopen($Filename, 'a') or die("can't open file");
    $filecontent = $values;
    $filecontent .= PHP_EOL;
    fwrite($fh,$filecontent);
    fclose($fh);
}

function generateCode(){
    $chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $res = "";
    for ($i = 0; $i < 4; $i++) {
        $res .= $chars[mt_rand(0, strlen($chars)-1)];
    }
  return $res;
}

function generateVCode(){
    $c1 = generateCode();
    $c2 = generateCode();
    $c3 = generateCode();
    $voucher = "$c1-$c2-$c3";

    return $voucher;
}

function searchDB($con, $voucher){
    $rs = mysqli_query($con,"SELECT count(*) AS cnt FROM vouchers WHERE vouchercode = '$voucher'");
    $row = mysqli_fetch_assoc($rs);
    $cnt = $row['cnt'];

    if($cnt > 0){
        return '1';
    } else {
        return '0';
    }
}

function checkVoucher($con, $voucher, $vsource, $expiry, $today, $vnum, $vprice){

    $dbres = searchDB($con, $voucher);

    if($dbres == '1'){ //voucher found in db
        $val = '0';
        $voucher = generateVCode(); //generate a new voucher
        checkVoucher($con, $voucher, $vsource, $expiry, $today, $vnum, $vprice); //repeat the process
    } else { // voucher is unique
        mysqli_query($con, "INSERT INTO vouchers (vouchercode, source, price, expires, generated) VALUES ('$voucher', '$vsource', '$vprice', '$expiry', '$today')");
        $flname = "$vsource - ".date('d M Y')." ($vnum vouchers)";
        WriteCSV($flname,$voucher);

        $val = '1';
    }

    return $val;
}

$vnum = $_POST['vouchernum'];
$vsource = $_POST['source'];
$vprice = $_POST['amt'];
$expdate = $_POST['expdate'];
$expiry = $_POST['voucherexpiry'];
$today = date('Y-m-d');
$expconv = date('Y-m-d',strtotime("$expiry"));
$expfive = date('Y-m-d',strtotime("$expiry +5 years"));

for ($x = 1; $x <= $vnum; $x++) {
    $vouchercode = generateVCode();

    if($expdate == "no"){
        $expiry = $expfive;
    } else {
        $expiry = $expconv;
    }

    do {

        $result = checkVoucher($con, $vouchercode, $vsource, $expiry, $today, $vnum, $vprice);

    } while ($result != '1');

    header("location: index.php?s=1");
}

By the way, if you have suggestions on how to generate the voucher codes easier, please feel free to share.

I'm thinking the issue/problem here is on either the do-while statement or the checkVoucher() function.

I'd really appreciate you help and suggestions. Thanks.

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

I would go completely easier. Set the voucher column in your table to unique. Generate a code PHP side, do your insert, in the error callback function call to generate a new code.

Basically, this will self loop until inserted. Then in your success callback add it to your display. All of this is wrapped in a while loop. Once you get your 5, break the loop.

As far as generating a random string with minimal chance of a repeat, check this thread: PHP random string generator

I would generate the full length string and then just add your hyphens.

edited May 23 '17 at 12:15

Community

1
1

answered Mar 02 '16 at 03:47

Munsterlander

1,356
1
16
29

1

Thank you for the suggestions and solution. I have updated the script and the code is definitely shorter than my previous one. Aside from using the MySQL unique constraint, I have also updated the function for generating the voucher codes. Here's the updated (working) script: http://hastebin.com/qurakiviya.bash – maikelsabido Mar 02 '16 at 08:01

score 1 · Answer 2 · answered Mar 02 '16 at 03:57

Using this approach to generate random unique data, the amount of processing required increases proportionally as more and more codes are generated.

What I would do instead is:

Generate a whole bunch of values (lets say a few thousand) values sequentially and store them in a redis/SQL database
Use a random number to index that record in the database, and remove the record from the table once it has been used

This reduces the processing required greatly, and also gives you a pre determined pool of voucher codes which could be useful for other purposes in your application

score 1 · Answer 3 · answered Mar 02 '16 at 04:31

Mysql unique constraint may be the solution you are looking for.it ensures a value is always unique. It is like primary key. but unlike primary key a table can have multiple unique values. Here is the link to w3school explaining this www.w3schools.com/sql/sql_unique.asp

The best part is it will genrerate a Duplicate Entry error when adding a duplicate entry. so you can use it to add data to csv . add it only when you have no error.

But make sure the unique value is not null.

How to generate voucher code, check the DB if it's unique, generate new one if not

3 Answers3