Selecting data from multiple JDatabase tables?

Question

I am trying to select data from multiple Jdatabase tables and use a IF-ELSE statement with this :

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

$query = $db->getQuery(true);
$query->select($db->quoteName('username'))
      ->from($db->quoteName('#__table1', '#__table2', '#__table3', '#__table4'))
      ->where($db->quoteName('username') . ' = '. $db->quote($user->username));

$db->setQuery($query);
$result = $db->loadResult();

if($result) {
echo 'hey you are already there'; 
}
else {
echo 'sorry boss, you are out'; 
}

HOW IT SHOULD WORK? :

The above query should check whether the username of the current logged-in user is present in any of the four tables ('#__table1', '#__table2', '#__table3', '#__table4') and should accordingly follow the IF-ELSE STATEMENT

But...

It only selects records from the first table (#__table1) and it ignores all other tables!

I mean, if the username is already present in (#__table2), (#__table3),(#__table4) and not in the first table (#__table1) , it still jumps to ELSE STATEMENT which means it only checks for the username in the first table!?

Any guesses on how to make it select records from all the four tables?

Answer 1

To check for a value in multiple tables that do not have a relationship you are going to need either to make multiple queries, 1 for each table, or better make a UNION query. Now, Joomla has the UNION database methods, but those work only on Joomla 3.3 and later.

The concept of the query you are going to need for your case should be similar to this:

$query = "SELECT `username`
FROM `#__table1`
WHERE `username` = $user->username
UNION (
SELECT `username`
FROM `#__table2`
WHERE `username` = $user->username)
UNION (
SELECT `username`
FROM `#__table3`
WHERE `username` = $user->username)";

Make sure you will use the Joomla methods to add quotes to your query. Then check if there is result with the loadResult() Method.

For JOOMLA 3.3 and later (and possibly soon for the latest J2.5 version)
Now, if you are on Joomla 3.3, you can use the UNION methods like below:

$query = $db->getQuery(true);
$query2 = $db->getQuery(true);
$query3 = $db->getQuery(true);
$query4 = $db->getQuery(true);

$query->select($db->quoteName('username'))
      ->from($db->quoteName('#__table1'))
      ->where($db->quoteName('username') . ' = '. $db->quote($user->username));

$query2->select($db->quoteName('username'))
       ->from($db->quoteName('#__table2'))
       ->where($db->quoteName('username') . ' = '. $db->quote($user->username));

$query3->select($db->quoteName('username'))
       ->from($db->quoteName('#__table3'))
       ->where($db->quoteName('username') . ' = '. $db->quote($user->username));

$query4->select($db->quoteName('username'))
       ->from($db->quoteName('#__table4'))
       ->where($db->quoteName('username') . ' = '. $db->quote($user->username));

$query->union($query2)->union($query3)->union($query4); // Union method allows you to chain the union queries!!

$db->setQuery($query);
$result = $db->loadResult();

Hope this helps.
* It is possible that there might be some syntax/parse errors, as I wrote this on here. Let me know if you encounter any such problem so I can correct my answer.