Codeigniter update_batch in Core PHP-CodePudding

everyone.

In the codeigniter there is update_batch function by using it we are able to bulk update multiple rows.

$this->db->update_batch('table_name', $update_data_array, 'where_condition_field_name');

I want similar functionality in core PHP in one function or in one file. Is there any workaround? Is there any way to extract update_batch function from Codeigniter in one file/function? I tried to extract this function but it is very lengthy process and there will be many files / functions should be extracted.

Please help me in this regard

Thanks in advance

CodePudding user response：

You can also insert multiple rows into a database table with a single insert query in core php.

(1)One Way

<?php 
$mysqli = new mysqli("localhost", "root", "", "newdb");
  
if ($mysqli == = false) {
    die("ERROR: Could not connect. ".$mysqli->connect_error);
}
  
$sql = "INSERT INTO mytable (first_name, last_name, age) 
           VALUES('raj', 'sharma', '15'),
    ('kapil', 'verma', '42'),
    ('monty', 'singh', '29'),
    ('arjun', 'patel', '32') ";
    if ($mysqli->query($sql) == = true)
{
    echo "Records inserted successfully.";
}
else
{
    echo "ERROR: Could not able to execute $sql. "
        .$mysqli->error;
}
  
$mysqli->close();
? >

(2)Second Way

Let's assume $column1 and $column2 are arrays with same size posted by html form.

You can create your sql query like this:-

<?php
    $query = 'INSERT INTO TABLE (`column1`, `column2`) VALUES ';
    $query_parts = array();
    for($x=0; $x<count($column1); $x  ){
        $query_parts[] = "('" . $column1[$x] . "', '" . $column2[$x] . "')";
    }
    echo $query .= implode(',', $query_parts);
?>

CodePudding user response：

You can easily construct similar type of query using PHP.

Lets use array containing key value pair and implode statement to generate query. Here’s the snippet.

<?php 
$coupons = array(
  1 => 'val1',
  2 => 'va2',
  3 => 'val3',
);
$data = array();    
foreach ($coupons AS $key => $value) {
$data[] = "($key, '$value')";
}
$query = "INSERT INTO `tbl_update` (id, val) VALUES " . implode(', ', $data) . " ON DUPLICATE KEY UPDATE val = VALUES(val)";
$this->db->query($query); 
?>

Alternatively, you can use CASE construct in UPDATE statement. Then the query would be something

like:

UPDATE tbl_coupons
SET code = (CASE id WHEN 1 THEN 'ABCDE'
             WHEN 2 THEN 'GHIJK'
             WHEN 3 THEN 'EFGHI'
     END)
WHERE id IN(1, 2 ,3);