how to display data based on this month only automatically?

Question

I have a lot of data in a table, if I want to see it without any filter then the table will process long and heavy. so I want when I open a page that contains this table it only opens the data for this month.

<div class="col-md-5">
<div class="input-group">
    <input type="date" class="form-control" name='fromDate' placeholder='From Date' autocomplete='off' value='<?php if(isset($_POST['fromDate'])) echo $_POST['fromDate']; ?>' required>
    <span class="input-group-text">S/D</span>
    <input type='date' class='form-control' name='endDate' placeholder='To Date' autocomplete='off' value='<?php if(isset($_POST['endDate'])) echo $_POST['endDate']; ?>' required>
</div>
</div>

above is the form to set the date range when I want to filter the data.

<tbody>
    <?php
        include_once('system/koneksi.php');
        $sql = "SELECT * FROM harianproses WHERE 1 ";
                            
        // Date filter
        if(isset($_POST['filter'])){
            $from       = $_POST['fromDate'];
            $fromDate   = date("d/m/Y", strtotime($from));
            $end        = $_POST['endDate'];
            $endDate    = date("d/m/Y", strtotime($end));
            $dept       = isset($_POST['departemen']) ? $_POST['departemen'] : [];
            $unit       = isset($_POST['unit']) ? $_POST['unit'] : [];
            $subunit        = isset($_POST['subunit']) ? $_POST['subunit'] : [];
                                        
            if(!empty($fromDate) && !empty($endDate)){
                $sql .= "AND date_hp between '".$fromDate."' and '".$endDate."' ";
            }
            if(!empty($dept)){
                $sql .= "AND dept_hp = '".$dept."'";
            }
            if(!empty($unit)){
                $sql .= "AND unit_hp = '".$unit."'";
            }
            if(!empty($subunit)){
                $sql .= "AND subunit_hp = '".$subunit."'";
            }
        }
                            
        $no = 1;
    
        $query = $connect->query($sql);
        while($row = $query->fetch_assoc()){
            echo 
            "<tr>
                <td>". $no++ ."</td>
                <td>".$row['date_hp']."</td>
                <td>".$row['dept_hp']."</td>
                <td>".$row['unit_hp']."</td>
                <td>".$row['subunit_hp']."</td>
                <td>".$row['shift_hp']."</td>
                <td>".$row['po_hp']."</td>
                <td>".$row['pono_hp']."</td>
                <td>".$row['ket_mat_hp']."</td>
                <td>".$row['type_mat_hp']."</td>
                <td>".$row['tout_hp']."</td>
                <td>".$row['lout_hp']."</td>
                <td>".$row['pout_hp']."</td>
                <td>".$row['pcsout_hp']."</td>
                <td>".$row['mlout_hp']."</td>
                <td>".$row['m2out_hp']."</td>
                <td>".$row['m3out_hp']."</td>
                <td>".$row['color_hp']."</td>
                <td>".$row['note_hp']."</td>
                <td>".$row['nopallet_hp']."</td>
                <td>".$row['quality_hp']."</td>
                <td>".$row['op_hp']."</td>
                <td>".$row['ht_hp']."</td>
                <td>".$row['hl_hp']."</td>
                <td>".$row['borong_hp']."</td>
                <td>".$row['pemborong_hp']."</td>
                <td>".$row['jenis_mat_hp']."</td>
                <td>".$row['supplier_hp']."</td>
                <td>".$row['nokirim_hp']."</td>
                <td>".$row['year_hp']."</td>
                <td>".$row['item_hp']."</td>
                <td>".$row['namakru_hp']."</td>
                <td>".$row['noprocess_hp']."</td>
                <td>".$row['nampan_hp']."</td>
                <td>".$row['ot_hp']."</td>
                <td>".$row['memo_hp']."</td>
                <td>".$row['ketproses_hp']."</td>
                <td>".$row['groupkualitas_hp']."</td>
                <td>".$row['speedpcs_hp']."</td>
                <td>".$row['speedml_hp']."</td>
                <td>".$row['inout_hp']."</td>
            </tr>";
        }
    ?>
</tbody>

how to have an auto filtered table show only data for the current month? like if today is august, it will display data from 01/08/2021 to 31/08/2021. if september it will change to 01/09/2021 to 30/09/2021.

not only the data changes, but the date filter also changes the date like this (date filter) :

Hello, @vivie and welcome to Stack Overflow. It is not clear what your question is. You have `fromDate` and `endDate` make it first and last days of today's month. Maybe it would be easier to help you if you follow this simple tutorial and created MRE: https://stackoverflow.com/help/minimal-reproducible-example — Felix, Aug 02 '21 at 02:49
if i use filter i have to set it first. but how to make it automatically set the start date of the month and the end of the month? — vivie, Aug 02 '21 at 02:55
you will have to do a little work *yourself*. PHP has function for today; from today you can take the month and construct first and last day — Felix, Aug 02 '21 at 03:01
I voted as Unclear (not the duplicate). If you need every day of the month to be represented in your output, then you should clarify your question. That said, I'm sure that that question is a duplicate here too. — mickmackusa, Aug 02 '21 at 04:09
i only want to display data from 01 to the end of the month. not every day changing date..... — vivie, Aug 02 '21 at 04:18
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 02 '21 at 09:55

score 0 · Accepted Answer · answered Aug 02 '21 at 03:52

0

If you want a standard query just to select all the rows in the current month there's no need to mess about with dates in PHP. MySQL will do the whole job for you:

SELECT * FROM harianproses 
WHERE
    (date(date_hp) 
    between 
      date_add(curdate(),interval -DAY(curdate())+1 DAY) 
    AND 
      LAST_DAY(CURDATE())
    )
    AND... -- Any other search terms
;

date(date_hp) - we take just the DATE portion of the date field
The first expression returns the date (only) of the first day of the current month
The last expression returns the date (only) of the last day of the current month.

It's important to ensure that we're working only with dates. If not, the time portion can result in unexpected effects. E.g. 2021-08-31 08:00:00 falls after the date returned by LAST_DAY('2021-08-03')

answered Aug 02 '21 at 03:52

Tangentially Perpendicular

5,012
4
11
29

thanks for your answer, but here I also want to set it as if the filter is automatically filled with the start date of the month (01/08/2021) and the end of the month (31/08/2021). – vivie Aug 02 '21 at 04:07
If this answer doesn't do exactly what you want, then why did you give it the green tick? – mickmackusa Aug 02 '21 at 04:07
that's what I want, but how do I get my date filter to also change to month start date and month end date? not dd/mm/yyyy like that anymore? do you know how? – vivie Aug 02 '21 at 04:16
@vivie I don't understand your question. This selects all the rows in the current month. There's no need to fiddle about with a date filter - this is already doing it. If you're using this as a basis you don't need a date filter in PHP. – Tangentially Perpendicular Aug 02 '21 at 04:39
@TangentiallyPerpendicular I have updated my question. yes that answers my question, but how do I get my date filter to also record that it's one month's data. – vivie Aug 02 '21 at 05:35

how to display data based on this month only automatically?

1 Answers1