Tom Tom - 4 months ago 31
PHP Question

joomla using multiple database queries in one function

In certain functions I may need to do a couple of queries like so:

$user = & JFactory::getUser();
$db = & JFactory::getDBO();
$query = $db->getQuery(true);

$query->select('id');
$query->from($db->quoteName('#__users'));
$query->where('username='.$db->quote($response->username));
$db->setQuery($query);
$user_id = $db->loadResult();

if ($user_id == "")
{
//do something
}


$query1 = $db->getQuery(true);
$query1->select('app_id');
$query1->from($db->quoteName('#__app_ids'));
$query1->where('app_descr='.$db->quote($this->app_descr).' AND app_valid=TRUE');
$db->setQuery($query1);
$app_id = $db->loadResult();


I find if I don't change
query
to
query1
I can't get this to work for the subsequent queries. Outside of Joomla I've never had to do this as I close the mysql connection use the same variable as long as it is in the right order, all is well.

Two questions:


  1. Is this right? Or is there a better way to do this?

  2. Do I need to check for mysql failure of
    loadResult
    ? How would I go about this. Looking at the Joomla core often I see nothing but sometimes there is a mix of things to handle this.


Answer

1) It should work with the same variable name, since you are getting a new query object since your method parameter is set to true. Try calling $query->clear(); just after getting query object

$query  = $db->getQuery(true);
$query->clear();
$query->select('app_id');

2) In Joomla 3 it should be something like

try
{
    $db->setQuery($query);
    $user_id = $db->loadResult();
}
catch (RuntimeException $e)
{
    $e->getMessage();
}

And in Joomla 2.5

if ($db->getErrorNum()) {
    JError::raiseWarning(500, $db->getErrorMsg());
} 

Also, change

$user = & JFactory::getUser();
$db = & JFactory::getDBO();

to

$user = JFactory::getUser();
$db = JFactory::getDBO();

Objects are returned by reference anyway in PHP 5, and it will throw a warning since php 5.3+