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, Jan 23 '19 at 02: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, Jan 23 '19 at 02:23
And are you then binding `$couponCode`? How about executing that prepared statement? — Phil, Jan 23 '19 at 02:34

Bira · Accepted Answer · 2019-01-23T02:38:57.020

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 '19 at 02:38

answered Jan 23 '19 at 02:22

Bira

4,531
2
27
42

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 – Phil Jan 23 '19 at 02:33
1

Let him set that :) We are just helping him to solve his coding problems – Bira Jan 23 '19 at 02:34
is because the single quotation was not closed in the UPDATE or is it something else? – haystax Jan 23 '19 at 02:37
1

sorry forgot to mention its a string varchar – haystax Jan 23 '19 at 02:38
i see that i had to make sure the PARAM and both need their separate execute(). thanks this did the trick. – haystax Jan 23 '19 at 02:46

Manoj Vadehra · Answer 2 · 2019-01-23T02:46:35.307

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.

add PHP variable in SQL WHERE

2 Answers2

Linked