Update mysql table with an array

Question

I've got table: platforms

Inside this table columns: 1. first_scan_date, 2. next_scan_date 3. scan_frequency

Now, I've got such array from the web based formular:

Array
(
    [scan_freq1] => Array
        (
            [0] => 0
            [1] => 0
            [2] => 0
            [3] => 0
        )

    [first_scan_date1] => Array
        (
            [0] => 0000-00-00
            [1] => 0000-00-00
            [2] => 0000-00-00
            [3] => 0000-00-00
        )

    [next_scan_date1] => Array
        (
            [0] => 
            [1] => 
            [2] => 
            [3] => 
        )

)

How can I update it in database using things that came from array in PHP? I guess I need smth like foreach, but have no clue...

UPDATE platforms (first_scan_date,next_scan_date,scan_frequency) VALUES (?,?,?)...

Help?

This thread answers it well http://stackoverflow.com/questions/7884284/simple-update-mysql-table-from-php-array — xdotcommer, Dec 08 '12 at 17:57
how to identify particular row of table ? you want to update all row of table ? — GBD, Dec 08 '12 at 18:09
They're identified by platform type: os, mw, net. This example was for operating systems, so WHERE platformtype='os'. — Pawel, Dec 08 '12 at 18:10
But the array will always cover 1:1 of the things that needs to be updated, so its ok. I just need some code to make it — Pawel, Dec 08 '12 at 18:13

GBD · Answer 1 · 2012-12-08T18:19:54.870

1

Try below way

$cnt = count($arr['scan_freq1']);
for($i=0;$i<$cnt;$i++){
  echo $arr['scan_freq1'][$i];
  echo $arr['first_scan_date1'][$i];
  echo $arr['next_scan_date1'][$i];

//now you have three values make query and update into db

$db->prepare(
     "UPDATE `platforms` SET `first_scan_date`=?, `next_scan_date`=?,
     `scan_frequency`= ? WHERE platformtype='os'"
 )->execute(array($arr['first_scan_date1'][$i],$arr['next_scan_date1'][$i],$arr['scan_freq1'][$i]));
}

edited Dec 08 '12 at 18:19

answered Dec 08 '12 at 17:59

GBD

15,847
2
46
50

well, looking at the table, didn't update, is there any easy way to debug the result of mysql possible error? – Pawel Dec 08 '12 at 18:30

Expedito · Accepted Answer · 2012-12-09T15:34:18.620

1

I would loop through the array to prepare a string to insert in a single query like this:

$sql = "INSERT INTO table (first_scan_date,next_scan_date,scan_frequency)
VALUES ('0000-00-00','0000-00-00',1),('0000-00-00','0000-00-00',1),('0000-00-00','0000-00-00',1)
ON DUPLICATE KEY UPDATE first_scan_date=VALUES(first_scan_date),next_scan_date=VALUES(next_scan_date),scan_frequency=VALUES(scan_frequency);";

You could do something like this:

$freq = $arr['scan_freq1'];
$date1 = $arr['first_scan_date1'];
$date2 = $arr['next_scan_date1'];
foreach ($date1 as $key => $value){
    $row[] = "('".$value."','".$date2[$key]."',".$freq[$key].")";
}
$list = implode(',', $row);
$sql = "INSERT INTO table (first_scan_date,next_scan_date,scan_frequency)
VALUES ".$list."
ON DUPLICATE KEY UPDATE first_scan_date=VALUES(first_scan_date),next_scan_date=VALUES(next_scan_date),scan_frequency=VALUES(scan_frequency);";

edited Dec 09 '12 at 15:34

answered Dec 08 '12 at 18:03

Expedito

7,771
5
30
43

Looks nice, could you please include the loop for querying particular values? – Pawel Dec 08 '12 at 18:08
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5),(2012-12-26,,7),(2012-12-14,,5),(2012-12-07,,8) ON DUPLICATE KEY UPDATE first' at line 2 – Pawel Dec 08 '12 at 19:23
I'm not sure what the problem is. I used that exact syntax and successfully did an update. What do have for the $sql string? – Expedito Dec 08 '12 at 19:36
Can you please change it to only update? not INSERT before? it will be easier I think for me, and still functional. – Pawel Dec 08 '12 at 19:38
Using INSERT is kind of a hack. UPDATE doesn't allow you to use a list like that. – Expedito Dec 08 '12 at 19:42
Can you please post the $sql string? – Expedito Dec 08 '12 at 19:46
There's just one thing I didn't mention, need to include WHERE platform_type='os', where should I put it? – Pawel Dec 08 '12 at 19:46

score 0 · Answer 3 · answered Mar 09 '19 at 00:26

this may be late but may help someone in the now or the future i believe here is the shortest way to achieve this

public function update($table,$values,$where_clause)
{
  foreach ($values as $key => $val)
  {
    $valstr[] = $key." = '$val'";
  }

 $update_query = 'UPDATE '.$table.' SET '.implode(', ', $valstr)
 ." WHERE ".$where_clause; 
  return $update_query;
}

found this in one of codeigniters core files it simply loops through all values stores them in an array then converts the array into a string and adds it to the query and the where clause comes last..i hope this helps someone

Update mysql table with an array

3 Answers3