creating mysql update string based on conditions

Question

I have list of parameters and their values. I want to build mysql update string based on whether values are present or not. One way to check using if condition like below

if (!empty($value['p_status']) || !empty($value['quantity'])){
      $strSet = '';
      if (!empty($value['p_status'])) {
             $strSet .= "status='$value[p_status]',";
       }
      if (!empty($value['quantity'])) {
            $strSet .= "amount='$value[quantity]',";
      }
      if (!empty($value['shipping_freight'])) {
          $strSet .= "shipping_freight='$value[shipping_freight]',";
      }
   }
   $strSet = trim($strSet,',');
   db_query("update table1 set ".$strSet." where pr1=123");

But there can be lots of parameters. Is there is any better way to do it ?

loop it like foreach($value as $key =>$value) then if (!empty($key)) — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Jul 4, 2015 at 6:21

dg131 · Accepted Answer · 2015-07-04 06:25:05Z

2

To avoid writing so many if conditions, simply use a loop.

foreach($values as $key=>$value)
{
    if(!empty($value))
        str = str." $key = '$value',";
}
$str=trim($str,',');

answered Jul 4, 2015 at 6:25

dg131

1568 bronze badges

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

Comments

dhh · Accepted Answer · 2015-07-08 05:33:33Z

0

if your $value array can use the same keys as the MySQL field names that would be much simpler!
e.g. if $value['p_status'] could simply be $value['status'] and $value['quantity'] could be $value['amount'] then you could do something like this:

$strSet = '';
foreach ($value as $field_name=>$field_value) {
  if (!empty($strSet)) $strSet .= ',';
  if (!empty($field_value)) $strSet .= "{$field_name}='{$field_value}'";
}
db_query("update table1 set ".$strSet." where pr1=123");

Oh, and remember:
In PHP the empty() function will return true for NULL, an empty string and zero - so if the quantity/amount is zero your sql won't update the field!
Also, you should really escape the values you are updating to prevent sql injection.

edited Jul 8, 2015 at 5:33

dhh

4,3578 gold badges46 silver badges61 bronze badges

answered Jul 4, 2015 at 6:47

Wee Zel

1,3249 silver badges11 bronze badges

Collectives™ on Stack Overflow

creating mysql update string based on conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related