3

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

EXAMPLE :

table

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.

3 Answers 3

4

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>';
9
  • I had posted an example above. The actual table structure is more complex. I cannot select all the roll number at once. I can only select the present and absent records one by one. Now I need to join and sort them alphabetically.
    – saibbyweb
    Aug 19, 2016 at 12:50
  • I'm not sure how the table structure should be preventing you from selecting all roll numbers at once based on the $subject.
    – Lodder
    Aug 19, 2016 at 14:25
  • I updated my question. Check the edit, may be you can now get the idea of what the actual question was.
    – saibbyweb
    Aug 19, 2016 at 16:51
  • @Saibbyweb - I've updated the query in my answer. Please test again
    – Lodder
    Aug 19, 2016 at 17:19
  • Thanks, but can you edit it a bit in context of the table in the question (which has no flag column).
    – saibbyweb
    Aug 19, 2016 at 17:24
1

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

1
  • Can you explain this query please? Whats P. and A. ?
    – saibbyweb
    Aug 19, 2016 at 12:51
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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