1074

How to use Custom update query by using inbuilt update function

Showing 1 to 10 of 17 comments.

Comments for “How to use Custom update query by using inbuilt update function”
 

Posted by siri on Wednesday 2nd February 2022 at 05:09 GMT

Hello every one,
Currently I am using this update query
$upd2 = "UPDATE warehouse_feedback set final_status = 'DELIVERED', updated_on = '".$sel_res[$i]['receipt_delivery']."' where order_id = '".$sel_res[$i]['order_id']."' and tracking_id = '".$sel_res[$i]['tracking_id']."'";
$this->model->exec($upd2);
But I would like to use inbuilt update function . How to use this query with that function ? or Can you please suggest Custom update function like for the above requirement ? like by passing multiple values in where clause.
Siri
Thanks in advance
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

Posted by djnordeen on Wednesday 2nd February 2022 at 11:06 GMT

Hello,
https://trongate.io/docs_m/information/the-update-method
In order for this to work you need to get the update_id of the record you are updating.
So, I assume from this query you can get the update_id.
$update_id = Query update_id;

Then set the $data['variables to update']
And run the update method
***Important***
This assumes that you are in the Warehouse_Feedback controller.
If not then use 1 more line below to set the module

$data['final_status'] = 'DELIVERED';
$data['update_on'] = $sel_res[$i]['receipt_delivery'];
$this->module->('warehouse_feedback');
$this->model->update($update_id, $data, 'warehouse_feedback');

****This may help you****DEBUG MODE
https://trongate.io/docs_m/information/how-to-use-debug-mode

Hope this helps,
Dan

This comment was edited by djnordeen on Wednesday 2nd February 2022 at 11:09 GMT

Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by siri on Wednesday 2nd February 2022 at 12:53 GMT

Hi,
Thanks for the reply. As you said I can get the update id from query. But there is a possibility of duplicate tracking ids for the order id. Means I will have multiple duplicate order ids and multiple duplicate tracking ids. So If i update based on the primary key then I need to run updated query multiple times. So Instead that, If i run this query like set status = 'DELIVERED' where order_id = 'order_id' and tracking_id = 'tracking_id'
then this one query will update all the related order id and tracking ids status.
Any Ideas/ suggestions?
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

Posted by djnordeen on Wednesday 2nd February 2022 at 13:36 GMT

Ok,
So is it like this
One Customer has many Order id's and One Order has many Tracking id's?
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by siri on Wednesday 2nd February 2022 at 13:48 GMT

Hi,
Correct, but in warehouse feedback table we have only order ids and related tracking info
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

Posted by djnordeen on Wednesday 2nd February 2022 at 14:05 GMT

Does the query above work as you want?
Early Adopter

djnordeen

User Level: Early Adopter

Date Joined: 20/08/2021

Posted by siri on Wednesday 2nd February 2022 at 14:14 GMT

Yes , please
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

Posted by Myhiideas on Wednesday 2nd February 2022 at 14:19 GMT

Okay here goes. UPDATED
Do you know how to set the Model Debug to true?
Is so do that to see how the query looks.
Test this out. No Promises it will work.
If debug is set you can verify the query is what you are looking for.
$data['final_status'] = 'DELIVERED';
$data['updated_on'] = $sel_res[$i]['receipt_delivery'];

$order_id = $sel_res[$i]['order_id'];

$tracking_id = $sel_res[$i]['tracking_id'];

$this->model->order_tracking_update($order_id, $tracking_id $data, 'warehouse_feedback');


private function order_tracking_update($order_id, $tracking_id $data, $target_tbl=NULL) {

if (!isset($target_tbl)) {
$target_tbl = $this->get_table_from_url();
}

$sql = "UPDATE `$target_tbl` SET ";

foreach ($data as $key => $value) {
$sql.= "`$key` = :$key, ";
}

$sql = rtrim($sql, ', ');
$sql.= " WHERE `$target_tbl`.`order_id` = :order_id AND `$target_tbl`.`tracking_id` = :tracking_id";

$data['id'] = $update_id;
$data = $data;

if ($this->debug == true) {
$query_to_execute = $this->show_query($sql, $data, $this->query_caveat);
}
$this->prepare_and_execute($sql, $data);
}

This comment was edited by Myhiideas on Wednesday 2nd February 2022 at 14:36 GMT

Myhiideas

User Level: Guest

Date Joined: 1/11/2021

Posted by siri on Wednesday 2nd February 2022 at 14:31 GMT

Hi thank you
Just this was missed
$this->prepare_and_execute($sql, $data);

I will let you know. Hope this will work
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

Posted by siri on Wednesday 2nd February 2022 at 14:49 GMT

Hi,
tested and did some modifications. The working one
controller
function updatetest()
  {
       $data['final_status'] = 'DELIVERED';
        $data['updated_on'] = '2022-02-04';
     $order_id = '6016122701458079';
       $tracking_id = '01225037346502';
      $this->model->order_tracking_update($order_id, $tracking_id, $data, 'warehouse_feedback');
    }


Model
public function order_tracking_update($order_id,$tracking_id,$data, $target_tbl=NULL) 
    {

        if (!isset($target_tbl)) {
            $target_tbl = $this->get_table_from_url();
        }

        $sql = "UPDATE `$target_tbl` SET ";

        foreach ($data as $key => $value) {
            $sql.= "`$key` = :$key, ";
        }
        $data['order_id'] = $order_id;
        $data['tracking_id'] = $tracking_id;
        $data = $data;
        $sql = rtrim($sql, ', ');
        $sql.= " WHERE `$target_tbl`.`order_id` = :order_id AND `$target_tbl`.`tracking_id` = :tracking_id";

        if ($this->debug == true) {
            $query_to_execute = $this->show_query($sql, $data, $this->query_caveat);
        }

        $this->prepare_and_execute($sql, $data);
    }
Early Adopter

siri

User Level: Early Adopter

Date Joined: 19/07/2019

×