article

Wednesday, March 9, 2022

Vue.js PHP Mysql Filter records by Date range

Vue.js PHP Mysql Filter records by Date range

Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

vuejs
https://v2.vuejs.org/v2/guide/installation.html
CDN : https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js

Axios  
https://www.npmjs.com/package/axios
CDN : https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js

vuejs-datepicker
https://www.npmjs.com/package/vuejs-datepicker
https://unpkg.com/vuejs-datepicker

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `position` varchar(100) NOT NULL,
  `office` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `date_of_join` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `date_of_join`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '2020-01-09'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '2020-02-15'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '2020-03-01'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '2020-01-24'),
(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '2020-01-11'),
(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '2020-02-23'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '2020-03-04'),
(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '2020-04-28'),
(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '2020-01-12'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '2020-02-06'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '2020-03-21'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '2020-04-14'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '2020-01-29'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2020-02-22'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '2020-03-10'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '2020-04-26'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '2020-01-17'),
(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '2021-02-01'),
(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2021-03-19'),
(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '2022-03-09');

ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;


index.html
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
//index.html
<!doctype html>
<html>
    <head>
        <title>Vue.js PHP Mysql Filter records by Date range</title>
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
        <script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
        <script src="https://unpkg.com/vuejs-datepicker"></script>
        <style type="text/css">
        .inline{
            display: inline-block;
        }
        </style>
    </head>
    <body>
    <div class="container" id='myapp'>
        <div class="row">
            <div class="col-md-12 mt-5">
                <h1 class="text-center">Vue.js PHP Mysql Filter records by Date range</h1>
                <hr>
            </div>
        </div>
        <div class="row">
            <div class="col-md-12">
                <div class="row">
                    <div class="col-md-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <span class="input-group-text bg-info text-white" id="basic-addon1"><i class="fas fa-calendar-alt"></i></span>
                            </div>
                            <vuejs-datepicker wrapper-class="inline" placeholder="From date" format="dd/MM/yyyy" :clear-button="true" v-model='fromdate' @closed='checkDate();'></vuejs-datepicker>
                        </div>
                    </div>
                    <div class="col-md-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <span class="input-group-text bg-info text-white" id="basic-addon1"><i class="fas fa-calendar-alt"></i></span>
                            </div>
                            <vuejs-datepicker wrapper-class="inline" placeholder="To date" format="dd/MM/yyyy" :clear-button="true" v-model='todate' @closed='checkDate();' ></vuejs-datepicker>
                        </div>
                    </div>
                </div>
                <div>
                    <input type='button' class="btn btn-outline-info btn-sm" @click='fetchRecords()' value='Search'>
                    <button id="reset" class="btn btn-outline-warning btn-sm">Reset</button>
                </div>
                <div class="row mt-3">
                    <div class="col-md-12">
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered table-hover">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Name</th>
                                        <th>Position</th>
                                        <th>Office</th>
                                        <th>Salary</th>
                                        <th>Date</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr v-for='employee in employees'>
                                        <td>{{ employee.id }}</td>
                                        <td>{{ employee.name }}</td>
                                        <td>{{ employee.position }}</td>
                                        <td>{{ employee.office }}</td>
                                        <td>{{ employee.salary }}</td>
                                        <td>{{ employee.date_of_join }}</td>
                                    </tr>
 
                                    <tr v-show="recordNotFound">
                                        <td colspan='6'>No record found.</td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- Font Awesome -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.13.0/js/all.min.js"></script>
    <script>
            var app = new Vue({
                el: '#myapp',
                data: {
                    fromdate: "",
                    todate: "",
                    employees: "",
                    recordNotFound: true
                },
                methods: {
                    checkDate: function(){
 
                        if(this.fromdate != ''){
                            var fromdate = new Date(this.fromdate);
                            var todate = new Date(this.todate);
 
                            if(fromdate.getTime() > todate.getTime()){
                                var currentDate = new Date();
 
                                var day = fromdate.getDate();
                                var month = fromdate.getMonth();
                                var year = fromdate.getFullYear();
 
                                this.todate = new Date(year, month,  day);
                            }
                             
                        }
                         
                    },
                    fetchRecords: function(){
                         
                        if(this.fromdate !='' && this.todate != ''){
                             
                            axios.get('ajaxfile.php', {
                                params: {
                                    fromdate: this.fromdate,
                                    todate: this.todate
                                }
                            })
                            .then(function (response) {
                                console.log(response);
                                 
                                app.employees = response.data;
 
                                // Display no record found <tr> if record not found
                                if(app.employees.length == 0){
                                    app.recordNotFound = true;
                                }else{
                                    app.recordNotFound = false;
                                }
                            })
                            .catch(function (error) {
                                console.log(error);
                            });
                         
                        }
 
                    }
                },
                components: {
                    vuejsDatepicker
                }
            })
 
        </script>
    </body>
</html>
config.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
//config.php
 <?php
$host = "localhost"; 
$user = "root";      
$password = "";        
$dbname = "testingdb";  
 
// Create connection
$con = mysqli_connect($host, $user, $password,$dbname);
 
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}
ajaxfile.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//ajaxfile.php
<?php
include "config.php";
 
$condition  = "1";
if( (isset($_GET['fromdate']) && $_GET['fromdate'] != '' ) &&
    (isset($_GET['todate'])  && $_GET['todate'] != '' ) ){
    $condition  = " date_of_join between '".$_GET['fromdate']."' and '".$_GET['todate']."' ";
}
$userData = mysqli_query($con,"select * from employee WHERE ".$condition );
 
$response = array();
 
while($row = mysqli_fetch_assoc($userData)){
 
    $response[] = array("id"=>$row['id'],"name" => $row['name'],"date_of_join" => $row['date_of_join'],"position" => $row['position'],"office" => $row['office'],"salary" => $row['salary']);
 
}
 
echo json_encode($response);
exit;

Posted by ednalan at 12:47 AM . Under .

Related Post