Pull records by Year dropdown list


#21

Ok I get it I’m lost! Just was trying to add one more report, by month and year.
The report by year works fine. It’s when I repeated everything to make the year report, I then add by month also. This way I can pull records by all months of the year (year) report. And also by month and year (month) and (year). I have learned a lot lately and have nearly finished this project, except the month year report. I will study more, and look more closely at your suggestions. I have cleaned up the code and made sure all letter case matches.
Thank you so much for giving of your very valuable time.


#22

Strangely this code below works, I’m sure something is a miss because of your expert comment " Also, stop doing this - $result = $query; nonsense.". Which I would trust over my experience, but this code below works. What will be a problem if I continue with this way?

<?php
    {      
        include 'db_connection2.php';        
        $query = " SELECT 
  g.name as `group`,
  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
month(date) = ? AND year(date) = ?
AND
ar.present = 1

GROUP BY g.name
ORDER BY ar.date, g.name ASC                ";
                
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["month"],$_POST["year"]] );
$result = $query;
     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered"> 
           
    <div align="center"><font size=4>
    Total present (by Class)-------PDO------ Not Working Code</font>
    </div>

                   <tr>  
                               <th style="text-align:center;" width=".001%"><font size=2><span>Class</span></th>
                              <th style="text-align:center;" width=".001%"><font size=2><span>Attended</span></th>
                </tr>  
                
                     ';
                   
                foreach($stmt as $row)
       
            {           
          
 $output .= '  
                     <tr>  
                 <td style="text-align:center;">' . $row['group'] . '</td>
                 <td style="text-align:center;">' . $row['attended'] . '</td>
                     </tr>  
       ';
            }
      
        $output .= '</table>';    }
        
   $pdo=null;
    // By this way you can close connection in PDO.    
 ?>

#23

I have no idea how that is working, and I sincerely doubt that it is. $stmt, which you are looping over, is a pdo object, not a result set.


#24

