Generating date SQL by using JQuery UI date picker with PHP code

Date Query Generator

How to use this query

This query is a part of the main query where Date search is applied along with other columns search. Here is the full query

SELECT * FROM table_name WHERE name LIKE '%John%' AND DATE BETWEEN '2017-03-02' AND '2017-03-11'

So we can replace WHERE with AND based on our requirement.

Using backend PHP Script

Usually the query is used in backend PHP script to handle records in a database. For this our form should submit data to backend script and query to be generated based on these data.


$dt1=$_POST['dt1'];
$dt2=$_POST['dt2'];

if(strlen($dt1) >5){ // if from date is filled
$date = new DateTime($dt1);
$dt1=$date->format('Y-m-d'); // To match MySQL date format
$sql= " AND dt >='$dt1'  " ;
}

if(strlen($dt2) >5){ // if to date is filled
$date = new DateTime($dt2);
$dt2=$date->format('Y-m-d'); // To match MySQL date format
$sql= " AND dt <= '$dt2'  " ;
}

if(strlen($dt1) >5 and strlen($dt2>5)){ // if both dates are  filled
$sql= " AND dt between  '$dt1' AND '$dt2'  " ;
}

$sql=preg_replace("/AND/","WHERE",$sql,1); // Remove first  AND with WHERE

Now above code can be joined with our main Query like this.

"SELECT FROM table_name $sql "

JQuery Date picker tutorial

This article is written by plus2net.com team.

plus2net.com

▼ More on Date & Time functions in SQL