Jump to content

Query to update order current_state and order_history


Recommended Posts

Hello,

My shop is built with Prestashop 1.5.6.2.

I would like to use an SQL UPDATE query to do the following :

1-Change the state of a list of orders to state 100 (100 is the state related to shipped order)

So, in the ps_orders Table, I need to update the current_state value to 100
This first part is easy.

2- But, I would like also to do simultaneously the following :
- create entries in the ps_order_history table as follows :
id_order_history : a value to be incremented for each new entry
id_employee : always the same, as 0, for instance
id_order : list to be included in the query
id_order_state : 100
date-add : the current date

Would anyone know how to make this query ?

I thank you in advance for any reply.

Patrick

Edited by patrmich (see edit history)
Link to comment
Share on other sites

Updates all orders that have a current status of 4 and changes them to a status of 100.

Save the script as update-state.php in the root of your prestashop.

Then call the script from the URL, eg https://mydomain.com/update-state.php

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $old_order_state = 4;
    $new_order_state = 100;
    
    $get_orders = Db::getInstance()->ExecuteS('SELECT id_order FROM '._DB_PREFIX_.'orders WHERE current_state = '.$old_order_state.' ORDER BY id_order');
    
    if (isset($get_orders)) {
        foreach ($get_orders as $orders) {
            $order = new Order($orders['id_order']);
            $history  = new OrderHistory();
            $history->id_order = (int)$order->id;
            $history->changeIdOrderState((int) $new_order_state, $order->id);
            
            if ($history->save()) 
                {echo 'OK - update order: '.$orders['id_order'].'<br />';} 
              else 
                {echo 'ERROR - update order: '.$orders['id_order'].'<br />';}
        } 
    }
?>

 

Link to comment
Share on other sites

Hi

I thank you very much for your kind reply.

1- I would like to change the state of a list of orders, according to the id_order value.
The id_order list could be as follows :
10201, 10202, 10204, 10206, 10310,...

In your script where could I add this list ?

2- In your script, you assume that the previous state is equal to 4

$old_order_state = 4;


In fact the previous value could have different value, such as 4 or 5 for instance.

I thank you again in advance for any reply.

Patrick

Link to comment
Share on other sites

https://www.php.net/manual/en/control-structures.foreach.php

$get_orders = array();

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $new_order_state = 100;
    
    $get_orders = array(10201,10202,10205,10210);
    
        foreach ($get_orders as $orders) {
            $order = new Order($orders);
            $history  = new OrderHistory();
            $history->id_order = (int)$order->id;
            $history->changeIdOrderState((int) $new_order_state, $order->id);
            
            if ($history->save()) 
                {echo 'OK - update order: '.$orders.'<br />';} 
              else 
                {echo 'ERROR - update order: '.$orders.'<br />';}
        } 

?>

 

Edited by Guest (see edit history)
Link to comment
Share on other sites

It would also be good to add to the treatment codes if the current status of the order is not already written.

 

<?php
    require(dirname(__FILE__).'/config/config.inc.php');
    
    $new_order_state = 100;
    
    $get_orders = array(10201,10202,10205,10210);
    
        foreach ($get_orders as $orders) {
            $order = new Order($orders);
            $_state = (int) $order->current_state;
            if ($_state == $new_order_state)
                {Continue;}
            else
                { 
                    $history  = new OrderHistory();
                    $history->id_order = (int)$order->id;
                    $history->changeIdOrderState((int) $new_order_state, $order->id);
            
                    if ($history->save()) 
                        {echo 'OK - update order: '.$orders.'<br />';} 
                    else 
                        {echo 'ERROR - update order: '.$orders.'<br />';}
             } 
          }

?>

No more advice needed.

Edited by Guest
No more advice needed. (see edit history)
Link to comment
Share on other sites

Hello,

Thank you very much for your posts.

This evening I used the php file several times.

Everything worked well

But, later on, I made a mistake in the array content.

I put a comma at the beginning of the list, such as 

 $get_orders = array(,11201,11202,11205,11210);

When running the php file, I got an error.

I deleted the comma, but now when running the php file I am always getting a blank page.

Would you know what happened ?

I hope I did not alter anything important in the data base.

I thank again for your help.

 

Patrick

 

 

 

 

Link to comment
Share on other sites

To thank you is a gray heart under the posts.

 

Check in the administration whether the status of orders has changed.

This mistake is being made by a person, so it is better to solve everything programmatically.
Of course, you can also do bug fixes if you're wrong.

 

But you probably don't even know the basics of PHP.
I just can't go back to the issue indefinitely.
Everyone can copy the script, but realize that I spend some time on it and during that time I could have made money elsewhere.

I end up in this thread.

Edited by Guest (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...