Jdatabase : Sorting two selections?

Question

I have Jdatabase table having attendance data of all my students.

EXAMPLE :

I need to display the attendance of all roll numbers for class 3. The records having 3 in attended_no column were present and records not having 3 in the attended_no column were absent.

This should be the output :

ABC-12-21 Present

ABC-12-24 Present

ABC-12-29 Absent

ABC-12-37 Absent

ABC-12-42 Present

Help would be appreciated.

Answer 1

I don't believe you need to use 2 queries. try the following:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('roll_no', 'attended_no'))
      ->from($db->qn('#__attendance'))
      ->order('roll_no ASC');
$db->setQuery($query);
$results = $db->loadObjectList();

echo '<ul>';
foreach ($results as $row)
{
    $array = explode(',', $row->attendance_no); // convert commas to array
    $flag  = in_array(3, $array);               //check if '3' in in the array

    if ($flag == null)
    {
        $flag='absent';
    }
    else
    {
        $flag='present';
    }

    echo '<li>' . $row->roll_no . ' : ' . $flag . '</li>';
}
echo '</ul>';

Answer 2

In a similar way, you have to compose a query to return the results that your require. Something like:

SELECT P.roll_num+A.roll_num FROM PRESENT P, ABSENT A 
WHERE 
P.subject_co = A.subject_co AND P.flag = 1 AND A.flag = 0
ORDER BY P.subject_co

In case, you are looking to return a UNION of your previous queries, it is even simpler:

Using the union methods in database queries https://docs.joomla.org/Using_the_union_methods_in_database_queries

Answer 3

You can spare yourself a lot of php code bloat by writing a refined sql SELECT clause with a conditional expression:

SELECT roll_num, IF(attended_no REGEXP '(^|,)3(,|$)', 'Present', 'Absent')

SQL Demo

The regular expression checks that the 3 value is never preceded or followed by another number -- so that 13 and 39 are never falsely matched. The regex pattern will correctly match if the 3 is the first value, the last value, or the only value in the comma separated string.

What does the regular expression mean? I'm glad you asked...

(    #start of capture group 1
^    #match the start position of the input string
|    #or
,    #match a comma
)    #end of capture group 1
3    #match the literal digit 3
(    #start of capture group 2
,    #match a comma
|    #or
$    #match the end position of the input string
)    #end of capture group 2

When you want to understand what a pattern is doing or you wish to test some fringe scenarios, feed it to regex101.com and upskill yourself.