How to update multiple rows in a table?

Question

I have to update the table daytot with the values present in the array named $result3.

How could this be done?

$this->db->select('tdate');
$this->db->where('tdate >=', $newDate);
$this->db->where('tdate <=', $date);
$result3=$this->db->get('daytot')->result_array();
$amount=$this->input->post('credit1');
for($i=0;$i<count($result3);$i++) {
    $opcash=$result3[$i]['opcash']+$amount;
    $data1 = array(
        'tdate'=>$newDate,
        'total_credit'=>$total_credit['amount'],
        'total_debit'=>$total_debit['amount'],
        'opcash'=>$opcash,
    ;
    $this->db->where('tdate', $result3[$i]);
    $this->db->update('daytot', $data1);
}

i have more than one value in $result3 and i want to update opcash value to the values(dates) present in a $result3array — laavanya dharani, Dec 28 '18 at 08:48
i want to update opcash to all the date which is present in $result3 array — laavanya dharani, Dec 28 '18 at 08:57
$result3 array have the tdate values..that is opcash needed to be updated for the $result3 array values — laavanya dharani, Dec 28 '18 at 09:04
i can't use like that because i have more than one index..so i am using for loop — laavanya dharani, Dec 28 '18 at 09:05
array(1) { [0]=> array(1) { ["tdate"]=> string(10) "24/12/2018" } } array(2) { [0]=> array(1) { ["tdate"]=> string(10) "25/12/2018" } } $result3 array value — laavanya dharani, Dec 28 '18 at 09:08
I am getting undefined offset in $this->db->where('tdate', $result3[$i]); line@splash58 — laavanya dharani, Dec 28 '18 at 09:09
In this line of code $opcash=$result3[$i]['opcash']+$amount; have issue. Because offset 'opcash' does not exists — Danish Ali, Dec 28 '18 at 09:14
added opcash to the $result3 array still having the same issue — laavanya dharani, Dec 28 '18 at 09:17
Now this is the structure? array(1) { [0]=> array(1) { ["tdate"]=> string(10) "24/12/2018", ["opcash"] => string(10) 'value'} } array(2) { [0]=> array(1) { ["tdate"]=> string(10) "25/12/2018", , ["opcash"] => string(10) 'value' } } $result3 — Danish Ali, Dec 28 '18 at 09:19
yes..{ ["tdate"]=> string(10) "24/12/2018" ["opcash"]=> string(3) "500" } { ["tdate"]=> string(10) "25/12/2018" ["opcash"]=> string(3) "1000" } — laavanya dharani, Dec 28 '18 at 09:20

score 0 · Answer 1 · answered Dec 28 '18 at 09:22

0

Try to use foreach loop for update the record

$result3 array structure is

{ ["tdate"]=> string(10) "24/12/2018" ["opcash"]=> string(3) "500" } { ["tdate"]=> string(10) "25/12/2018" ["opcash"]=> string(3) "1000" }

So try like this. And make sure you have all the variable values

$this->db->select('tdate');
$this->db->where('tdate >=', $newDate);
$this->db->where('tdate <=', $date);
$result3=$this->db->get('daytot')->result_array();
$amount=$this->input->post('credit1');

Now check if $result3 have record or not

if(isset($result3) && count($result3) > 0){
foreach($result3 as $result) {
    $opcash=$result['opcash']+$amount;
    $data1 = array(
    'tdate'=>$newDate,
    'total_credit'=>$total_credit['amount'],
    'total_debit'=>$total_debit['amount'],
    'opcash'=>$opcash,
   ;
    $this->db->where('tdate', $result['tdate']);
   $this->db->update('daytot', $data1);
}
}

answered Dec 28 '18 at 09:22

Danish Ali

2,354
3
15
26

I want opcash to be updated fo be update for all the date present in $resul3 array – laavanya dharani Dec 28 '18 at 09:40
not working..i have to update the opcash value to all the date that is presnt in $result3 array – laavanya dharani Dec 28 '18 at 10:22
did you understand my problem?? – laavanya dharani Dec 28 '18 at 10:57
@laavanyadharani yes. – Danish Ali Dec 28 '18 at 11:01
getting undefined index opcash and tdate – laavanya dharani Dec 28 '18 at 11:05

score 0 · Answer 2 · answered Dec 28 '18 at 09:39

0

Try below method. id is a unique value from the table daytot, you can use any unique field.

$this->db->select('id, tdate, opcash'); //id => primary key
$this->db->where('tdate >=', $newDate);
$this->db->where('tdate <=', $date);
$result3 = $this->db->get('daytot')->result_array();
$amount  = $this->input->post('credit1');

Using For

$data1 = array();
for ($i = 0; $i < count($result3); $i++) {
    $opcash = $result3[$i]['opcash'] + $amount;
    $data1[]  = array(
        'id'        => $result3[$i]['id'],
        'tdate'        => $newDate,
        'total_credit' => $total_credit['amount'],
        'total_debit'  => $total_debit['amount'],
        'opcash'       => $opcash
    );    
}
$this->db->update_batch('daytot', $data1, 'id');

OR Using Foreach

foreach($result3 as &$data){
    $opcash = $data['opcash'] + $amount;
    $data['tdate'] = $newDate;
    $data['total_credit'] = $total_credit['amount'];
    $data['total_debit'] = $total_debit['amount'];
    $data['opcash'] = $opcash;    
}
$this->db->update_batch('daytot', $result3, 'id');

answered Dec 28 '18 at 09:39

Karthik Sekar

625
4
7

why for loop will not work, could you explain it please? because I couldn't find any issue. – Karthik Sekar Dec 28 '18 at 09:49
@KarthikSekar getting undefined index tdate – laavanya dharani Dec 28 '18 at 09:52
i have to update the opcash value where tdate present in $result3 array – laavanya dharani Dec 28 '18 at 09:57
error message: Undefined index: tdate UPDATE `daytot` SET `tdate` = CASE WHEN `tdate` = THEN '24/12/2018' ELSE `tdate` END, `total_credit` = CASE WHEN `tdate` = THEN '2500' ELSE `total_credit` END, `total_debit` = CASE WHEN `tdate` = THEN NULL ELSE `total_debit` END, `opcash` = CASE WHEN `tdate` = THEN 1000 ELSE `opcash` END WHERE `tdate` IN() – laavanya dharani Dec 28 '18 at 09:58
looks like tdate field is empty in your table. print array before execute update_batch – Karthik Sekar Dec 28 '18 at 09:59
array(1) { [0]=> array(2) { ["tdate"]=> string(10) "24/12/2018" ["opcash"]=> string(3) "500" } } Array ( [0] => Array ( [tdate] => 24/12/2018 [total_credit] => 3500 [total_debit] => [opcash] => 1000 ) )this is my data1 value – laavanya dharani Dec 28 '18 at 10:03
print the complete array before update_batch, like based on my code if you use for print_r($data1), if you use foreach print_r($result3) – Karthik Sekar Dec 28 '18 at 10:10
yes done like this rray(2) { [0]=> array(2) { ["tdate"]=> string(10) "24/12/2018" ["opcash"]=> string(3) "500" } [1]=> array(2) { ["tdate"]=> string(10) "25/12/2018" ["opcash"]=> string(4) "1000" } } data1 value..but getting undefined index tdate – laavanya dharani Dec 28 '18 at 10:19
Let me check in my local – Karthik Sekar Dec 28 '18 at 10:29
@laavanyadharani, I am able to update, please refer my screenshots https://prnt.sc/m0kbma https://prnt.sc/m0kcad – Karthik Sekar Dec 28 '18 at 11:31
$data1 = array(); for ($i = 0; $i < count($result3); $i++) { $opcash = $result3[$i]['opcash'] + $amount; $data1[] = array( 'tdate' => $result3[$i]['tdate'], 'total_credit' => $total_credit['amount'], 'total_debit' => $total_debit['amount'], 'opcash' => $opcash ); } i have changed my data1 like this – laavanya dharani Dec 28 '18 at 12:18
@KarthikSekar still getting undefined index tdate – laavanya dharani Dec 28 '18 at 12:20
I think your update_batch data variable is wrong, yours should be $this->db->update_batch('daytot', $data1, 'tdate'); – Karthik Sekar Dec 28 '18 at 13:22
yes used like this..now getting Filename: C:/xampp/htdocs/Finance/system/database/DB_query_builder.php Line Number: 1898 error – laavanya dharani Dec 29 '18 at 05:23
sorry..I don't have skype – laavanya dharani Dec 29 '18 at 06:03
can i connect you through teamviewer or anydesk?? – laavanya dharani Dec 29 '18 at 06:08
lets discuss here https://chat.stackoverflow.com/rooms/185953/how-to-update-the-multiple-rows – Karthik Sekar Dec 31 '18 at 01:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185957/discussion-between-laavanya-dharani-and-karthik-sekar). – laavanya dharani Dec 31 '18 at 04:52

score 0 · Answer 3 · answered Dec 28 '18 at 11:18

0

First you need to select opcash column too :

$this->db->select('tdate,opcash');

Then apply an index 0 on each foreach iteration :

if(isset($result3) && count($result3) > 0){
    foreach($result3 as $r) {
        $opcash=$r[0]['opcash']+$amount;
        $data1 = array(
            'tdate'=>$newDate,
            'total_credit'=>$total_credit['amount'],
            'total_debit'=>$total_debit['amount'],
            'opcash'=>$opcash,
        );
        $this->db->where('tdate', $r[0]['tdate']);
        $this->db->update('daytot', $data1);
    }
}

answered Dec 28 '18 at 11:18

Hasta Dhana

4,699
7
17
26

getting undefined offset 1 – laavanya dharani Dec 28 '18 at 11:26
`undefined offset 1` at what line? – Hasta Dhana Dec 28 '18 at 11:53
$opcash=$r[0]['opcash']+$amount;$this->db->where('tdate', $r[0]['tdate']);in both the lines – laavanya dharani Dec 28 '18 at 12:09
what happen if you remove the `[0]` on each line? – Hasta Dhana Dec 28 '18 at 12:35
invalid argument suppplied for foreach – laavanya dharani Dec 28 '18 at 12:40
not the `0` on `foreach` line, but on `$r[0]` line... change all `$r[0]` to `$r` and what is the output? – Hasta Dhana Dec 28 '18 at 13:26

How to update multiple rows in a table?

3 Answers3