Is there any settings for the obsolete cart delete?

[uvip]

I got a lot of deleted carts, some of them are registered customers generated and some are guests generated, how to automatically delete them when they are 7 days old?

 

Thanks

[uvip]

Any suggestions? Thanks

[vekia]

you can do it in several ways, each of them need some customization.

 

mysql trigger (how to)

run it several times/day with code like:

DELETE * from ps_cart where date_add < today-7days

Cron job (how to)

You can also do it with cron job with the same script, or with php code to remove cart object.

[bellini13]

do you want to delete carts that have been converted to orders?

[uvip]

do you want to delete carts that have been converted to orders?

Yes, I also want to delete carts that have been converted to orders, how to do it? And will it cause any issues?

 

Thanks

[bellini13]

Vekia has already addressed your question about how to do it.

[uvip]

Will this method delete the carts had already been transferred to orders? Since there is no delete button for the ones had already been converted to orders.

 

Thanks

 

Vekia has already addressed your question about how to do it.

 

 

you can do it in several ways, each of them need some customization.

 

mysql trigger (how to)

run it several times/day with code like:

DELETE * from ps_cart where date_add < today-7days

Cron job (how to)

You can also do it with cron job with the same script, or with php code to remove cart object.

[vekia]

Will this method delete the carts had already been transferred to orders? Since there is no delete button for the ones had already been converted to orders.

 

Thanks

 

yes, code will remove also carts that had already been transferred to orders

[uvip]

yes, code will remove also carts that had already been transferred to orders

 

Will there be any issues if we delete the carts had been converted to orders? Will we be still able to see the former order's contents after the delete of the cart?

 

Thanks a lot!

[vekia]

you will see order details because they are stored in ps_order_details

[uvip]

you will see order details because they are stored in ps_order_details

 

So this code:DELETE * from ps_cart where date_add < today-7days will delete carts older than 7 days right? I am not very proficient on the coding here, so an explanation will be highly appreciated. Thanks

[vekia]

ofcourse instead of  today-7days you have to use DATE format

[uvip]

ofcourse instead of  today-7days you have to use DATE format

 

Could you please be more specific about how to use the date format and what is the meaning of your code by writing today-7days ? For example, I want to delete carts older than 7 days, how should I put it? Sorry for my ignorance. Thanks a lot.

[vekia]

you said that you want to remove carts older than 7 days, so today - 7 days means: 

 

for example:

2013-10-29 14:42:44 - 7 days = 2013-10-22 14:42:44

2013-12-18 14:42:44 - 7 days = 2013-12-18 14:42:44

etc.

[bellini13]

Vekia, i believe he wants you to provide a valid SQL statement that he can use

[uvip]

you said that you want to remove carts older than 7 days, so today - 7 days means: 

 

for example:

2013-10-29 14:42:44 - 7 days = 2013-10-22 14:42:44

2013-12-18 14:42:44 - 7 days = 2013-12-18 14:42:44

etc.

 

Hi Vekia,

 

I am wondering if you can give me the direct sql code that I can use to insert into the cron job. I want to auto delete carts over 7 days.

 

Thanks a lot for your help there

[uvip]

you said that you want to remove carts older than 7 days, so today - 7 days means: 

 

for example:

2013-10-29 14:42:44 - 7 days = 2013-10-22 14:42:44

2013-12-18 14:42:44 - 7 days = 2013-12-18 14:42:44

etc.

 

Hi Vekia,

 

I am wondering if you can give me the direct sql code that I can use to insert into the cron job. I want to auto delete carts over 7 days.

 

Thanks a lot for your help there

[uvip]

Vekia, i believe he wants you to provide a valid SQL statement that he can use

 

Bingo! You got my meaning there:) Thanks

[uvip]

Hi Vekia,

 

I am wondering if you can give me the direct sql code that I can use to insert into the cron job. I want to auto delete carts over 7 days.

 

Thanks a lot for your help there

 

 

you said that you want to remove carts older than 7 days, so today - 7 days means: 

 

for example:

2013-10-29 14:42:44 - 7 days = 2013-10-22 14:42:44

2013-12-18 14:42:44 - 7 days = 2013-12-18 14:42:44

etc.

Hi Vekia,

 

Your response will be highly appreciated!

 

Thanks

[uvip]

Hi Vekia,

 

Your response will be highly appreciated!

 

Thanks

Anyone's suggestion will be highly appreciated !

 

Hi Vekia,

 

I am wondering if you read my message there?

 

Thanks

[vekia]

i just thought that i already posted it

DELETE from ps_cart where date_add < '2014-03-03 19:42:57'

