0

Hi, ive been working on a project which in web with php and mysql. My goal is simple user/admin can input two dates then the result of the query will exported in excel. here's my code which i manage to extract the query from database:

if(isset($_POST['submit'])){

        // $frdate = $_POST['fr-date'];

        $sel = "SELECT infotbl.barcodeid, tbltime.barcodeid, infotbl.fname, infotbl.lname, infotbl.jposition, tbltime.rectime, tbltime.recdate FROM infotbl INNER JOIN tbltime WHERE recdate BETWEEN :frdate AND :todate";
        $stmt = $DBcon->prepare($sel);
        $stmt->bindParam(':frdate',$_POST['fr-date']);
        $stmt->bindParam(':todate',$_POST['to-date']);

        $stmt->execute();

        // if($stmt->rowCount() == 0){
            ?>
            <div id="sub-table">
            <div class="rep-content">
            <table border="1"> 
            <tr>
            <th>Barcode</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Position</th>
            <th>Time</th>
            <th>Date</th>
            </tr>
            <?php

            while($row = $stmt->fetch()){

                ?>
                    <tr>
                    <td><?php echo $row['barcodeid']; ?></td>
                    <td><?php echo $row['fname']; ?></td>
                    <td><?php echo $row['lname']; ?></td>
                    <td><?php echo $row['jposition']; ?></td>
                    <td><?php echo $row['rectime']; ?></td>
                    <td><?php echo $row['recdate']; ?></td>
                    </tr>
                <?php
            }

            ?>
            </table>
            </div>
            </div>
            <?php
        // }

    }

But when i exported it to excel, all dates is been exported, not my date range. here's my export code.

<?php
  //2nd Attempt: Export to excel
  include 'dbcon.php';

// if(isset($_POST['export'])){

 $output = '';  

      $sql = "SELECT infotbl.barcodeid, tbltime.barcodeid, infotbl.fname, infotbl.lname,infotbl.jposition, 
             tbltime.rectime, tbltime.recdate FROM infotbl INNER JOIN tbltime ON infotbl.barcodeid = tbltime.barcodeid -- WHERE recdate = :frdate AND recdate = :todate";
      $stmt = $DBcon->prepare($sql);
      $stmt->bindParam(':frdate', $_POST['fr-date']);
      $stmt->bindParam(':todate',$_POST['to-date']);
      $stmt->execute();

      $output .= '  
                <table class="table" bordered="1">  
                     <tr>  
                          <th>BARCODE</th>  
                          <th>FIRST NAME</th>  
                          <th>LAST NAME</th> 
                          <th>POSITION</th>
                          <th>TIME</th>
                          <th>DATE</th>

                     </tr>  
           ';  
           while($row = $stmt->fetch())  
           {  
                $output .= '  
                     <tr>  
                          <td>'.$row["barcodeid"].'</td>  
                          <td>'.$row["fname"].'</td>  
                          <td>'.$row["lname"].'</td>  
                          <td>'.$row["jposition"].'</td>
                          <td>'.$row["rectime"].'</td>
                          <td>'.$row["recdate"].'</td>
                     </tr>  
                ';  
           }  
           $output .= '</table>';  
           header("Content-Type: application/xls");   
           header("Content-Disposition: attachment; filename = Attendance_Report-" . date('Y-m-d') .".xls");  
           echo $output;  

?>

Need help to figure this out.

Thanks.

Otep

3
Contributors
5
Replies
33
Views
1 Year
Discussion Span
Last Post by pritaeas
1

Are you saying the first one works but the second one doesn't?

The second will export all dates because you the comment delimiter inserted:
SELECT infotbl.barcodeid, tbltime.barcodeid, infotbl.fname, infotbl.lname,infotbl.jposition, tbltime.rectime, tbltime.recdate FROM infotbl INNER JOIN tbltime ON infotbl.barcodeid = tbltime.barcodeid -- WHERE recdate = :frdate AND recdate = :todate"`

The WHERE part of the command will be ignored because of the --

0

I ommitted the WHERE clause becuse it returns the Header only when execute it. the result of that date range query is what im trying to export in xls, as of now when i run the 2nd code it fetch the beginning and end date. instead of showing July 1 to july 15 only for example, it shows Dates May to July when i check the exported xls.

1

And May to July is all of the data contained in the database?
When you say it returned the header only are you referring to the first row in the file i.e. just the headings? It could be that you're parameters don't match the format in the date columns and therefore nothing is returned.
Can you echo out the select statement and paste it here?

0

Its a sample database. when you include the WHERE it export only the table header below will display

<table class="table" bordered="1">  
                     <tr>  
                          <th>BARCODE</th>  
                          <th>FIRST NAME</th>  
                          <th>LAST NAME</th> 
                          <th>POSITION</th>
                          <th>TIME</th>
                          <th>DATE</th>
                     </tr>  

my table columns were set to DATE not datetime. by the way this is a simple Time and Attendance using Barcode. On this part my goal is to show and export the query result range of two dates.

1

Why are you not using BETWEEN in the export query as in the first query? That is the issue you have.

Edited by pritaeas

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.