Pull records by Year dropdown list

put the code under three backticks,

```
<div class="col-md-3">
           <input type="text" name="from_date" id="from_date" class="form-control" placeholder="From Date" />
            <input type="text" name="to_date" id="to_date" class="form-control" placeholder="To Date" />

 <select id="year">
  <option selected value="base">Please Select</option>
  <option value="2018">2018</option>
  <option value="2019">2019</option>
</select

<input class=“MyButton2” type=“button” name=“filter” id=“filter”;" value=“Filter” >

 <script>  
      $(document).ready(function(){  
           $.datepicker.setDefaults({  
                dateFormat: 'yy-mm-dd'   
           });  
           $(function(){  
                $("#from_date").datepicker();  
                $("#to_date").datepicker();  
           });  
           $('#filter').click(function(){
                var from_date = $('#from_date').val();  
                var to_date = $('#to_date').val();
                if(from_date != '' && to_date != '')
                {
                                   $.ajax({  
                           url:"https://website.com/dropdown-list/filter9_class.php",
                          method:"POST",  
                          data:{ 
               from_date:from_date, 
               to_date:to_date,  
               class: $('#class').val()
               },  
                          success:function(data)  
                          {  
                               $('#order_table').html(data);  
                          }  
                     });  
                }  
                else  
                {  
                     alert("Please Select Date");  
                }  
           });  
      });  
 </script>

filter9_class.php


<?php

    {
        include 'db_connection.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
date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
AND
ar.present = 1

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

 
        $result = mysqli_query($conn, $query);
       
        $conn->close();

        $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=".02%"><font size=2><span>Attended</span></th>
                             
                              
                </tr>  
                
                     ';

        if(mysqli_num_rows($result) >0)
        {
           
            
            while($row = mysqli_fetch_array($result))
            
            {
                
               
                $output .= '  
                     <tr>  
                           <td style="text-align:center;">' . $row['group'] . '</td>
                           <td style="text-align:center;">' . $row['attended'] . '</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>';
        
    }
    
 ?>
 
<?php

What code do I add and or change to use the year dropdown menu, instead of the to from date picker?

You would want to use YEAR(date) = the_submitted_year_value in the WHERE clause.

In case no one has already stated this, you should NOT put external/unknown values directly into the sql query statement. Doing so in a SELECT query will allow a hacker to inject sql that lets them read any/all values from any of your database tables, by injecting sql that produces a UNION query with your existing query.

You need to use prepared queries when supplying external/unknown data to the query and the easiest way of doing this is to switch to the much simpler php PDO extension.

1 Like

Worked perfect, thankyou phdr!

<?php
//filter9_class.php
    {
        include 'db_connection.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
YEAR(date) = '".$_POST["year"]."'
AND
ar.present = 1
                 ";             
                 
       $result = mysqli_query($conn, $query);
       
        $conn->close();

        $output .= '  
           <table class="table table-bordered">


[quote="phdr, post:7, topic:28011"]
You need to use prepared queries when supplying external/unknown data to the query and the easiest way of doing this is to switch to the much simpler php PDO extension.
[/quote]

This I'm lost on, never done prepared queries. Could you give me an example using my code so far?

Using the php PDO extension is extremely easy. First make a connection and set some commonly used attribute values -

<?php
// db connection - you should put the connection code in an external .php file and require it when needed
$DB_HOST = ''; // fill in these values with your connection credentials 
$DB_USER = '';
$DB_PASS = '';
$DB_NAME = '';
$DB_ENCODING = 'utf8'; // db character encoding - set to match your database tables

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc

Next, to convert any query to a prepared query, just do these steps -

  1. Replace each variable, single-quotes around the variable, any concatenation dots, and any {}, with a ? place-holder.
  2. Call the pdo prepare method. This returns a PDOStatement object.
  3. Call the PDOStatement execute method with an array consisting of the variable(s) that were removed in step #1.
  4. Fetch the data from the query using one of the PDOStatement fetch methods. Fetch() is used for a query that will match at most one row. FetchAll() is used for a query that will match a set of data. You can also use fetchAll() to get an array of values from a single column, rather than an array or rows. FetchColumn() is used when you only want a value from a single column, which is useful for things like COUNT() queries.

See the code at the end of this reply for an example - PHP Select from MYSQL with Variable

For your query/code, after you require the .php file that makes the connection, you would end up with this -

<?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
YEAR(date) = ?
AND
ar.present = 1
"; 

$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$query_data = $stmt->fetchAll();

For a query that doesn’t have any external/unknown data being put into it, instead of calling prepare()/execute(), you would just call the pdo query() method, which returns a PDOStatement object. You would then use any of the same PDOStatement fetch methods to retrieve the data into a php variable.

I cant get it to pull the records this way, No error just doesn’t pull records.

<?php
class View_Reports__Attendance_Report_Count_Year extends View

{

	function printView()
	{
		$this->_printParams();

		
	}

	function _printParams()
	{

require '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
YEAR(date) = ?
AND
ar.present = 1
"; 

$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$query_data = $stmt->fetchAll();

 ?>

  <div class=   @media screen and (max-device-width: 480px){
    body{...}
    div{
        clear:both!important;
        display:block!important;
        width:100%!important;
        float:none!important;
        margin:0!important;
        padding:0!important;
    }
}
<head>
<style>

 @media print {
  #noprint {
    display: none;
  }
}  
@media print {
  #filter {
    display: none;
  }
}  

@media print {
  #year {
    display: none;
  }
}  
@media print {
  #class {
    display: ;
  }
}  

@media print {
  #h10 {
    display: ;
     }
}  

input.MyButton {
width: 200px;
padding: 5px;
cursor: pointer;
font-weight: bold;
font-size: 85%;
background: grey;
color: Khaki;
border: 1px solid #3366cc;
border-radius: 0px;
}
input.MyButton:hover {
color: black;
background: #f0f8ff;
border: 1px solid 3366cc;
}

input.MyButton2 {
width: 50px;
padding: 5px;
cursor: pointer;
font-weight: bold;
font-size: 75%;
background: grey;
color: Khaki;
border: 1px solid #3366cc;
border-radius: 0px;
}
input.MyButton2:hover {
color: black;
background: #f0f8ff;
border: 1px solid 3366cc;
}

</style>
</head>
<div align="center">
  &nbsp;<br>
<input id="noprint" class="MyButton" type="button" value="Print Report" onclick="window.print()"/>
				
<input id="noprint" class="MyButton" type="button" value="Reset Page" onclick="window.location.href=window.location.href" />

<br/>
<?php
echo "Print Date " . date("Y/m/d") . "&nbsp;&nbsp;";
echo date("l");
?>

<div align="center"><font size=4>
Attendance Report Year</font>
   </div>

  <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  
<!------------------------seperate buttons from report------------------------->
                <br />

           <div class="col-md-3">
          
           
<!----------------------------------Year Dropdown----------------------------->.
       <body>
        <form>
                       <select name="year" id="year" class="form-control">
                <?php

                    $year = !empty( $_GET['year'] ) ? $_GET['year'] : 0;

                    for ($i = 0; $i <= 5; ++$i)  {
                        $time = strtotime(sprintf('-%d years', $i));
                        $value = date('Y', $time);
                        $label = date('Y ', $time);

                        $selected = ( $value==$year ) ? ' selected=true' : '';

                        printf('<option value="%s"%s>%s</option>', $value, $selected, $label);
                    }
                ?>
            </select>
            <input class="MyButton2" type="button" name="filter" id="filter";" value="Filter" >
        </form>
    </body>
</html>
           
  </div>
<div id="order_table">    
 
 <script>  
      $(document).ready(function(){  
           $.datepicker.setDefaults({  
                dateFormat: 'yy-mm-dd'   
           });  
           $(function(){  
                $("#year").datepicker();  
                  
           });  
           $('#filter').click(function(){
                
                var year = $('#year').val();
                if(year != 'Select Year')
                {
                                   $.ajax({  
                           url:"https://mywebstesite.com/dropdown-list/filter9_class.php",
                          method:"POST",  
                          data:{ 
               
               year:year,
               class: $('#class').val()
               },  
                          success:function(data)  
                          {  
                               $('#order_table').html(data);  
                          }  
                     });  
                }  
                else  
                {  
                     alert("Please Select Date");  
                }  
           });  
      });  
 </script>
		<?php
		
		}
}
?>

filter9_class.php

<?php
    {
        require '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
YEAR(date) = '".$_POST["year"]."'
AND
ar.present = 1

                 ";       

        $output .= '  
           <table class="table table-bordered"> 
                
                   <tr>  
                                                          
                              <th style="text-align:center;" width=".02%"><font size=2><span>Total Year Attendance</span></th>
                                                           
                </tr>  
                
                     ';

        if(mysqli_num_rows($result) >0)
        {
                       
            while($row = mysqli_fetch_array($result))
            
            {                
               
                $output .= '  
                     <tr>  
                <td style="text-align:center;">' . $row['attended'] . '</td>                         
                           
                     </tr>  
                ';
            }
        }
        else
        {
            $output .= '  
                <tr>  
                     <td colspan="5">No Report Found! Try another Year!</td>  
                </tr>  
           ';
        }
        $output .= '</table>';
         echo $output;
    }
    
 ?>

db_connection2.php

<?php
// db connection - you should put the connection code in an external .php file and require it when needed
$DB_HOST = 'localhost'; // fill in these values with your connection credentials
$DB_USER = 'user';
$DB_PASS = 'password';
$DB_NAME = 'dbname';
$DB_ENCODING = 'utf8'; // db character encoding - set to match your database tables

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // run real prepared queries
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc

The mysqli statements in the html document that operated on the data from the query must be change to instead operate on the php variable that the data from the query is fetched into by the PDO based code.

Ok I think I finally got it!
Please check out and give blessing.
filter_year.php

<?php
//filter_year.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
YEAR(date) = ?
AND
ar.present = 1
 ";
 
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$result = $query;

  $output .= '  
           <table class="table table-bordered"> 

                   <tr>  
                              <th style="text-align:center;" width=".001%"><font size=2><span>Total Year Attendance</span></th>
                </tr>  
                
                     ';
                     
                  
            foreach($stmt as $row);
            
            {
                     
  
        
 $output .= '  
                     <tr>  
                 <td style="text-align:center;">' . $row['attended'] . '</td>
                     </tr>  
       ';
            }
      
        $output .= '</table>'; 
        }
        
  $pdo=null;
    // By this way you can close connection in PDO.   
 ?>
 <!--------------------------------Total by class----------------------------->
 <?php
$date =  $_POST["year"];
echo '<span style="font-size: 30pt">' . $date . '</span>';
?>         
 
 
 <?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
YEAR(date) = ?
AND
ar.present = 1

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

$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$result = $query;

//-------------------------------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($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.    
 ?>
 
 <!------------------------------Total by Months----------------------------->
 
<?php

    {
        include 'db_connection2.php';

        
        $query = " SELECT 
  g.name as `group`,
  COUNT(ar.present) as attended,
  DATE_FORMAT(ar.date, '%Y-%m-%d') as `date`,
  FLOOR((DayOfYear(date)+5)/31)+5 as 'month_number',
   FLOOR((DayOfYear(date)+4)/28)+0 as 'month'
   

 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  month_number, g.name
ORDER by g.name, month_number
                 ";
                 
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$result = $query;
        
 //-------------------------------Table------------------------------------//    

        $output .= '  
           <table class="table table-bordered"> 
           
            <div align="center"><font size=4>
Total Present by (Months)</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>Month</span></th>
                              <th style="text-align:center;" width=".001%"><font size=2><span>Attendance</span></th>
                </tr>  
                
                     ';
                     
                  foreach($stmt as $row)
       
            {
                     
  
        
 $output .= '  
                     <tr>  
                 <td style="text-align:center;">' . $row['group'] . '</td>
                 <td style="text-align:center;">' . $row['month'] . '</td>
                 <td style="text-align:center;">' . $row['attended'] . '</td>
                     </tr>  
       ';
            }
      
        $output .= '</table>';    }
    
 
        echo $output;
     $pdo=null;
    // By this way you can close connection in PDO.  
     ?>

Code is not pulling any data? I’m still learning pdo.

<?php
//filter_month.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
YEAR(date) = ?
AND
Month(date) = ?
AND
ar.present = 1

GROUP BY g.name
ORDER BY ar.date, g.name ASC
                 ";
                 
$stmt = $pdo->prepare($query);
 
$stmt->bindParam(':month', $_POST['month'], PDO::PARAM_STR);
$stmt->bindParam(':year', $_POST['year'], PDO::PARAM_STR);
$stmt->execute();       

 $result = $query;


     //-----------------------------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>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.    
 ?>

You are binding named parameters, but using placeholders instead.

So, you can either add the named parameters to the query or remove the bindParam and change the execute to,

$stmt->execute([$_POST['month'], $_['year']]);

Now, your $result is just the query. If you actually want to results, you are going to need to fetch them as well.

Ok I just cant seem to get this to work. If I pick just year or month alone it pulls the data.
But when I try to combine month and year, it wont pull the data.Here is what I have tried.
I know it’s a simple thing I missing something in my thinking.

<?php
//filter_month.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'], $_['year']]);
$result = $stmt->fetch(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>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.    
 ?>

probably want those to be,

Month(ar.date) = ? AND Year(ar.date) = ?

I tried that, but this is coming from my $post month year selection on my report page.

There’s a typo on the $_POST variable name. It is $POST[‘year’], not $[‘year’].

If you had php’s error reporting setup to report and display all errors, you would be getting an undefined variable/index error to alert you to the problem.

LOL, this ridiculous forum software alters what is displayed. There are underscores in those values, which are there in the text but are being operated on by the forum software when displayed.

Ok- filter_year.php works by selecting a year from year dropdown list on report_year.php page. Code below, works perfect.

<?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
YEAR(date) = ?
AND
ar.present = 1
 ";
 
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["year"]]);
$result = $query;

  $output .= '  
           <table class="table table-bordered"> 

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

I also have a filter_month.php works by selecting a month and year from a month dropdown list and year dropdown list on report_month.php page. Code below, does not work!.

?php
//filter_month.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 = $stmt->fetch(PDO::FETCH_ASSOC);


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

        $output .= '  
           <table class="table table-bordered"> 
           
                       <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.    
 ?>

I’m not sure if the problem is due to this forum software altering the things you put in posts, using a device to enter code that’s upper-casing the first letter of things, or if you are sloppily typing/pasting together code, but you now have a letter-case difference in the $_POST … array index names, that would both be producing php errors and not matching any data in the query. Is your client-side code submitting variables with Year and Month, or year and month index names? The client-side and server-side code must be using the same letter-case.

Next, the PDO information I posted both stated and showed retrieving the data from the query into a php variable. While not stated (it takes books worth of information to learn everything involved, that you are not going to get in replies written in forum posts) there are three main reasons for doing so - 1) this allows you to easily debug problems since you can dump all the data from the query, 2) allows different (specialized) individuals to work on the different types of coding involved, and 3) makes the presentation code, that knows how to produce the output from the data, general purpose, so that you don’t need to keep changing it if the data source changes. If the original code was using this practice, you wouldn’t have needed to touch the presentation code when you changed from the mysqli extension to the PDO extension or if you ever need to change the database extension again, or if the data needs to come from a different source, such as an API that returns JSON encoded data.

Anyways, you now have a random mixture of both fetching data from the query into a php variable, then not using that variable, which will discard the fetched data, and looping over the PDOStatement object directly in the presentation code. Pick one method and stick to it throughout your application. Also, stop doing this - $result = $query; nonsense. Not only is that wasting your time typing unnecessary lines of code, but you aren’t using the $result variable at all, and since it is the sql query statement, there’s no point using it beyond where you prepared the query.

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.

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.    
 ?>

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.

Sponsor our Newsletter | Privacy Policy | Terms of Service