[uvip]

 

i just thought that i already posted it

DELETE from ps_cart where date_add < '2014-03-03 19:42:57'

Hi Vekia,

 

I am not sure about the code is right? Since I want to execute this code as a cron job, how can I make sure the code will auto delete the card from the day to the former 7 days? You only put a specific date there:'2014-03-03 19:42:57' and I think a variable should be a proper one instead of a specific date?

 

Please correct me if I am wrong.

 

Thanks

[vekia]

DELETE from ps_cart where date_add < DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y-%m-%d %H:%i:%s');

date will be created automatically (-7 days) enjoy

[uvip]

DELETE from ps_cart where date_add < DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y-%m-%d %H:%i:%s');

date will be created automatically (-7 days) enjoy

 

 

Thanks for the information. I just figured out the cron job will control the date here, so I think if I set the cron job to run once a week, then I can just run a sript to delete the ps_cart content, there should be no need to define the date since the script will delete all of the content and the settings for once a week will control the time span. In this case, could you please let me know what the script would be? So it will be even simple and I think it will help many people who have the same request in the community.

 

Thanks a lot for your help there:)

[uvip]

Thanks for the information. I just figured out the cron job will control the date here, so I think if I set the cron job to run once a week, then I can just run a sript to delete the ps_cart content, there should be no need to define the date since the script will delete all of the content and the settings for once a week will control the time span. In this case, could you please let me know what the script would be? So it will be even simple and I think it will help many people who have the same request in the community.

 

Thanks a lot for your help there:)

 

It is my fault to not think through the issue here, this code should be the one: DELETE from ps_cart where date_add < DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y-%m-%d %H:%i:%s');

 

Anyone who want to delete the ps_cart, please use the script above. Thanks a lot for vekia's help!

[vekia]

you're welcome :-)

you used php script to run cron job, or just this query only?

[uvip]

you're welcome :-)

you used php script to run cron job, or just this query only?

 

I use your script:DELETE from ps_cart where date_add < DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y-%m-%d %H:%i:%s'); to run from the host cpanel end, they have a setting called cron job. Please correct me if I am doing the wrong way? Or you have better suggestions. Also, Vekia, did you test this cron job already from your end? I am using prestashop 1.4, is it safe to run this for the cron job with the setting of once a week? Will the script cause any issues or side effect? I just test to delete the ps_cart from the my sql and noticed the content still there for the orders, so anyone who will be deleting the ps_cart contents should be safe, but I am not sure with the running of this script will have any side effects, hope Vekia or anyone else can offer more test datas or experiences.

 

Thanks

[uvip]

you're welcome :-)

you used php script to run cron job, or just this query only?

 

Hi Vekia,

 

I ran this script as a cron job but the ps_cart table contents are still there? I believe all of the carts contents are older than 7 days. Please advise. Thanks

[vekia]

can you test this query in phpmyadmin ?

[uvip]

can you test this query in phpmyadmin ?

 

Hi Vekia,

 

I am wondering if you can help do a local test there since you are an expert on the code. 

 

Hope to see a perfect working script from you soon.

 

Thanks a lot for your help there.

[vekia]

im testing this code in phpmyadmin and everything works

[uvip]

im testing this code in phpmyadmin and everything works

It is weird that it does not work for the cron job as I executed in phpadmin and it also works. I am wondering if this script can be used for prestashop 1.3?

 

Thanks

[vekia]

yes script can be used in prestashop 1.3
i suppose that your cron job just doesnt support execution of mysql queries

[uvip]

yes script can be used in prestashop 1.3

i suppose that your cron job just doesnt support execution of mysql queries

If cron job just doesnt support execution of mysql queries, is there any other ways to run the cron job and achieve the same goal? Thanks

[vekia]

you can create php script with this sql query,

then execute this php script as a cron job

[uvip]

you can create php script with this sql query,

then execute this php script as a cron job

 

May I know how to create php script with this sql query? Thanks

[uvip]

you can create php script with this sql query,

then execute this php script as a cron job

I got this error from cron job:syntax error near unexpected token `('

[vekia]

it just means that you php script has got some errors.

make sure that you use correct php syntax.

[uvip]

it just means that you php script has got some errors.

make sure that you use correct php syntax.

 

I used the exactly script provided by you here: DELETE from ps_cart where date_add < DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y-%m-%d %H:%i:%s');

 

Could you please double check the code your provided for cron job? Thanks

[uvip]

it just means that you php script has got some errors.

make sure that you use correct php syntax.

Hi Vekia,

 

Could you please help me solve this issue there? Thanks a lot!