Am trying to export report .csv file in codeigniter, the records allocated in two tables in DB , the problem is when i tried to fetch data between two dates its give me blank file, but it works fine without between query!
another thing i need to display report on UI(view) before exporting it which doesn't work.

the controller:

public function Export_appointments() {
    //check if the user is admin
    $this->session->set_userdata('dest_url', site_url('backend/users'));
             if ( ! $this->has_privileges(PRIV_USERS))
            {
         return;
     }
    $this->load->dbutil();
    $this->load->helper('file');
    $this->load->helper('download');
    $this->load->view('backend/settings');
/* read input data*/
$date1 = date('Y-m-d',  strtotime($this->input->post("from_date"))); //date type
$date2 = date('Y-m-d', strtotime($this->input->post("to_date")));
/*working query*/
$this->db->select('first_name, last_name,     book_datetime,start_datetime,end_datetime');
$this->db->from('ea_appointments');
$this->db->join('ea_users', 'ea_users.id = ea_appointments.id');
 $this->db->where('ea_users.id_roles',3); 
/* I tried this ------book_datetime is datetime type---*/ 
          $this->db->where('CAST(book_datetime As Date) >= ',$date1);
          $this->db->where('CAST(book_datetime As Date) <= ',$date2);
          /* also this -------
$this->db->where('CAST(book_datetime as date) BETWEEN "'. date('Y-m-d',  strtotime($date1)). '" and "'. date('Y-m-d', strtotime($date2)).'"');*/ 
        $query = $this->db->get()->result_array();
        $fp = fopen('php://output', 'w');
        $header = array("First_Name","Last_Name","Booking_Date","Start_Date","End_Date"); 
    fputcsv($fp, $header);
    foreach ($query as $key => $value) {
    fputcsv($fp, $value);
    }

    $data = file_get_contents('php://output');
    $name = 'report.csv';

    // Build the headers to push out the file properly.
    header('Pragma: public');     // required
    header('Expires: 0');         // no cache
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Cache-Control: private',false);
    header('Content-Disposition: attachment; filename="'.basename($name).'"');  // Add the file name
    header('Content-Transfer-Encoding: binary');
    header('Connection: close');
    exit();
    force_download($name, $data);
    fclose($fp);
}

The view:

<?php $hidden = ($privileges[PRIV_SYSTEM_SETTINGS]['view'] == TRUE) ? '' : 'hidden'; ?>
<div id="export" >
<form   method= "post" action="<?php echo base_url();  ?>backend/Export_appointments">
<fieldset >
<legend>Export Appointment Data from:</legend>
<input type="date"  name="from_date">
<legend>To:</legend>
<input type="date"  name="to_date">
<button type="button" id="data-csv-export"  onclick="window.location='<?php echo site_url("backend/Export_appointments");?>'">Download report.CSV</button>
<div >
 <div >
  <table >
   <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Book Date</th>
    <th>Start Date</th>
    <th>End Date</th>
   </tr>
   <?php
  /* foreach($query as $row)
   {
    echo '
    <tr>
     <td>'.$row["first_name"].'</td>
     <td>'.$row["last_name"].'</td>
     <td>'.$row["book_datetime"].'</td>
     <td>'.$row["start_datetime"].'</td>
     <td>'.$row["end_datetime"].'</td>
     </tr> ';  }*/
     ?>
   </table>
   </div>
     </div>
       </fieldset>
          </form>
            </div>

CodePudding user response：

You can use your "controller name"/export_appointments instead of base_url() in the form url.

CodePudding user response：

You have used $_post instead of $_POST to reference to values posted in the form. Also, you have an exit() statement after your headers, which would be required for the output to prevent further execution, but it is not conditional, and will always exit so it never gets to the load view function. Suggestion before doing the headers and exit:

if (!empty($_POST)) {
    // Code that does things only when the form has been submitted. Headers and export features go here.
} else {
    // Do things when the form is NOT submitted. The load view will go here.
}

This should get you closer to the solution.

If your dates are already captured in Y-m-d format in your form/form plugin, you can skip the strtotime() calls on the dates - in that case, simply put the posted date values in the query. Please also sanitize your inputs before doing so - you're risking SQL injection as it stands.