Cant pull data report by group.id only between dates?


#1

I thought I have all the codes correct to pull a report by between dates and by group.id. But the filter message No Order Found. How to get the filter_class.php select where by group.id = index_class.php dropdown selected numeric value. I’ve been searching and trying everything I’ve learned no success. Any help would be appreciated. If I put the group.id number in the filter_class.php select where by group.id = 4 manually, it will pull the report by that number. But it needs to pull the report based on the dropdown list on index_class.php.
I can pull the report just fine with the datepicker between dates, but I need it to pull by date and group.id number.
codes below

index_class.php

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

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

        <div class="col-md-3"> 
       <input type="button" name="filter" id="filter" value="Filter" class=".btn-mini btn-info" /> 
         </div>

index_class.php
Dropdown that pulls data from database _person_group table

    <div class="col-md-4"> 
    <script>

    $(document).ready(function() {
    //////////////////////
    $.getJSON("classes-data.php", function(return_data){
        $.each(return_data.data, function(key,value){
        $("#class").append("<option value=" + value.id +">"+value.name+"</option>");    

        });
    });
    //////////////////////
    ////////////

    $('#class').change(function(){
    //var st=$('#class option:selected').text();
    var st=$('#class').val();
    //alert(st);
    });
    /////////////////////
    });

    </script>
    <select id=class id=class>
    </select>
    </div>

classes-data.php

<?Php
require "config.php"; // Database Connection

$sql="select  id, name  from _person_group "; 
$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);

echo json_encode(array('data'=>$result));
?>

config.php

<?Php
///////// Database Details change here  ////
$dbhost_name = "localhost";
$database = "databasename";
$username = "root";
$password = "password";
//////// Do not Edit below /////////
try {
$dbo = new PDO('mysql:host=localhost;dbname='.$database, $username, $password);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage();
die();
}
?>

index_class.php
Datepicker code

 <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:"filter_class.php",
                          method:"POST",  
                          data:{from_date:from_date, to_date:to_date},  
                          success:function(data)  
                          {  
                               $('#order_table').html(data);  
                          }  
                     });  
                }  
                else  
                {  
                     alert("Please Select Date");  
                }  
           });  
      });  
 </script>

filter_class.php

     $query = "
                    SELECT * FROM attendance_record, _person,  _person_group
                   WHERE _person.id = attendance_record.personid AND _person_group.id = $('#class').val()
    and date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
    ORDER BY _person_group.id ASC

                     ";

            $result = mysqli_query($conn, $query);

by date and class picked
report


#2

First and foremost, prepared statements. I am a stickler for them because of security issues.

In your sql you have, $(’#class’).val(). That doesn’t exist.

So, how it happens is:

Php runs first. It renders the page after whatever processing and then sends it to the browser. JQuery (JavaScript) runs entirely in the browser. So you are trying to get a value from the browser, before the browser has anything.


#3

The group.id = $(’#class’).val() was a failed attempt to get it to pull.

If I put in there group.id =4, which is the id for young marrieds, it will pull the data.
I cant figure the code to put after group.id =., so it will pull the correct group from the dropdown selection.
Thank You


#4

You need to pass it in thru the ajax function that calls the API.


#5

Thank you for the response’s, maybe I’m confusing this. It works by date, just not by _person_group.id.
I need the report to pull all persons that where in a selected class and the between date.


#6

And the ajax call passes in the dates, but nothing else.


#7

Should be in the ajax.

Add this to the top of the php page that gets the request.

print_r($_POST);
exit();

And then in the ajax function make these modifications,

data:{ 
               from_date:from_date, 
               to_date:to_date,
               group: $('#class').val()
               },

#8

Ok we are getting closer, I did as you said. Now it pulls the report by date and by _person_group.id, but if I change the class dropdown selection to another class it still pulls the report for that same person in that class. I only have 1 person in 1 class currently to test.
And that person (Bill Hughes) is only in young marrieds class and only attended on 9/2/2018.

Working correct
youngmarriedsclassreport

So close we are!!!


#9

Not Working correct
allclassshows
should say > No Order Found, because Bill Hughes is not in this class.

Sorry had to reply twice, because I’m new, and it will only let me post 1 image per post.


#10

tables below, hope this helps

alltables


#11

Are the values changing for the group id first of all?


#12

Yes. I can change the selection and it changes the class. But it shows the only person (Bill Hughes) is in every class.


#13

You need to revisit your query. You need an inner join


#14

Ok I revisited my query and found I needed to add
WHERE attendance_record.groupid = _person_group.id
All working as desired, thank you astonecipher for all your help.

changed

 $query = "
                SELECT * FROM attendance_record, _person,  _person_group
               WHERE _person.id = attendance_record.personid
               AND _person_group.id = '".$_POST["class"]."'
AND date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
ORDER BY _person_group.id ASC

to

 $query = "
                SELECT * FROM attendance_record, _person,  _person_group
               WHERE attendance_record.groupid = _person_group.id
              AND _person_group.id = '".$_POST["class"]."'
AND date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."' 
ORDER BY _person_group.id ASC