PHP function with JDatabase queries

Question

I have these two blocks of identical code in one of my PHP modules. The only difference between these two blocks is that in first block it is ... active = ' .$db->q('1')); and in the second block its ... active = ' .$db->q('0'));

Can I make a common PHP function say subjectlist() for both these blocks and then pass arguments 0 and 1 respectively to shorten the code.

As in, subjectlist(0); subjectlist(1);

First Block

<?php

// getting branch, semester and subject code of present classes of the teacher

$query1 = $db->getQuery(true);
$query1->select('sem,code')
->from('iust_teachers_classes')
->where('teacher = '. $db->q($user->username). 'and active = ' .$db->q('1'));

$db->setQuery($query1);
$sem_code = $db->loadObjectlist(); 

echo "<h3>List of present classes : </h3> <br>";

// Loop to populate the list of all present classes of the teacher

foreach ($sem_code as $rows) {

   $query2 = $db->getQuery(true);
   $query2->select('subject')
          ->from('iust_subjects')
          ->where('sem = '. $db->q($rows->sem). 'and code = ' .$db->q($rows->code));
   $db->setQuery($query2);
   $sub_list = $db->loadObjectlist();

    // echoing the active subject list

   foreach ($sub_list as $rows) {
        echo $rows->subject;
        echo "<br>";     
   }
}

?>

Second Block

<?php

// getting branch, semester and subject code of previous classes of the teacher

$query3 = $db->getQuery(true);
$query3->select('sem,code')
       ->from('iust_teachers_classes')
       ->where('teacher = '. $db->q($user->username). 'and active = ' .$db->q('0'));

$db->setQuery($query3);
$sem_code = $db->loadObjectlist();

echo "<h3>List of previous classes : </h3> <br>";

// Loop to populate the list of all previous classes of the teacher

foreach ($sem_code as $rows) {

    $query4 = $db->getQuery(true);
    $query4->select('subject')
           ->from('iust_subjects')
           ->where('sem = '. $db->q($rows->sem). 'and code = ' .$db->q($rows->code));
    $db->setQuery($query4);
    $sub_list = $db->loadObjectlist();

// echoing the previous subject list

    foreach ($sub_list as $rows) {
        echo $rows->subject;
        echo "<br>"; 
    }
}

?>

Help would be appreciated.

Answer 1

It looks like you could use a join in the query to use 1 query instead of 2 but I think you want something like the following.

function subjectlist($username,$active){

    $html = "";
    // getting branch, semester and subject code of present classes of the teacher
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('sem,code')
        ->from('iust_teachers_classes')
        ->where('teacher = '. $db->q($username). 'AND active = ' .$db->q($active));

    $db->setQuery($query);
    $sem_codes = $db->loadObjectlist(); 

    // Loop to populate the list of all present classes of the teacher
    foreach ($sem_codes as $sem_code) {
       $query = $db->getQuery(true);
       $query->select('subject')
              ->from('iust_subjects')
              ->where('sem = '. $db->q($sem_code->sem). 'AND code = ' .$db->q($sem_code->code));
       $db->setQuery($query);
       $sub_list = $db->loadObjectlist();

        // build the html to return
        $html .= "<h3>List of present classes : </h3> <br>";

        foreach ($sub_list as $sub_item) {
            $html .= $sub_item->subject . "<br>";
       }
       return $html;
    }
}

// best to pass the username to make the function more reusable
$username = JFactory::getUser()->username;
echo subjectlist($username,0); 
echo subjectlist($username,1);

Answer 2

I'd suggest putting this function in your helper.php file, assuming it belong to an extension. I'll just use a module as an example in this case:

helper.php:

class MyHelperFile
{
    public function subjectlist($active)
    {
        $db   = JFactory::getDbo();
        $user = JFactory::getUser();
        $html = '';

        $query = $db->getQuery(true);
        $query->select('sem, code')
              ->from($db->qn('iust_teachers_classes'))
              ->where($db->qn('teacher') . ' = ' . $db->q($user->username))
              ->where($db->qn('active') . ' = ' . (int)$active);
        $db->setQuery($query);
        $rows = $db->loadObjectlist(); 

        $html .= '<h3>List of present classes :</h3>';
        $html .= '<ul>';

        // Loop to populate the list of all present classes of the teacher
        foreach ($rows as $row)
        {
            $query = $db->getQuery(true);
            $query->clear();
            $query->select('subject')
                  ->from($db->qn('iust_subjects'))
                  ->where($db->qn('sem') . ' = ' . $db->q($row->sem))
                  ->where($db->qn('code') . ' = ' . $db->q($row->code));
            $db->setQuery($query);
            $rows = $db->loadObjectlist();

            // echoing the active subject list
            foreach ($rows as $row)
            {
                $html .= '<li>' . $row->subject . '</li>';
            }
        }
        $html .= '</ul>';

        return $html;
    }
}

mod_mymodule.php

require_once dirname(__FILE__) . '/helper.php';

$helper = new MyHelperFile();
$subjectlist_0 = $helper->subjectlist(0);
$subjectlist_1 = $helper->subjectlist(1);

As show in the code, I'd suggest calling the user object and getting the username inside the function rather than passing it through as a parameter.

I've also made a few code improvements.

You would of course though be better off using a join() clause in 1 query rather than running a 2nd one in a foreach loop.

Answer 3

Well, may be at your stage of development there is to late to look into something to build from, but your can look at the implementation of specific method of Model of JooYii Library.

There is method to generate query for table by passing values of columns as "conditions" array.

This class not only allows you to generate query, but along with other functions allows you to get list with pagination.