Validate Promo Code for one time use PHP/My SQLi

Question

I’m very new to the whole MySQLi thing. I have a basic understanding of PHP. Anyway, I’ve searched around here and just haven’t been able to get a solid answer.

Basically, I have a form where a person can enter name, email and promo code. The form validates the name and email but when it comes to the promo code, that’s where I’m getting stuck.

I have a database that has two columns. One is for the codes and the other is for a “used” column – eventually I need to be able to write a “1” to that column when a unique code has been used so it cannot be used again. I’m trying to use some code I found on here, FYI.

Here is the PHP (after connecting) to the database:

if(isset($_POST['sponsorcode']) && !empty($_POST["sponsorcode"])){
   $sponsorcode = mysqli_real_escape_string($link,$_POST['sponsorcode']);
   $query = "SELECT 'sponsorcode' FROM 'teachercodes' WHERE sponsorcode = '$sponsorcode'";
   $result = mysqli_query($link, $query) or die(mysqli_error($link));
   $option = "";
   if(mysqli_num_rows($result)>0){
       while($row=mysqli_fetch_array($result)) {
       $option = "<option value='{$row['codes']}'>{$row['codes']}</option>";
}

Any tips would be GREATLY appreciated! Thanks.

Answer 1

No reason to perform your task as two separate steps. Simply mark the sponsor code as used in the teachercodes table. If the update affected any rows (i.e. mysqli_affected_rows returns 1 or more) then it hasn't been used before and is a valid sponsor code. Something like this:

// Make sure a sponsor code was provided
if (isset($_POST['sponsorcode']) && !empty($_POST['sponsorcode'])) {

    // Escape the sponsor code to prevent SQL injection
    $code = mysqli_real_escape_string($link, $_POST['sponsorcode']);

    // Mark sponsor code as used if possible 
    $sql = 'UPDATE teachercodes SET used=1 WHERE sponsorcode="' . $code . '"';
    mysqli_query($link, $sql) or die(mysqli_error($link));

    if (mysqli_affected_rows($link)) {
        // Sponsor code hasn't been used before and is valid
    }
}