如何生成凭证代码，检查数据库是否唯一，如果不是则生成新数据库

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.

Answer 1

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.