php - 如何从数据库中获取特定月份的数据？

Question

我有 3 个 html select 标签来根据日期从 mysql 数据库搜索数据。使用这 3 个选择标签，用户可以选择年、月和区域名称 (zone_id)。

选择这 3 个标签选项值后，我将创建一个变量来根据日期搜索数据:

$selected_year = isset($_POST['year']) ? (int) $_POST['year'] : date("Y");
$selected_month = isset($_POST['month']) ? (int) $_POST['month'] : date("n");
$selected_zone = isset($_POST['zone']) ? htmlspecialchars($_POST['zone']) : '';

$search_date = date('d'.'-'.$selected_month.'-'.$selected_year);
$search_date = strtotime($search_date);

此 $search-date 变量将用户选择的月份打印为 php strtotime()。例如:1470866400

现在我有一个名为 client_pay_bill 的表，并且有一个列名称 conn_date。此 conn_date 列值存储为 php strtotime() 的各个月份。例如:1467324000 或 1470866400

现在我想获取特定月份的值。例如:

如果用户选择的年份和月份生成 2016 年 8 月时间戳，则查询将仅返回 2016 年 8 月数据。我该如何做到这一点？

Sql 查询:

$get_client = "SELECT cbp.advance_amount, cbp.bill_month, cbp.due_amount, cbp.pay_amount, c.is_active, c.client_id, c.user_id, c.address, c.contact_no, zone.zone_name, package.package_name, c.monthly_bill, c.bill_date 
FROM clients AS c 
LEFT JOIN zone ON zone.zone_id = c.zone_id 
LEFT JOIN package ON package.package_id = c.package_id 
LEFT JOIN clients_pay_bill AS cbp ON cbp.client_id = c.client_id 
WHERE c.uid = '$uid' AND c.is_active = 1 AND c.conn_date <= '$search_date' ";

if($selected_zone != "all") {
    $get_client .= " AND c.zone_id != '' ";
} elseif($selected_zone == "all") {
    $get_client .= " AND c.zone_id != '' ";
} else {
    $get_client .= " AND c.zone_id = '$selected_zone' ";
}

$get_client .= "  ORDER BY c.client_id DESC ";
$get_client = mysqli_query($conn, $get_client);

更新:

3个选择标签如下:

<div class="col-md-3">
<div class="form-group">
    <select name="year" id="" class="form-control">
        <option value="">--Select Year-- </option>                        
        <?php
        for ($y=2013; $y<=2017; $y++) {
            if(isset($_POST['year']) && $_POST['year'] == $y) {
                $selected = 'selected = "selected"';
            } elseif( !isset($_POST['year']) && date('Y') == $y ) {
                $selected = 'selected = "selected"';
            } else {
                $selected = '';
            }
            echo "<option value='$y' $selected>$y</option>";
        }
        ?>                                        
    </select>           
</div>                                      
</div>
<div class="col-md-3">
<div class="form-group">
    <select name="month" id="" class="form-control">
        <option value="">--Select Month-- </option>                        
        <?php 
        $months = array('January', 'February','March', 'April', 'May', 'June', 'July ',   'August',  'September',  'October',  'November', 'December');

        $mcount = 1;
        foreach($months as $month) {
            if(isset($_POST['month']) && $_POST['month'] == $mcount) {
                $selected = 'selected = "selected" ';
            } elseif(!isset($_POST['month']) && date('n') == $mcount ) {
                $selected = 'selected = "selected" ';
            } else {
                $selected = '';
            }
            echo "<option value='$mcount' $selected>$month</option>";
            $mcount++;                                        
        }

        ?>                                        
    </select>                    
</div>
</div>
<div class="col-md-3">
<div class="form-group">
    <select name="zone" id="" class="form-control">                                                
    <?php
    $query_zone = mysqli_query($conn, "SELECT zone_id, zone_name FROM zone WHERE uid = '$uid' ORDER BY zone_id DESC");
    $query_zone_num = mysqli_num_rows($query_zone);
    if($query_zone_num == 0) {
        echo "<option value=''>No zone added</option>";
    } else {
        echo "<option value=''>Select Zone</option>";  
        echo '<option value="all" selected="selected">All Zone</option>';
    }
    while($result_zone = mysqli_fetch_array($query_zone)) {
        $zone_id = (int) $result_zone['zone_id'];
        $zone_name = htmlspecialchars($result_zone['zone_name']);

        if(isset($_POST['zone']) && $_POST['zone'] == $zone_id) {
            $selected = 'selected = "selected" ';
        } else {
            $selected = '';
        }
        echo "<option value='$zone_id' $selected>$zone_name</option>";
    }
    ?>
    </select>                    
</div>

Answer 1

你可以让 MySQL 处理它:

$get_client = "
  SELECT cbp.advance_amount, cbp.bill_month, cbp.due_amount, cbp.pay_amount, c.is_active, c.client_id, c.user_id, c.address, c.contact_no, zone.zone_name, package.package_name, c.monthly_bill, c.bill_date 
  FROM clients AS c 
  LEFT JOIN zone
    ON zone.zone_id = c.zone_id 
  LEFT JOIN package
    ON package.package_id = c.package_id 
  LEFT JOIN clients_pay_bill AS cbp
    ON cbp.client_id = c.client_id 
  WHERE c.uid = '$uid'
    AND c.is_active = 1
    AND MONTH(c.conn_date) = $selected_month
    AND YEAR(c.conn_date) = $selected_year
";

请尝试一下，看看是否是您想要的。

编辑，试试这个:

$get_client = "
  SELECT *
  FROM clients AS c
  WHERE MONTH(c.conn_date) = $selected_month
    AND YEAR(c.conn_date) = $selected_year
";