How to Filter Between Two Dates using jQuery with PHP/MySQLi

nofe · Wednesday at 8:41 PM

Getting Started

First, we need the jQuery library and also Bootstrap for a better design to our app. I've included these files in the downloadable of this tutorial but if you want, you can download them yourself using the links below:

For Bootstrap
For jQuery

Take note that we are gonna be using HTML5's input date.

Creating our Database

Next, we create the database that we are going to filter in this tutorial.

I've included a SQL file in the downloadable of this tutorial. All you have to do is import the said file. If you have no idea on how to import, please visit my tutorial How import .sql file to restore MySQL database.

Displaying our Table

Next, we are going to display table where we display our data. Create a new file, name it as index.html and paste the codes below.

<!DOCTYPE html>
<html

>
<head

>
<meta

charset

=

"utf-8"

>
<title

>

How to Filter Between Two Dates using jQuery with PHP/MySQLi</

title

>
<link

rel

=

"stylesheet"

type

=

"text/css"

href

=

"bootstrap/css/bootstrap.min.css"

>
</

head

>
<body

>
<div

class

=

"container"

>
<h1

class

=

"page-header text-center"

>

Filter Between Two Dates using jQuery</

h1

>
<div

class

=

"row"

>
<div

class

=

"col-sm-8 col-sm-offset-2"

>
<form

class

=

"form-inline"

>
<input

type

=

"date"

class

=

"form-control"

id

=

"date_start"

required>
<input

type

=

"date"

class

=

"form-control"

id

=

"date_end"

required>
<button

type

=

"button"

id

=

"filter"

class

=

"btn btn-primary"

>

Filter</

button

>
<button

type

=

"button"

id

=

"return"

class

=

"btn btn-primary"

>

Return</

button

>
</

form

>
<br

>
<table

class

=

"table table-bordered table-striped"

>
<thead

>
<th

>

ID</

th

>
<th

>

Firstname</

th

>
<th

>

Lastname</

th

>
<th

>

Address</

th

>
<th

>

Gender</

th

>
<th

>

Birthday</

th

>
</

thead

>
<tbody

id

=

"tbody"

>
</

tbody

>
</

table

>
</

div

>
</

div

>
</

div

>
<script

src

=

"jquery.min.js"

></

script

>
<script

src

=

"bootstrap/js/bootstrap.min.js"

></

script

>
<script

src

=

"app.js"

></

script

>
</

body

>
</

html

>

Creating our jQuery Scripts

Next, we create our jquery scripts for all our jquery functions and request. Create a new file, name it as app.js and paste the codes below.

$(

document)

.ready

(

function

(

)

{
fetch(

)

;
//clicking the filter button
$(

'#filter'

)

.click

(

function

(

e)

{
var

date_start =

$(

'#date_start'

)

.val

(

)

;
var

date_end =

$(

'#date_end'

)

.val

(

)

;
$.ajax

(

{
method:

'POST'

,
url:

'filter.php'

,
data:

{
date_start:

date_start,
date_end:

date_end,
}

,
dataType:

'json'

,
success:

function

(

response)

{
if

(

response.error

)

{
$(

'#tbody'

)

.html

(

'<tr><td colspan="6" align="center">No data matches your filter</td></tr>'

)

;
}
else

{
$(

'#tbody'

)

.html

(

response.data

)

;
}
}
}

)

;
}

)

;
$(

'#return'

)

.click

(

function

(

)

{
$(

'#date_start'

)

.val

(

''

)

;
$(

'#date_end'

)

.val

(

''

)

;
fetch(

)

;
}

)

;
}

)

;
function

fetch(

)

{
$.ajax

(

{
method:

'POST'

,
url:

'fetch.php'

,
dataType:

'json'

,
success:

function

(

response)

{
$(

'#tbody'

)

.html

(

response)

;
}
}

)

;
}

Creating our Fetch Table Data Script

Next, we create our script that will fetch data from our MySQL Database as our initial table data. Create a new file, name it as fetch.php.

<?php
//connection
$conn

=

new

mysqli(

'localhost'

,

'root'

,

''

,

'mydatabase'

)

;
$output

=

''

;
$sql

=

"SELECT * FROM members"

;
$query

=

$conn

->

query

(

$sql

)

;
while

(

$row

=

$query

->

fetch_assoc

(

)

)

{
$output

.=

"
<tr>
<td>"

.

$row

[

'id'

]

.

"</td>
<td>"

.

$row

[

'firstname'

]

.

"</td>
<td>"

.

$row

[

'lastname'

]

.

"</td>
<td>"

.

$row

[

'address'

]

.

"</td>
<td>"

.

$row

[

'gender'

]

.

"</td>
<td>"

.

date

(

'M d, Y'

,

strtotime

(

$row

[

'birthday'

]

)

)

.

"</td>
</tr>
"

;
}
echo

json_encode

(

$output

)

;
?>

Creating our Filter Script

Lastly, we create our script that will filter data on our table depending on the dates we enter. We name this file as filter.php.

<?php
//connection
$conn

=

new

mysqli(

'localhost'

,

'root'

,

''

,

'mydatabase'

)

;
$start

=

date

(

'Y-m-d'

,

strtotime

(

$_POST

[

'date_start'

]

)

)

;
$end

=

date

(

'Y-m-d'

,

strtotime

(

$_POST

[

'date_end'

]

)

)

;
$output

=

array

(

'error'

=>

false

,

'data'

=>

''

)

;
$sql

=

"SELECT * FROM members WHERE birthday BETWEEN '$start

' AND '$end

'"

;
$query

=

$conn

->

query

(

$sql

)

;
if

(

$query

->

num_rows

>

0

)

{
while

(

$row

=

$query

->

fetch_assoc

(

)

)

{
$output

[

'data'

]

.=

"
<tr>
<td>"

.

$row

[

'id'

]

.

"</td>
<td>"

.

$row

[

'firstname'

]

.

"</td>
<td>"

.

$row

[

'lastname'

]

.

"</td>
<td>"

.

$row

[

'address'

]

.

"</td>
<td>"

.

$row

[

'gender'

]

.

"</td>
<td>"

.

date

(

'M d, Y'

,

strtotime

(

$row

[

'birthday'

]

)

)

.

"</td>
</tr>
"

;
}
}
else

{
$output

[

'error'

]

=

true

;
}
echo

json_encode

(

$output

)

;
?>

That ends this tutorial. Happy Coding :)

How to Filter Between Two Dates using jQuery with PHP/MySQLi

More options

nofe

452,496

327,690

327,698