Insert then Update

[Andrew Paget]

Hoping someone can point me in the right direction here. I'm fairly certain its going to be something real trivial but I can't get the following to work. I think I am totally misunderstanding and after many hours confused...

This is a typical post set up...

        $_POST = array(
            'cart_id' => session_id(),
            'transaction_id' => $f3->get('SESSION.payment_id'),
            'transaction_total' => $f3->get('SESSION.OrderSummary')['grandtotal']
        );

        $invoice_management = new DB\SQL\Mapper($f3->get('DB'),'invoice_management');

        if ($f3->get('SESSION.invoice_id')) {
            $invoice_management->load(array('record_id = ?', $f3->get('SESSION.invoice_id')));
        }

        $invoice_management->copyFrom('POST');
        $invoice_management->save();

        if(!$f3->get('SESSION.invoice_id')) {
            $f3->set('SESSION.invoice_id',$f3->get('DB')->lastInsertId());
        }

This is to create a new invoice in the invoice_management table and the details are coming from a cart in the public facing website. The new invoice is being created beautifully but, if I go back to the cart and say update a quantity of a product and then navigate back to the final checkout page...where the data is then stores to the invoice_management table, it does not update the invoice that was already created a few moments back but instead creates a brand new invoice.

If any one can give me a hint, tip, advice on this I would very much appreciate it.

[v.]

change

        if ($f3->get('SESSION.invoice_id')) {
            $invoice_management->load(array('record_id = ?', $f3->get('SESSION.invoice_id')));
        }

        $invoice_management->copyFrom('POST');
        $invoice_management->save();

to

        if ($f3->get('SESSION.invoice_id')) {
            $invoice_management->load(array('record_id = ?', $f3->get('SESSION.invoice_id')));
        $invoice_management->copyFrom('POST');

        $invoice_management->update();
        }

else{

        $invoice_management->copyFrom('POST');
        $invoice_management->save();

[Andrew Paget]

Thank you so much for your reply. 

However, I did try that earlier in my debug and it did not work. And, as I re-read the docs, I interpreted the save() as either inserting or updating depending on the existence (or not) of the record.

With your code (as I tried some days back), the record still gets added as a new record each time.

I am still trying to figure this out and why it will not work.

[v.]

I haven't checked but I am pretty sure it is not even possible to insert a new row when calling the update function.

Check you code again

[ved]

Hello,

Instead of using copyfrom, try adding each field individually.

I'm not sure this is the issue, but looking at the documentation for copyfrom it states:

"This function allows you to hydrate the mapper using an array (or the name of a hive variable containing an array)."

So, what I'm interpreting is that you're trying to hydrate the mapper with your "$invoice_management->load(array('record_id = ?', $f3->get('SESSION.invoice_id')));" call that loads the record that you wish to update.

But then you use copyfrom that will try to rehydrate the mapper with all variables from your request. As your $_POST array has no id field, it just creates a new record.

So, try doing something like:

if ($f3->get('SESSION.invoice_id')) {
     $invoice_management->load(array('record_id = ?', $f3->get('SESSION.invoice_id')));
}

$invoice_management->transaction_id =  $f3->get('SESSION.payment_id') ;

$invoice_management->transaction_total =  $f3->get('SESSION.OrderSummary')['grandtotal'];

//  (...etc)
$invoice_management->save();

Again, not absolutely sure this is the issue as I rarely used copyfrom, but is what I could deduce from the docs.

Cheers

[Andrew Paget]

Thanks again for the replies. So, I have updated my code (tidier and less of it) and now looks like...

// Example Post Array

$_POST = array(
'cart_id' => '1234567890',
'transaction_id' => '12345',
'transaction_total' => '150.50'

);

$invoice_management = new DB\SQL\Mapper($f3->get('DB'),'invoice_management');

$invoice_management->load(array('id = ?',$f3->get('SESSION.invoice_id')));
$invoice_management->copyfrom('POST');
$invoice_management->save();

if(!$f3->exists('SESSION.invoice_id')){

$f3->set('SESSION.invoice_id',$invoice_management->id);

}

Now it is working! :-) 

If the invoice does not exist it will create a new invoice and populate the columns from copyfrom('POST). BUT...if the invoice already exists, it will now only update the fields that have changed! :-)

Interestingly, the second line of the $invoice_management object can also be wrapped in an if(!$f3->exists('SESSION.invoice_id')){} but it makes no difference...either way works...I guess because the invoice_id first time round is empty and so there is no matching invoice in the database to fetch.

Thank you for the replies!