4

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?

  • Your query as is, does not make any real sense and probably is not close to describe what you are trying to do. Most probably you should get repeating records with data of the first table. Probably you are after a MySQL Join or Union... Please describe what you are trying to achieve and the relationship between the records in those tables. – FFrewin Jun 15 '14 at 15:57
  • i updated my question @FFrewin – saibbyweb Jun 15 '14 at 17:58
5

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.

  • Exactly what i needed! worked like charm (on v3.3) THANKS ATON :) – saibbyweb Jun 16 '14 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.