[El Forum]

[eluser]Ignis Avis[/eluser]
I am parsing a CSV file to add recrods to a table. this is how I do it.

Code:

foreach($csvData as $key => $row) {
            $data_n[$key] = array(
                'mf_date' => $row['mf_date'],
                'mf_work_id' => $row['mf_work_id'],
                'mf_sender' => $row['mf_sender'],
                'mf_amount' => $row['mf_amount'],
                'mf_trx_id' => $row['mf_trx_id'],
                );
            $this->db->insert('monthly_fee', $data_n[$key]);
        }

I have another table named monthly_due where there is three columns

Code:

1)md_work_id
2)md_ue
3)md_paid

When uploading a csv file to store data to table monthly fee
i also want to update the monthly_due table where mf_work_id from csv matches md_work_id.

Code:

md_due = md_due - 'mf_amount'
md_paid = md_due - 'mf_amount'

How should I do it?

[El Forum]

[eluser]Flemming[/eluser]
You could create a new function, e.g. update_monthly_due() and pass in parameters: md_work_id, mf_amount

Then your new function will do a mysql update like this:

UPDATE monthly_due set md_due = md_due - $mf_amount, md_paid = md_paid - $mf_amount WHERE md_work_id = $md_work_id

Code:

function update_monthly_due($md_work_id, $mf_amount)
{
$sql = "UPDATE monthly_due set md_due = md_due - " . $mf_amount . ", md_paid = md_paid - " . $mf_amount . " WHERE md_work_id = " . $md_work_id;
$this->db->query($sql);
}

then call that function from within your existing code:

Code:

foreach($csvData as $key => $row) {
            $data_n[$key] = array(
                'mf_date' => $row['mf_date'],
                'mf_work_id' => $row['mf_work_id'],
                'mf_sender' => $row['mf_sender'],
                'mf_amount' => $row['mf_amount'],
                'mf_trx_id' => $row['mf_trx_id'],
                );
            $this->db->insert('monthly_fee', $data_n[$key]);
            $this->update_monthly_due($row['mf_work_id'], $row['mf_amount']);
        }

That should do it?

Of course you could move the new function to a model and then call it from your existing code:

Code:

...
$this->some_model->update_monthly_due($row['mf_work_id'], $row['mf_amount']);

I may have got some syntax wrong but that's roughly how I would do it, I think!

[El Forum]

[eluser]Ignis Avis[/eluser]
@Flemming

I have implemented what you suggested. But now I am getting an DB error

Code:

Error Number: 1054

Unknown column 'MCS20145B41' in 'where clause'

UPDATE monthly_due set md_due = md_due - 5500, md_paid = md_paid + 5500 WHERE md_work_id = MCS20145B41

Filename: F:\xampp\htdocs\foo\system\database\DB_driver.php

Line Number: 330

Why is this happening?

[El Forum]

[eluser]Tim Brownlaw[/eluser]

Code:

function update_monthly_due($md_work_id, $mf_amount)
{
$sql = "UPDATE monthly_due set md_due = md_due - " . $mf_amount . ", md_paid = md_paid - " . $mf_amount . " WHERE md_work_id = " . $md_work_id;
$this->db->query($sql);
}

Your md_work_id is obviously a string and not an integer... So it needs to be wrapped in quotes ''... So you would then have...

Code:

function update_monthly_due($md_work_id, $mf_amount)
{
$sql = "UPDATE monthly_due set md_due = md_due - " . $mf_amount . ", md_paid = md_paid - " . $mf_amount . " WHERE md_work_id = '{$md_work_id}'";
$this->db->query($sql);
}

The {} is a little trick for helping to separate variables in a Double Quoted String. You could try it on the others... but see if that works first.