Help with seperating date rows

[willymec]

I have an attendance program that I’m trying to pull a report based on attendance each week by the month. There are multiple classes. Class is same as person_group.
I need to pull the report like below.

     Class                     Date           Attended     

Experienced Adults ……… 2018-12-02 ………. 5
Experienced Adults ……… 2018-12-09 ….…… 5
Sr. Adults …………….……. 2018-12-02 ………. 22
Sr. Adults ……………. …… 2018-12-09 …….… 25

What I have so far.

Filter code below

<?php
//filter7_class.php
    {
        include 'db_connection.php';
        
        $query = " SELECT *
       
       

        FROM _person, attendance_record, _person_group
        WHERE _person.id = attendance_record.personid
        AND _person_group.id = attendance_record.groupid
        AND attendance_record.present = 1
        AND _person_group.id = 2
        AND date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
      
      
                 ";
while($row = mysqli_fetch_array($result))
{
 
  $counter++; //increment counter by 1 on every pass 
  }  
$result = mysqli_query($conn, $query);
        $num_rows = mysqli_num_rows($result);
        
 echo "Present $num_rows";

        $output .= '  
           <table class="table table-bordered">  
           
                <tr>  
                             
                              
                              <th width="30%"><span>Class</span></th>
                              <th width="20%"><span>Date</span></th>
                              <th width="2%"><span>RunCnt</span></th>
                             
                </tr>  
                
                     ';

        if(mysqli_num_rows($result) >0)
        {
            $count++;
            
            while($row = mysqli_fetch_array($result))
            
            {
                
               
                $output .= '  
                     <tr>  
                           
                          <td>'. $row["name"] .'</td>  
                          <td>'. $row["date"] .'</td>
                          <td>'. $count++.$row["count"] .'</td>
			              
                     </tr>  
                ';
            }
        }
        else
        {
            $output .= '  
                <tr>  
                     <td colspan="5">No Report Found or No Class Selected (Check your Dates and Select a Class and try again)</td>  
                </tr>  
           ';
        }
        $output .= '</table>';
        echo $output;
    }
 ?>

[astonecipher]

It should be done in the query, but what is your scheme?

http://www.sqlfiddle.com/#!9/40dabf/25

[willymec]

Ok thank you for the reply.
I’m not sure how to answer the question what is my scheme?
But, I’m was able to add what you gave me and I’m trying to wrap my head around the process. My tables are similar but different enough I cant seem to make it work. Since I’m new I can only post 1 pic.

$query = " SELECT 

g.name as group,
DATE_FORMAT(ar.date, ‘%Y-%m-%d’) as date,
COUNT(ar.personid) as attendee

FROM
attendance_record ar, _person_group, _person

INNER JOIN
_person p
ON ar.personid = p.id
ON ar.groupid = g.id

RIGHT JOIN

_person_group g
ON p.id = g.id

WHERE
date BETWEEN ‘".$_POST[“from_date”]."’ AND ‘".$_POST[“to_date”]."’
GROUP BY ar.date, g.name
ORDER BY ar.date ASC

             ";Blockquote

Tables below
<------------------
_person_group
[id] [name]
--------------------/>
<---------------------
attendance_record

----------------------------------->

<--------------------------------------
_person
[id] [first_name] [last_name]

----------------------------------------->

I hope this helps.

[willymec]

Ok I finally got it. Thank you astonecipher for the example you gave me on sqlfiddle.com.
It pointed me in the right direction.

$query = " SELECT 
  g.name as `group`,
  DATE_FORMAT(ar.date, '%Y-%m-%d') as `date`,
  COUNT(ar.present) as attended

 FROM 
   attendance_record ar
   
   INNER JOIN
   _person p
   ON ar.personid = p.id
   
   INNER JOIN
   _person_group g 
   ON ar.groupid = g.id
   
   
-- WHERE

AND
date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
AND
ar.present = 1
 
GROUP BY g.name, ar.date  
ORDER BY g.name, ar.date ASC

[astonecipher]

Now you just need to clean up the security holes. First would be to utilize prepared statements. That means that someone can’t pass malicious data in to mess with the system.

[willymec]

Ok will work on that next, I do have the program with login & password.

[willymec]

How do I now Sum the Attended column for each Class column

Experienced Adults    35
Sr, Adults                    08
Young Marrieds          28

[phdr]

You would just use - GROUP BY g.name

Note: since GROUP BY orders the rows so that it can consolidate them into the groups, an ORDER BY that’s the same as the GROUP BY is redundant and is not needed.

[willymec]

That’s it, Thank you phdr! I must of tried everything but the obvious. Crazy how that happens.