Dave Dave - 2 months ago 13
PHP Question

Generate a daily transaction Register

Please, i need assistance on how to generate a register for a transaction which is entered into the database daily as a register, group by each car. See my code below:

Selection Criteria:

<tr>
<td>Start Date:<input type="date" name="d1" class="form-control" id="datepicker" /></td>
<td>End Date:<input type="date" name="d2" class="form-control" id="datepicker" /></td>
<?php $s = mysqli_query($connection,"SELECT * FROM tab_location");?>
<td>Location:
<select name="loc" class="form-control">
<option>Select Location...</option>
<?php while($lo = mysqli_fetch_array($s)) { ?>
<option value="<?php echo $lo['location_name'];?>"><?php echo $lo['location_name'];?></option>
<?php } ?>
</select>
</td>
<td>Action:<input type="submit" name="sbt" class="form-control btn btn-success" value="Generate Report" /></td>
</tr>


Table to Display the result:

<table width="70%" border="0" style="margin-top:15px;" align="left" class="table table-bordered">
<thead>
<tr>
<th>S/N</th>
<th nowrap="nowrap">FLT NO</th>
<?php $list = array();
$month = date('m');
$year = date('Y');
$dy = cal_days_in_month(CAL_GREGORIAN,date('n'),date('Y'));

for($d=1; $d<=$dy; $d++) {
$time = mktime(12, 0, 0, $month, $d, $year);
if (date('m', $time) == $month)
$list[]=date('Y-m-d', $time);
}

foreach ($list as $li){
echo "<th>".$li."</th>";
}?>
</tr>
</thead>
<tbody>
<?php if(isset($_POST['sbt'])){
$loc = $_POST['loc'];
$d1 = $_POST['d1'];
$d2 = $_POST['d2'];
$c = 0;
$st = mysqli_query($connection, "SELECT DISTINCT(fltno),created_at,id,status FROM tab_ddaily WHERE loc='$loc' AND CAST(created_at as date) BETWEEN '$d1' AND '$d2' ORDER BY fltno");
while($r = mysqli_fetch_array($st)){
$c++;?>
<tr>
<td><?php echo $c;?></td>
<td nowrap="nowrap"><?php echo $r['fltno'];?></td>
<td nowrap="nowrap"><?php echo $r['status']=='Available'?'<img src="includes/images/pass.jpe" width="20" height="10" />':'<img src="includes/images/wrong.jpe" width="20" height="10" />';?></td>
</tr>
<?php }
} else {
$loc = "";
$d1 = "";
$d2 = "";
}?>
</tbody>
</table>


The Above produces:enter image description here

I want it to produce:Distinct FLT No on a row with the status of each day spreading from the 1st day to the last day of the month. Kindly help on how to achieve this.
See structure and Data below:
[![enter image description here][2]][2]

Answer

I have modified the output array returned from database and then reiterate it to show FLT and status date-wise.

<tbody>
<?php if(isset($_POST['sbt'])){
    $loc = $_POST['loc'];
    $d1 = $_POST['d1'];  
    $d2 = $_POST['d2'];
    $c = 1;
    $st = mysqli_query($connection, "SELECT DISTINCT(fltno),created_at,id,status FROM tab_ddaily WHERE loc='$loc' AND CAST(created_at as date) BETWEEN '$d1' AND '$d2' ORDER BY fltno");

    $temp = array();
    while($r = mysqli_fetch_array($st)){
        $temp[ $r['fltno'] ][ $r['created_at'] ] = array('id' => $r['id'], 'status' => $r['status']);
    }

    foreach($temp as $key => $values){
        $dates = array_keys($values); ?>
        <tr>
            <td><?php echo $c;?></td>
            <td nowrap="nowrap"><?php echo $key;?></td>
            <?php foreach ($list as $li){
                if(in_array($li, $dates)){ ?>
                    <td nowrap="nowrap"><?php echo ($values[$li]['status'] == 'Available') ? '<img src="includes/images/pass.jpe" width="20" height="10" />':'<img src="includes/images/wrong.jpe" width="20" height="10" />';?></td>
                <?php } else{ ?>
                    <td nowrap="nowrap">n/a</td>
            <?php }
            } ?>
        </tr>
        <?php $c++;
    }
} else {
    $loc = "";
    $d1 = "";
    $d2 = "";
}?>
</tbody>

Outer loop is printing table row, SR_NO, FLT_NO and inner loop is printing status date wise.