php - 生成每日交易登记

Question

请问，我需要有关如何生成交易登记册的帮助，该登记册每天作为登记册输入数据库，按每辆车分组。请参阅下面的代码:

选择标准:

<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 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>

以上产生:

我希望它生成:Distinct FLT No，每天的状态从该月的第一天到最后一天。请帮助了解如何实现这一目标。参见下面的结构和数据:[![在此处输入图像描述][2]][2]

Answer 1

我修改了从数据库返回的输出数组，然后重申它以按日期显示 FLT 和状态。

<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>

外循环正在打印表行、SR_NO、FLT_NO，内循环正在打印状态日期。