SMT SMT - 11 days ago 6
MySQL Question

I am exporting data to CSV but I want to calculate something then export it

<?php


Database Connection

include('db.php');


Fetch Record from Database

if(isset($_POST['submit'])){
$first_day=$_POST['date_start'];
$last_day=$_POST['date_end'];
$output= "";
$table="users_new_test"; // Enter Your Table Name
$sql=mysqli_query($conn,"SELECT indx,date_start,id,name,tl_name,skills,grp,shift,breaktime,updatetime FROM $table WHERE date_start >= '$first_day' AND date_end <= '$last_day' ORDER BY date_start ASC");
$columns_total = mysqli_num_fields($sql);


Get The Field Name

for ($i = 0; $i < $columns_total; $i++) {
$heading = mysqli_field_name($sql, $i);
$output .= '"'.$heading.'",';
}
$output .="\n";

// Get Records from the table

while ($row = mysqli_fetch_array($sql)) {
for ($i = 0; $i < $columns_total; $i++) {
$output .='"'.$row["$i"].'",';
}
$output .="\n";
}


Download the file

$filename = date("d-m-Y").'.csv';
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
exit;
header("location:export_excel.php");
}
?>


I want to calculate the count of shift on particular date.
Like

2016-11-19
Shift = Morning (12)
Shift = Late Morning (12)
Shift = Evening (12)
Shift = Late Evening (12)
Shift = Night (12)

2016-11-20
Shift = Morning (14)
Shift = Late Morning (10)
Shift = Evening (15)
Shift = Late Evening (9)
Shift = Night (12)


How can I achieve this. Any Suggestions. Please have a look I am new in development.

Answer

@TPLMedia24 is right. Use MySQL instead of looping on every line of the result.

SELECT date_start, shift, COUNT(*) as total FROM FROM $table WHERE date_start >= '$first_day' AND date_end <= '$last_day' GROUP BY shift, date_start ORDER BY date_start ASC