Ok, well it did indeed work, idk how? Still I have changed the code around. Works perfect!

 <?php

    {
        include 'db_connection2.php';        
       $stmt = $pdo->prepare( " SELECT        
  g.name as `group`,
  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
YEAR(date) = ?
AND
ar.present = 1

GROUP BY g.name
ORDER BY g.name ASC
                 ");

$stmt->execute([$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);


     //-----------------------------Table------------------------------------//
 

        $output .= '  
           <table class="table table-bordered"> 
           
    <div align="center"><font size=4>
    Total present (by Class)</font>
    </div>

                   <tr>  
                               <th style="text-align:center;" width=".001%"><font size=2><span>Class</span></th>
                              <th style="text-align:center;" width=".001%"><font size=2><span>Attendance</span></th>
                </tr>  
                
                     ';
                                     foreach($result as $row)
                   {
                            
 $output .= '  
                     <tr>  
                 <td style="text-align:center;">' . $row['group'] . '</td>
                 <td style="text-align:center;">' . $row['attended'] . '</td>
                     </tr>  
       ';
            }
      
        $output .= '</table>';    }
        
   $pdo=null;
    // By this way you can close connection in PDO.    
 ?>

#25

That is different than,

This.


#26

A PDOStatement object is Traversable and can be looped over with a foreach(){} loop. However, for the reasons already stated in this thread, is the equivalent of painting yourself into a corner, and should be avoided.


#27

I have now add another client-side report by class, year and month. But can’t get the code right to pull records.
Code I have used and works, But that leaves open allow a hacker to inject sql below.

<?php
    {
          require 'db_connection2.php';
        
        $stmt = $pdo->prepare( " SELECT * 
        
FROM
_person, attendance_record, _person_group, age_bracket

WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
_person_group.id = '".$_POST["class"]."'
AND
month(date) = ?
AND
year(date) = ?

ORDER BY attendance_record.date, age_bracket.id ASC 
                 ");
                 
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);



     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                <tr>  
                              
                              <th width="10%"><font size=2><span>FirstName</span></th>
                              <th width="15%"><font size=2><span>LastName</span></th>
                               <th width="10%"><font size=2><span>Age Class</span></th>
                              
                              <th width="25%"><font size=2><span>Class</span></th>
                              <th width="10%"><font size=2><span>Status</span></th>
                              <th width="15%"><font size=2><span>Date</span></th>
                              
                             
                </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                           
                           
                            
                          <td><font size=2>'. $row["first_name"] .'</td>  
                          <td><font size=2>'. $row["last_name"] .'</td> 
                          <td><font size=2>'. $row["label"] .'</td>
                          
                          <td><font size=2>'. $row["name"] .'</td>
                           <td><font size=2>'. $row["status"] .'</td>
                          <td><font size=2>'. $row["date"] .'</td>
                          
			              
                     </tr>  
                ';
            }
      
       $output .= '</table>'; 
        
        }
         echo $output;
         
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

Below is the code I have tried but wont pull records.

//filter_Attendance_class.php

<?php
    {
          require 'db_connection2.php';
        
        $stmt = $pdo->prepare( " SELECT * 
        
FROM
_person, attendance_record, _person_group, age_bracket

WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
class(_person_group.id) = ?
AND
month(date) = ?
AND
year(date) = ?

ORDER BY attendance_record.date, age_bracket.id ASC 
                 ");
                 
$stmt->execute([$_POST["month"],$_POST["year"],$_POST["class"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);



     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                <tr>  
                              
                              <th width="10%"><font size=2><span>FirstName</span></th>
                              <th width="15%"><font size=2><span>LastName</span></th>
                               <th width="10%"><font size=2><span>Age Class</span></th>
                              
                              <th width="25%"><font size=2><span>Class</span></th>
                              <th width="10%"><font size=2><span>Status</span></th>
                              <th width="15%"><font size=2><span>Date</span></th>
                              
                             
                </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                           
                           
                            
                          <td><font size=2>'. $row["first_name"] .'</td>  
                          <td><font size=2>'. $row["last_name"] .'</td> 
                          <td><font size=2>'. $row["label"] .'</td>
                          
                          <td><font size=2>'. $row["name"] .'</td>
                           <td><font size=2>'. $row["status"] .'</td>
                          <td><font size=2>'. $row["date"] .'</td>
                          
			              
                     </tr>  
                ';
            }
      
       $output .= '</table>'; 
        
        }
         echo $output;
         
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

I’m guessing it’s this line, is not correctly written.
class(_person_group.id) = ?


#28

What do you see different in those two queries?


#29

Also, the order of the ? place-holders in the sql query statement must match the order of the the values in the ->execute([…]) method call.


#30

Ok I thought I tried that and it didn’t work, so I must have had something backwards. Thank you both for all your time and help. Just when I thought I’m getting it, I’m lost again. But now it is working great, thank you again.

<?php

    {
          require 'db_connection2.php';
        
        $stmt = $pdo->prepare( " SELECT * 
        
FROM
_person, attendance_record, _person_group, age_bracket

WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
_person_group.id = ?
AND
month(date) = ?
AND
year(date) = ?

ORDER BY attendance_record.date, age_bracket.id ASC 
                 ");
                 
$stmt->execute([$_POST["class"],$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);


     //-----------------------------Table------------------------------------// 

        $output .= '  
           <table class="table table-bordered">  
                <tr>  
                              
                              <th width="10%"><font size=2><span>FirstName</span></th>
                              <th width="15%"><font size=2><span>LastName</span></th>
                               <th width="10%"><font size=2><span>Age Class</span></th>
                              
                              <th width="25%"><font size=2><span>Class</span></th>
                              <th width="10%"><font size=2><span>Status</span></th>
                              <th width="15%"><font size=2><span>Date</span></th>
                              
                             
                </tr>  
                
                     ';
                   
                foreach($result as $row)
       
            {
                $output .= '  
                     <tr>  
                           
                           
                            
                          <td><font size=2>'. $row["first_name"] .'</td>  
                          <td><font size=2>'. $row["last_name"] .'</td> 
                          <td><font size=2>'. $row["label"] .'</td>
                          
                          <td><font size=2>'. $row["name"] .'</td>
                           <td><font size=2>'. $row["status"] .'</td>
                          <td><font size=2>'. $row["date"] .'</td>
                          
			              
                     </tr>  
                ';
            }
      
       $output .= '</table>'; 
        
        }
         echo $output;
         
   $pdo=null;
    // By this way you can close connection in PDO.  
 ?>

#31

Named parameters don’t need to follow an order because there is a reference. Unnamed parameters need to follow the order they are referenced. Anything more than 5 placeholders, I start using named values.