How to get data from database based on particular month?

Question

I have 3 html select tag to search data from mysql database based on date. Using these 3 select tag user can select Year, Month and Zone Name (zone_id).

After select these 3 tag option value I am creating a variable to search data based on date:

$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);

This $search-date variable is printing user selected month as php strtotime(). eg : 1470866400

Now I have a table called client_pay_bill and there are a column name conn_date. This conn_date column value is store various month as php strtotime(). For eg : 1467324000 or 1470866400

Now I want to get value for specific month. For eg :

If user selected year and month is generate a August, 2016 timestamp then the query will be return only this August, 2016 data. How can I do this ?

Sql Query :

$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);

Update :

3 select tag are bellow :

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

Are you saving date in unix time stamp format in your client_pay_bill table ? — R.K.Saini

William William · Accepted Answer · 2016-08-25T11:07:23

You can make the MySQL handle it:

$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
";

Please try it and check if it's what you want.

Edit, try this:

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

How to get data from database based on particular month?

3 Answers