CODEWITHSUNDEEP
phpmysqlimplode
Denis
Denis

Reputation: 73

How to update multiple data with implode in PHP

I have a script for inserting multiple data in PHP with implode method but I don't know how to use it in UPDATE syntax.

<input type="text" name="field_name" value="unyil"/>

With PHP like this:

foreach($_POST['id_table'] as $row=>$id_table)
{
    $id_table=$id_table;
    $full_name=$_POST['full_name'][$row];

    $query_row[] = "('$id_table','$full_name')";
}

$sql="INSERT INTO tb_client_table_rows (id_table, full_name) VALUES " . implode(',',$query_row); 

if (!mysql_query($sql))
{
    die('Error: ' . mysql_error());
}

But how can I use that for UPDATING row values?

Upvotes: 1

Views: 2086

Answers (2)

Nisse Engstr&#246;m
Nisse Engstr&#246;m

Reputation: 4752

Mohamed Hossam has posted the following in a comment to the MySQL reference manual (archived):

A very server resources friendly method to update multiple rows in the same table is by using WHEN THEN (with a very important note).

UPDATE tbl_name SET fld2 = CASE fld1
WHEN val1 THEN data1
WHEN val2 THEN data2
ELSE fld2 END

The note is: do not forget ELSE. If you do not use it, all rows that are outside the range of your updated values will be set to blank!

(Emphasis and formatting added.)

Based on this, you can use implode to construct an UPDATE query with multiple values:

<?php

  /* Sample data: */

  $_POST['id_table'] = array ('1746', '1834', '1944');
  $_POST['full_name'] = array ('lumbar', 'bezoar', 'sniggle');

  /* Code */

  foreach($_POST['id_table'] as $row=>$id_table)
  {
    /* Don't forget to escape data to avoid SQL injection */

    $id_table  = mysql_real_escape_string ($id_table);
    $full_name = mysql_real_escape_string ($_POST['full_name'][$row]);

    $query_row[] = "'$id_table' THEN '$full_name'";
  }

  $query = "UPDATE tb_client_table_rows\n"
         . "   SET full_name = CASE id_table\n"
         . "                     WHEN "

         . implode ("\n                     WHEN ", $query_row) 

         . "\n                     ELSE full_name\n"
         . "                   END";

  echo "$query\n";

Output:

UPDATE tb_client_table_rows
   SET full_name = CASE id_table
                     WHEN '1746' THEN 'lumbar'
                     WHEN '1834' THEN 'bezoar'
                     WHEN '1944' THEN 'sniggle'
                     ELSE full_name
                   END

Note that the MySQL extension has been deprecated and is no longer maintained. You should be using MySQLi or PDO these days.

Upvotes: 1

VIDesignz
VIDesignz

Reputation: 4783

Don't use implode...just do

$sql="UPDATE tb_client_table_rows SET full_name=$full_name WHERE id_table=$id_table";

Upvotes: 0

Related Questions