Update SQL Query not executing

Question

I have a system in which when a user uses a coupon, a "taken" column is filled with a string which I run IS NULL against to render the coupon consumed. My problem is that my update_coupon function is not updating the column as I expect. The first time it worked, but then I must have changed something and subsequent queries no longer work.

Sorry for the noob question. Thank you for any help.

    function is_valid_coupon($card_code){
        global $db;
        $code = sha1($card_code);
        $query = 'SELECT * FROM giftcards where card_code = :code';
        $statement = $db->prepare($query);
        $statement->bindValue(':code', $code);
        $statement->execute();
        $valid = ($statement->rowCount() == 1);
        $statement->closeCursor();
        return $valid;
    }

    function update_coupon($card_code){
        global $db;
        $code = sha1($card_code);
        $query = "
        UPDATE `giftcards`
        SET taken = used
        WHERE card_code = :card_code";
        $statement = $db->prepare($query);
        $statement->bindValue(':card_code', $code);
        $statement->execute();
        $statement->closeCursor();
    }

Which are executed like so:

    if ($card_code){ 
        if (is_valid_coupon($card_code)){
          update_coupon($card_code);
        }
    }

In SET taken = used do you mean to set it to a value of used - which should be SET taken = 'used' with quotes? — Nigel Ren
– Nigel Ren, Commented Aug 31, 2019 at 7:37
You call is_valid_coupon() but at no point do you also check if the coupon has already been used, so in theory you can keep on using the same coupon - if that matters at all. — Nigel Ren
– Nigel Ren, Commented Aug 31, 2019 at 7:39
Wouldn't is_valid_coupon return a boolean if used or not? I then use that value to update_coupon. — user123
– user123, Commented Aug 31, 2019 at 15:43
I just found out is_valid_coupon returns false, I don't understand why because the coupon should be in the database — user123
– user123, Commented Aug 31, 2019 at 17:16

Lajos Arpad · Accepted Answer · 2019-08-31 12:12:31Z

2

This line is causing the problem:

SET taken = used

you need to put apostrophes before and after used:

SET taken = 'used'

answered Aug 31, 2019 at 12:12

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user123 Over a year ago

I did try this and it still did not update the database.

Lajos Arpad Over a year ago

@user123 What is card_code that is passed to the query?

user123 Over a year ago

$card_code = strtoupper( substr(base_convert(sha1(uniqid(mt_rand())), 16, 36), 0, 5) . '-' . substr(base_convert(sha1(uniqid(mt_rand())), 16, 36), 0, 5) . '-' . substr(base_convert(sha1(uniqid(mt_rand())), 16, 36), 0, 5) ); **so it's the coupon code

Lajos Arpad Over a year ago

@user123 mt_rand() is random. If you randomize this value, then you will get a random value as a result. Now, since you are filtering by this random value (calculated by Mersenne Twister algorithm), it's quite possible that it will not match any of the records you store, hence not updating anything. Can you take a value that you actually have and hard-code it into your SQL temporally just to see whether the filter is the only problem?

Collectives™ on Stack Overflow

Update SQL Query not executing

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related