[Solved] Execute multiple queries in a PHP cron plugin

[georgie]

Hello

I would run many "INSERT" queries in a scheduled task, separated by a semi-colon.

I well recover my data, I well create queries (copying-pasting queries generated in DB = OK).

But PHP does not seem to want to execute them.

I tried 2 ways to create my sequence of queries, first this "classical" way (which work for me when I have just one query):

$query = "
BEGIN ;
INSERT INTO customer (civility, lastname, firstname, email)
VALUES ('$A_var', '$Another_Var', '$Another_Var', '$Another_Var') ;
INSERT INTO address (id_customer, country)
VALUES (LAST_INSERT_ID(), '$Another_Var') ;
INSERT INTO telephone (id_customer, id_address, code_tel1, tel1)
VALUES ( (SELECT MAX(id_customer) FROM customer) , (SELECT MAX(id_address) from address) , '$Another_Var', '$Another_Var') ;
INSERT INTO customer_organisme (id_customer, id_organisme)
VALUES ( (SELECT MAX(id_customer) FROM customer) , 'A_Value') ;
COMMIT ;
" ;
$db->setQuery($query);
$db->query();

It does not work.

After documenting, it seems this other method is recommended, for multiple queries:

$query  = "BEGIN ; ";
$query .= "INSERT INTO customer (civility, lastname, firstname, email)
VALUES ('$A_var', '$Another_Var', '$Another_Var', '$Another_Var') ; ";
$query .= "INSERT INTO address (id_customer, country)
VALUES (LAST_INSERT_ID(), '$Another_Var') ; ";
$query .= "INSERT INTO telephone (id_customer, id_address, code_tel1, tel1)
VALUES ( (SELECT MAX(id_customer) FROM customer) , (SELECT MAX(id_address) from address) , '$Another_Var', '$Another_Var') ; ";
$query .= "INSERT INTO customer_organisme (id_customer, id_organisme)
VALUES ( (SELECT MAX(id_customer) FROM customer) , 'A_Value') ; ";
$query .= "COMMIT ;";

OK the sequence generated is the same and works in DB (with a var_dump).
But I can not execute them in my PHP cron plugin. I tried many syntaxs, playing with this in various form:

$db->multi_query($query) ;
$db->query() ;

No way...

I have seen this PHP doc, I tried, but without success ("if...do...if...while... all with my "$db"):
http://php.net/manual/en/mysqli.multi-query.php

Please can you help me?

 

[troester]

I don't think you can have multiple queries in one statement with the Joomla layer
You have to split up.
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/
https://docs.joomla.org/Accessing_the_database_using_JDatabase
https://docs.joomla.org/Using_transactions_in_Joomla

 

[georgie]

Please note my sequence of queries is correct with a var_dump, now I think I just need to "send" it in DB, to execute.
Do you think I really need to split up?

 

[troester]

Var_dump is only showing the string you have composed, it can be anything.
It doesn't mean that it is some valid query.
And even if it's a valid combination to run directly in the DB you can't run it through Joomla's JDatabase.

 

[georgie]

OK thanks.

My problem is (was) I have (had) to insert in a remote DB, in tables linked, without InnoDB. So I think I can not split my sequence of queries (please what do you think about it?).

I am still working, but I think problem is solved : with Joomla/Fabrik/PHP cron, I do just one insert query in remote DB, in a special table created only for this. Then a trigger in this remote DB dispatchs in the required tables.

THX for infos:

#Fab