set and use MySQL variable in Joomla Query

For Joomla! 3.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
rjo
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Wed May 23, 2007 7:37 pm

set and use MySQL variable in Joomla Query

Post by rjo » Tue Sep 11, 2018 2:57 pm

Hi, I want to do some maths on the database server, assign the result to a variable and use that variable in a subsequent query, however, the second query doesn't seem to get the variable value.
This is what I have so far:

Code: Select all

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$db->setQuery('SET @MVAL = (SELECT MAX(id) FROM #__table)');
$db->query();
$query->select('id, firstname');
$query->from('#__table');
...
Removing the $db->query() in line 4 of the code doesn't help to execute the queries together. The SELECT does not get the value from the SET.
Is that possible or do I need to retrieve the first result in PHP and pass it on to a second independent query?
I suppose running two distinct queries is slightly slower.
Thanks,

Edit:
I have also tried the syntax:

Code: Select all

$query->set('@MVAL = (SELECT MAX(id) FROM #__table)');
Last edited by toivo on Tue Sep 11, 2018 3:00 pm, edited 1 time in total.
Reason: mod note: moved, not related to 3.x Performance

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 24870
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: set and use MySQL variable in Joomla Query

Post by Per Yngve Berg » Tue Sep 11, 2018 6:02 pm

Why not put it all in one query?

$query->select('id, firstname',MAX(id));

rjo
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Wed May 23, 2007 7:37 pm

Re: set and use MySQL variable in Joomla Query

Post by rjo » Tue Sep 11, 2018 6:23 pm

I was just about to edit my post to add the final purpose :-)
The idea is to replace a slow query that returns a random selection of records with a faster one.
MySQL ORDER RAND() is easy to use but slow.
As a replacement I would like to query the max(id), calculate a random id < max(id), then calculate an id range +- an offset and finally return records from that (random enough) range.
I would like to avoid repeating the same subqueries (max(id)) and I need to ensure the random value doesn't change within the same query. Thus the idea to use variables.


Post Reply

Return to “Joomla! 3.x Coding”