3

I'm having a difficult time translating this SQL script to JDatabase methods because I can't find the AND query method.

SELECT user.firstname, user.lastname, leadership.leadname, leadership.lead_des
FROM CLUB,LEADERSHIP,USER
WHERE CLUB.CLUB_ID=11
AND LEADERSHIP.USER_ID=USER.USER_ID
AND CLUB.CLUB_ID=Leadership.CLUB_ID;

I was looking at the JDatabase documentation but don't see anything in there with AND.

How can I write my query using Joomla's query syntax?

6
$db = JFactory::getDbo();
$query = $db->getQuery(true)
  ->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
  ->from('CLUB,LEADERSHIP,USER')
  ->where('CLUB.CLUB_ID=11')
  ->where('LEADERSHIP.USER_ID=USER.USER_ID')
  ->where('CLUB.CLUB_ID=Leadership.CLUB_ID');

Multiple where clauses by default clubbed with AND. You can give the glue (AND or OR) explicitly as second option to the where function above however it is optional. Quote from Joomla API docs for JDatabaseQuery->where function:

string $glue - The glue by which to join the conditions. Defaults to AND. Note that the glue is set on first use and cannot be changed.

Or the alternative is you can manually combine in single where clause. You can use this method to mix OR and AND in the where clauses in a complex query.

$query = $db->getQuery(true)
  ->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
  ->from('CLUB,LEADERSHIP,USER')
  ->where('CLUB.CLUB_ID=11 AND LEADERSHIP.USER_ID=USER.USER_ID AND CLUB.CLUB_ID=Leadership.CLUB_ID');
  • That was extremely helpful thanks! The first part worked. Just another side noob question: How can I echo specific parts of that array? It outputs as Array ( [0] => stdClass Object ( [firstname] => Mary [lastname] => Bowman [leadname] => Advisor [lead_des] => President of the club ) ) – Sosa May 14 '15 at 19:13
  • echo $output['firstname']; – Nagarjun May 14 '15 at 19:15
  • I tried it, but it outputted nothing. It was able to output Mary with this that I wrote though. foreach ($result as $row) : echo "First name " . $row->firstname; endforeach; I wish there was a simpler way to write it though. – Sosa May 14 '15 at 19:23
  • My bad, yes, since you are loading object list, you should use $row->fieldname syntax. – Nagarjun May 14 '15 at 19:26
1

You can use the and clause like so:

$query->where('CLUB.CLUB_ID = USER.USER_ID', 'AND')
      ->where('LEADERSHIP.USER_ID = USER.USER_ID', 'AND')
      ->where('CLUB.CLUB_ID = Leadership.CLUB_ID');
0
$db = JFactory::getDbo();
$query = $db->getQuery(true);

// Select records from tables based on condition

$query->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des');
$query->from('CLUB, LEADERSHIP, USER');
$query->where('CLUB.CLUB_ID=11 AND LEADERSHIP.USER_ID=USER.USER_ID AND CLUB.CLUB_ID=Leadership.CLUB_ID');

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

// fetch result

foreach($result as $row) {
    echo "First name ".$row->firstname;
    echo "Last name ".$row->lastname;
}
0

Using commas as a means to JOIN tables is an older style syntax. More recently, MySQL has offered more keywords to better describe the joining intent (improving code readability).

Your commas represent INNER JOINs, and Joomla has a designated query method for them.

Below I will recommend a try {} catch{} block that will aid in the handling and debugging of your code. None of your tables, columns, or values require any backtick or single quote wrapping, so I will not call any $db->qn() or $db->q() methods. If your real query does have variables, or otherwise needs these considerations, my snippet is well-suited to receive these amendments.

$db = JFactory::getDbo();
try {
    $query = $db->getQuery(true)
                ->select(
                    array("a.firstname", "a.lastname", "b.leadname", "b.lead_des")
                  )
                ->from("club a")
                ->innerJoin("leadership b ON a.club_id = b.club_id")
                ->innerJoin("user c ON b.user_id = c.user_id")
                ->where('a.club_id = 11');
    //echo $query->dump();
    $db->setQuery($query);
    $resultset = $db->loadAssocList();
    if (!$resultset) {
        echo "No Rows In Resultset";
    } else {
        foreach ($resultset as $row) {
            echo "<div>";
                echo "<div>First Name: {$row['firstname']}</div>";
                echo "<div>Last Name: {$row['lastname']}</div>";
                echo "<div>Lead Name: {$row['leadname']}</div>";
                echo "<div>Lead Description: {$row['lead_des']}</div>";
            echo "</div>";
        }
    }
} catch (Exception $e) {
    echo "Syntax Error "; // . $e->getMessage();
}

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.