Joomla menu error using custom code mysql call procedure

Hi to all,

Im using a custom code for display data from 4 tables in my mysql db.
In custom code i can't use temporary tables because one query is one session so temporary tabels disapires after next select :<.

I find out to use a procedure in mysql. So i do that and its works but when i call it:
<?php
$options = array();
$db =& JFactory::getDBO();
$query="call daneB";
$db->setQuery($query);
$lista = $db->loadAssocList();
foreach ($lista as $dane)
{
$form->data['Dane'] = $lista;
}
?>

My menu in joomla disappear and i have an error :

Error loading module Commands out of sync; you can't run this command now SQL=SELECT m.id, m.title, m.module, m.position, m.content, m.showtitle, m.params, mm.menuid FROM kmpsp_modules AS m LEFT JOIN kmpsp_modules_menu AS mm ON mm.moduleid = m.id LEFT JOIN kmpsp_extensions AS e ON e.element = m.module AND e.client_id = m.client_id WHERE m.published = 1 AND e.enabled = 1 AND (m.publish_up = '0000-00-00 00:00:00' OR m.publish_up <= '2013-08-28 10:52:02') AND (m.publish_down = '0000-00-00 00:00:00' OR m.publish_down >= '2013-08-28 10:52:02') AND m.access IN (1,1,2,3,4,5,6,7,8,9,10,11,12,13) AND m.client_id = 0 AND (mm.menuid = 137 OR mm.menuid <= 0) ORDER BY m.position, m.ordering

What is hapend ??
Thanks for help.

Hi kivlow,

Sorry, I've never used a MySQL Procedure (one of these days I'll find the time to try).

The MySQL looks OK as far as I can tell (though the PHP has a problem in the foreach loop). You could try adding a ; to the end of the query in case that is getting included into something else.

The error message is not from a ChronoForms query but from what looks like a Joomla! menu module.

You could try turning site Debug on so that you can see all the queries being created, that might give you a clue.

Bob

Hi Bob,

Thanks for answer. The ';' in query is not a problem.
I find on google that the joomla 2.5 have a bug when custom code use mysql and stored procedur then the next query from joomla is out of sync.

I now use 4 conections and 4 query that create tables with select and in the end delete them. That works for me.

Thanks for help.
p.s. it is possible to send in custom code a multi query ?? in one session to DB (then i may use a temporaty tables).

Hi kivlow,

Well done for finding the bug report - I didn't know about that.

p.s. it is possible to send in custom code a multi query ?? in one session to DB (then i may use a temporary tables).

I'm not clear what you need here? It's certainly possible to use compound queries and to do multiple queries using a Custom Code action.

Bob

Hi kivlow,
I'm not clear what you need here? It's certainly possible to use compound queries and to do multiple queries using a Custom Code action.
Bob

Hi Bob
Simply one connection to DB four querys (3x create temporary tables with select and 1x select with inner join).
I test:
<?php
$db =& JFactory::getDBO();
$query="some select;";
$query.="some next select;";
$db->setQuery($query);
$lista = $db->loadAssocList();
?>
and that not work :<

Hi kivlow,

No, I'm pretty certain that the Joomla! DB Code doesn't support multiple queries like that. Neither am I sure what result you would get back if it did. You can run several queries one after the other though.

<?php
$db =& JFactory::getDBO();
$query="some select;";
$db->setQuery($query);
$lista = $db->loadAssocList();

$query ="some next select;";
$db->setQuery($query);
$listb = $db->loadAssocList();
?>

Bob

HI Bob

The $query is send to the DB in one session. Next $query will be next session so the temporary tables will disappear.
Then at the end i cant select data from this tables because they dont exists.

I mast use a chain of querys: drop tables x if exists, create tables x (select ...),drop tables y if exists, create tables y (select ...), select * from x inner join y where condition group by ... .

Thanks for Help :>
Kamil from Poland

Hi Kamil,

Ah, I hadn't understood that - I've never used Temporary tables. I found a StackOverFlow answer here that might help. I have used Views with ChronoConnectivity OK - and they will sustain across sessions; or you can use the PHP Array approach which is effectively a local copy of a temporary table.

Bob

Hi Bob

I dont thing about the views 😶. That solvs my problem :>

Thanks a lot.
p.s.
You may close this topic