CODEWITHSUNDEEP
phpmysqlsql
Satheesh Narayanan
Satheesh Narayanan

Reputation: 321

Comma separated string as field and data - update query in php mysql

I'm having following data 

$field  = 'f1','f2','f3'; // (comma separated string)
$value = 'v1','v2','v3'; // (comma separated string)

Is there any shortcut method to generate update query like this.

UPDATE table set ($field = $value) where id =1;

This method is working on insert query, But i dont know how to use like this for update query.

Upvotes: 0

Views: 4490

Answers (3)

DaveRandom
DaveRandom

Reputation: 88647

I think the best way to approach things like this is by storing the data in associative arrays, not strings like you have used:

$data = array(
  'f1' => 'v1',
  'f2' => 'v2',
  'f3' => 'v3'
);

// Then for an insert:
$query = "INSERT INTO table (`".implode("`, `", array_keys($data))."`) VALUES ('".implode("', '", array_values($data))."')";

// And an update
$updates = array();
foreach ($data as $key => val) $updates[] = "`$key` = '$val'";
$query = "UPDATE table SET ".implode(', ',$updates)." WHERE `id` = 1";

// Or even a select
$where = array();
foreach ($data as $key => val) $where[] = "`$key` = '$val'";
$query = "SELECT * FROM table WHERE ".implode(' AND ',$where);

If you are doing this, you need to ensure that you escape the data as you build the array. Or better yet, use prepared statements.

Upvotes: 5

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Someting like this, but you need to make your values safer:

$field  = 'f1,f2,f3'; // (comma separated string)
$value = 'v1,v2,v3'; // (comma separated string)



    $fieldArr = explode(",", $field);
    $valArr = explode(",", $value);
    $d = array_map("make_it", $fieldArr, $valArr);

    function make_it($a, $b) {
      return $a.'='.$b;
    }
    $up = "UPDATE table set ";
    foreach($d as $key => $val) {
        $up .=$val.',';
    }
  $up = rtrim($up, ',');
  echo $up .=' where id =1;'

Upvotes: 1

mishu
mishu

Reputation: 5397

the update syntax is update table set field1 = value1, field2=value2 so you can't have a list of fields and after that a list of values (insert can be used like that also, but not the other way around)

so you need a more advanced solution.. like making those two variables arrays (maybe using explode), iterate those arrays and create the lines one by one

Upvotes: 3

Related Questions