Post by mani144125 » Sun Dec 05, 2021 7:46 pm

Hello everyone,
As we all know OpenCart sent the orders that weren't finalized or orders without a confirmed payment in"missing orders" with "order status id = 0".
Now in my store, I have a massive number of these orders that stacked up in the past few years and these unnecessary rows made my site's database a mess.

Now I want to delete these rows from flowing tables but except for a few related tables, others don't have order status id = 0 to use as reference, the only data that they all have is order id and I made this code for SQL to use for this purpose.

Code: Select all

DELETE FROM OC_Order WHERE order_id='number';
DELETE FROM OC_Order_history WHERE order_id='number';
DELETE FROM OC_Order_option WHERE order_id='number';
DELETE FROM OC_Order_product WHERE order_id='number';
DELETE FROM OC_Order_total WHERE order_id='number';
and a few other tables that they are from extensions.

Now I have two questions: do you guys have a better solution?
And Will this code work safely or I will run into a problem?

Thank everyone

New member

Posts

Joined
Tue Jul 30, 2019 2:40 pm

Post by straightlight » Mon Dec 06, 2021 11:17 pm

mani144125 wrote:
Sun Dec 05, 2021 7:46 pm
Hello everyone,
As we all know OpenCart sent the orders that weren't finalized or orders without a confirmed payment in"missing orders" with "order status id = 0".
Now in my store, I have a massive number of these orders that stacked up in the past few years and these unnecessary rows made my site's database a mess.

Now I want to delete these rows from flowing tables but except for a few related tables, others don't have order status id = 0 to use as reference, the only data that they all have is order id and I made this code for SQL to use for this purpose.

Code: Select all

DELETE FROM OC_Order WHERE order_id='number';
DELETE FROM OC_Order_history WHERE order_id='number';
DELETE FROM OC_Order_option WHERE order_id='number';
DELETE FROM OC_Order_product WHERE order_id='number';
DELETE FROM OC_Order_total WHERE order_id='number';
and a few other tables that they are from extensions.

Now I have two questions: do you guys have a better solution?
And Will this code work safely or I will run into a problem?

Thank everyone
OC version. While the purpose of deleting old orders locally is pretty much useless in this case since payment service providers still collects the data that you may decide to manually remove, you could always see this e.g: viewtopic.php?f=202&t=226017&p=831071#p831071 .

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by mani144125 » Wed Dec 08, 2021 8:45 pm

straightlight wrote:
Mon Dec 06, 2021 11:17 pm
mani144125 wrote:
Sun Dec 05, 2021 7:46 pm
Hello everyone,
As we all know OpenCart sent the orders that weren't finalized or orders without a confirmed payment in"missing orders" with "order status id = 0".
Now in my store, I have a massive number of these orders that stacked up in the past few years and these unnecessary rows made my site's database a mess.

Now I want to delete these rows from flowing tables but except for a few related tables, others don't have order status id = 0 to use as reference, the only data that they all have is order id and I made this code for SQL to use for this purpose.

Code: Select all

DELETE FROM OC_Order WHERE order_id='number';
DELETE FROM OC_Order_history WHERE order_id='number';
DELETE FROM OC_Order_option WHERE order_id='number';
DELETE FROM OC_Order_product WHERE order_id='number';
DELETE FROM OC_Order_total WHERE order_id='number';
and a few other tables that they are from extensions.

Now I have two questions: do you guys have a better solution?
And Will this code work safely or I will run into a problem?

Thank everyone
OC version. While the purpose of deleting old orders locally is pretty much useless in this case since payment service providers still collects the data that you may decide to manually remove, you could always see this e.g: viewtopic.php?f=202&t=226017&p=831071#p831071 .
Hi dear straightlight thanks for your reply, but I think you got it in the wrong way. These types of orders are the ones that customers didn't complete and they didn't pay anything for it it just created many useless rows and records in the database.
I will check your post.
OC version :3.0.2.0

New member

Posts

Joined
Tue Jul 30, 2019 2:40 pm

Post by straightlight » Wed Dec 08, 2021 11:05 pm

mani144125 wrote:
Wed Dec 08, 2021 8:45 pm
straightlight wrote:
Mon Dec 06, 2021 11:17 pm
mani144125 wrote:
Sun Dec 05, 2021 7:46 pm
Hello everyone,
As we all know OpenCart sent the orders that weren't finalized or orders without a confirmed payment in"missing orders" with "order status id = 0".
Now in my store, I have a massive number of these orders that stacked up in the past few years and these unnecessary rows made my site's database a mess.

Now I want to delete these rows from flowing tables but except for a few related tables, others don't have order status id = 0 to use as reference, the only data that they all have is order id and I made this code for SQL to use for this purpose.

Code: Select all

DELETE FROM OC_Order WHERE order_id='number';
DELETE FROM OC_Order_history WHERE order_id='number';
DELETE FROM OC_Order_option WHERE order_id='number';
DELETE FROM OC_Order_product WHERE order_id='number';
DELETE FROM OC_Order_total WHERE order_id='number';
and a few other tables that they are from extensions.

Now I have two questions: do you guys have a better solution?
And Will this code work safely or I will run into a problem?

Thank everyone
OC version. While the purpose of deleting old orders locally is pretty much useless in this case since payment service providers still collects the data that you may decide to manually remove, you could always see this e.g: viewtopic.php?f=202&t=226017&p=831071#p831071 .
Hi dear straightlight thanks for your reply, but I think you got it in the wrong way. These types of orders are the ones that customers didn't complete and they didn't pay anything for it it just created many useless rows and records in the database.
I will check your post.
OC version :3.0.2.0
It may not be as the wrong way as one believe since the order_status_id field is also inquired in the e.g post I provided. You just need to change the value to your order status ID value you'd be specifically calling for and, instead of updating the field from my e.g, you're simply looking for a way to delete those rows.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Who is online

Users browsing this forum: jp1077 and 75 guests