Run database MYSQL "update query" from Prestashop Cron task or SQL function in backend

[lateral]

Hi guys

 

I want to run a number of MYSQL update queries regularly against a number of tables in the database ideally from either the SQL interface in the backend and/or the Cron Task module.

 

 

Can any one help?

Regards

Greg

 

 

 

 

[electriz]

Hey,

 

- Create a .php file which will execute those queries

 

- Create new task and put link to this file in the "Cron tasks manager" module

Edited May 8, 2015 by electriz (see edit history)

[lateral]

Thanks for the reply.

 

I need help creating the PHP file that will contain the queries....

[electriz]

That should not be hard

 

For example, create a somefile.php in your shop root folder and make a call to Presta's Db class:

<?php
Db::getInstance()->update($table, 
$data, 
$where = '', 
$limit = 0, 
$null_values = false, 
$use_cache = true, 
$add_prefix = true)

or if you prefer raw SQL:

$sql = 'UPDATE '._DB_PREFIX_.'product SET something=1 WHERE id=1';
if (!Db::getInstance()->execute($sql))
    die('error!');

More informations: http://doc.prestashop.com/display/PS15/DB+class+best+practices

 

Then, you should be able to execute file from:

http://your_presta_store.com/somefile.php

And add that url in the crontab module.

Edited May 8, 2015 by electriz (see edit history)

[lateral]

Thanks mate!

 

I'll try this over the weekend and let you know how I go.

 

Regards

Greg

[lateral]

Hi again,

 

I tried the following without any luck:

 

$sql = 'UPDATE '._DB_PREFIX_.'product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = " "';
if (!Db::getInstance()->execute($sql))
    die('error!');

 

I want to create a "Reference" based upon the "id_product" number with a prefix of "RPR - ". I add 10000 to the "id_product" for alignment purposes.

 

I used the webservice to generate the "key" and ran it from the "http://your_presta_store.com/somefile.php?key=1234567890987654321

 

Is there a way to display what is happening so I can actually see what it is doing?

 

Thanks for your help.

 

Regards

Greg

[electriz]

You can add some kind of logs in specific parts of your PHP script to see what's going on (and save them to a file or database).

 

But before you add this link to crontab module, execute it directly from browser and check if there are any errors. 

 

And of course check your database, if desired rows are updated.

Edited May 9, 2015 by electriz (see edit history)

[lateral]

Hi

 

I have run it in the browser and there is nothing being displayed.

 

Does the query look correct?

 

Regards

Greg

Edited May 9, 2015 by lateral (see edit history)

[electriz]

If you don't display anything in this file, of course it would show nothing.

 

Check database if desired values are updated.

[lateral]

What do you mean when you say "If you don't display anything in this file, of course it would show nothing."

 

I have checked the database and it is NOT being updated...

[bellini13]

What do you mean when you say "If you don't display anything in this file, of course it would show nothing."

He just means that the only output you are going to see from this script is the "error!", but that will only occur when

1) the execute fails

2) you have debug mode enabled

 

What I would suggest doing is using phpmyadmin to test your SQL command and ensure it works properly.  Once you have a valid SQL command, then put that command into the script.  Doing that will give you this command

UPDATE ps_product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = "";

I think the issue with your original script is that the existing value for reference is not equal to "one blank space", which you have as reference = " "

Instead the reference is "empty", as in reference = "" (that is 2 double quotes without a space between them)

 

So then you transform that into your php code

$sql = 'UPDATE '._DB_PREFIX_.'product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = "";
if (!Db::getInstance()->execute($sql))
    die('error!');

Edited May 17, 2015 by bellini13 (see edit history)

[lateral]

Hi mate

 

Thanks for looking into this...I really appreciated it.

 

I did run my original query (with reference = " ") through the phpadmin backend to make sure it worked and it did:

 

UPDATE ps_product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = " ";

 

I then edited the "reference" field of a few records and deleted the data so that they were empty.

 

I then rang your version of the query with out the space between the double quotation marks and it also works:

 

UPDATE ps_product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = "";

 

 

I then created a php file in the root called "greg.php" that contains the following:

 

<?php

$sql = 'UPDATE '._DB_PREFIX_.'product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = "";
if (!Db::getInstance()->execute($sql))
    die('error!');

 

 

I then ran the greg.php script from the browser using the following:

 

 

mydomain.com.au/greg.php

 

 

It did not work (ie: it didn't change the reference field in the table) or display an error message or any message for that matter.

 

 

Do I need to have to use a security key because I am accessing the database????

 

 

Thanks again for your help

 

Regards

Greg

[bellini13]

Do I need to have to use a security key because I am accessing the database????

 

no

 

Enable debug mode for Prestashop and try again

[lateral]

Hi again,

 

I just enable debug mode by setting "define('_PS_MODE_DEV_', false);" to true and ran the front end without executing the php script to make sure that it was turned on and it is working.

 

I then run the script and nothing is displayed in the browser and the table is not updated.....

 

I created another simple script containing just the following and it works:

 

printf('Hello %s How are you feeling today?', $name);

 

I'm confused....

[bellini13]

missing a single quote at the end of the line

$sql = 'UPDATE '._DB_PREFIX_.'product SET reference = CONCAT ("RPR - ",id_product + 10000) where reference = ""';

 

[lateral]

Nope, still does not work...and no messages are being displayed in browser.....

[bellini13]

Not much more I can do for you here

[lateral]

Hi mate

 

Don't give up so easily!

 

I'm assuming this update should work?

 

Can you point me in the right direction?

 

Regards

Greg

[PCloud]

Hi Everyone,

 

This code does not seem to work. I am getting a Http 500 error.

 

<?php

 

$disableoutofstock = UPDATE `'._DB_PREFIX_.'ps_product_shop` SET `active`=0 WHERE `id_product` IN (SELECT `id_product` FROM `'._DB_PREFIX_.'ps_stock_available` WHERE `quantity`=0);

$enableinstock = UPDATE `'._DB_PREFIX_.'ps_product_shop` SET `active`=1 WHERE `id_product` IN (SELECT `id_product` FROM `'._DB_PREFIX_.'ps_stock_available` WHERE `quantity`>0);

if (!Db::getInstance()->execute($disableoutofstock))

    die('error!');

/*

if (!Db::getInstance()->execute($enableinstock))

    die('error!');

*/

?>

[bellini13]

you appear to have the prefix in your table names twice. 

UPDATE `'._DB_PREFIX_.'ps_product_shop`

_DB_PREFIX_ is typically ps_

 

and you already have ps_ in the table name

[ctapia]

También estoy haciendo una secuencia de comandos como esa, pero tampoco funciona, ¿será un problema de conexión con la base de datos? o el host debe apuntar a la dirección IP del servidor para conectarse?,  This is my script so far, is there something missing?

 

<?php

    if (!defined('_PS_VERSION_'))
        exit;
    function upgrade_order($object)
    {
        return Db::getInstance()->execute(
                'UPDATE `'._DB_PREFIX_.'ps_orders` SET `'._DB_PREFIX_.'ps_orders.current_state` =2 WHERE `'._DB_PREFIX_.'ps_orders.id_order` =1');
    }

Edited April 3, 2019 by ctapia (see edit history)