add PHP variable in SQL WHERE

Question

on my first UPDATE statement, im trying to have my WHERE value contain the variable $couponCode but it does not work as of now. This is so that the correct row updates depending on what the input is. any help would be appreciated.

if ($couponCode == $coupons_db3['coupon_code']){
   echo $couponCode;
   $stmt = $db->prepare('UPDATE promocode_3 SET used = 1 WHERE coupon_code ='.$couponCode);
   $stmt = $db->prepare('UPDATE usr_customer_profile SET packageid = 3 WHERE usrcustomerid = :usrcustomerid');
   $stmt->bindValue(':usrcustomerid', $_SESSION['usrcustomerid'], PDO::PARAM_INT);
   $stmt->execute();
   break;
 }

Prepare, bind (ie WHERE coupon_code = :couponCode) and execute your first query in the same way you are doing for your second query — Phil
– Phil, Commented Jan 23, 2019 at 2:18
id like to see an example of that, i already turned the line into: "$stmt = $db->prepare('UPDATE promocode_3 SET used = 1 WHERE coupon_code = :couponCode);" — haystax
– haystax, Commented Jan 23, 2019 at 2:23
And are you then binding $couponCode? How about executing that prepared statement? — Phil
– Phil, Commented Jan 23, 2019 at 2:34

Bira · Accepted Answer · 2019-01-23 02:38:57Z

2

You need to bind the couponCode as well.

if ($couponCode == $coupons_db3['coupon_code']){
       echo $couponCode;
       $stmt = $db->prepare('UPDATE promocode_3 SET used = 1 WHERE coupon_code =:couponCode');
       $stmt->bindValue(':couponCode', $couponCode, PDO::PARAM_STR);
       $stmt->execute();

       $stmt = $db->prepare('UPDATE usr_customer_profile SET packageid = 3 WHERE usrcustomerid = :usrcustomerid');
       $stmt->bindValue(':usrcustomerid', $_SESSION['usrcustomerid'], PDO::PARAM_INT);
       $stmt->execute();
       break;
     }

edited Jan 23, 2019 at 2:38

answered Jan 23, 2019 at 2:22

Bira

5,6263 gold badges31 silver badges43 bronze badges

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

5 Comments

Phil Over a year ago

I wouldn't assume $couponCode is an integer. I mean, it might be but OP hasn't confirmed so I'd just use the default bind type for now

Bira Over a year ago

Let him set that :) We are just helping him to solve his coding problems

haystax Over a year ago

is because the single quotation was not closed in the UPDATE or is it something else?

haystax Over a year ago

sorry forgot to mention its a string varchar

haystax Over a year ago

i see that i had to make sure the PARAM and both need their separate execute(). thanks this did the trick.

Manoj Vadehra · Accepted Answer · 2019-01-23 02:46:35Z

0

Edit

Please ignore.. @Bira's answer is more accurate

Try this:

$stmt = $db->prepare("UPDATE promocode_3 SET used = 1 WHERE coupon_code ='".$couponCode."'");

you missed the quote in coupon code value. P.S. I don't know which database you are using. Please mention that next time. :)

This should work but it's not an ideal case for a prepared statement because in case of prepared statements you should give parameters only at the time of execution.

"prepare" should only compile an sql statement and parameters should be passed later on.

edited Jan 23, 2019 at 2:46

answered Jan 23, 2019 at 2:19

Manoj Vadehra

8464 silver badges17 bronze badges

Collectives™ on Stack Overflow

add PHP variable in SQL WHERE

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related