add a jquery date picker to php list

Question

I have a mysql database table which contains a error code, date and mail address.

My script below displays a basic list as per the screenshot,

Script displays as follows

I would like to be able to filter by date, I am hoping to use jquery date picker.

The idea being, only show entries where the date matches that in the jquery date picker.

The php code used to display the list:

    <?php

// Add  Logo
$image = "logo.png";
$width = 300;
$height = 280;
echo '<img src="'.$image.'" style=width:"' . $width . 'px;height:' . $height . 'px;">';

// Make a MySQL Connection
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("pmta_reporting") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM address")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Error</th> <th>Date</th> <th>Mail Address</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
        // Print out the contents of each row into a table
        echo "<tr><td>";
        echo $row['code'];
        echo "</td><td>";
        echo $row['date'];
        echo "</td><td>";
        echo $row['address'];
        echo "</td></tr>";
}

echo "</table>";
// disconnect from the database

    mysql_close();
?>

The code I am using for the date picker is

<!doctype html>

<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title>jQuery UI Datepicker - Default functionality</title>
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
    <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
    <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
    <link rel="stylesheet" href="/resources/demos/style.css" />
    <script>
       $(function() {
          $( "#datepicker" ).datepicker();
       });
   </script>
  </head>
  <body>
     <p>Date: <input type="text" id="datepicker" /></p>
  </body>
</html>

How can I add the jquery date picker to the script so that when a user selects a date the results shown on the page only display the date selected by the user?

I notice you to use a class instead a an id for this kind of functionnality. An id it's just use one times, a class you can use several times — AFrenchDev, Apr 24 '13 at 06:45

score 1 · Answer 1 · answered Apr 24 '13 at 06:55

On value change in datepicker field, reload url with GET variable date:

$( "#datepicker" ).change(function(){
   window.location.href = window.location.href + '?date=' + $(this).val();
})

in php, if get variable is suplied add a where statement to the query:

$query = "SELECT * FROM address"
if (isset( $_GET['date']) && ($date = $_GET['date'])){
  $query .= " WHERE date = '$date'";
}
$result = mysql_query($query)

NOTE! this wont protect against sql injection!

score 1 · Answer 2 · answered Apr 24 '13 at 07:02

This would be a bit complicated to write up, so forgive me for not writing an example, but I'll give you a good idea of how to do this. First of all, you should make a separate PHP file that returns only the table and takes a POST variable argument for the date that it uses to filter the results in the SQL query. Next, use jQuery's .change() on the input field that is the datepicker to make an $.ajax call with $('#datepicker').val() set in the data argument to the PHP file that returns your data and load it into a specified <div>.

You can read more about $.ajax here: http://api.jquery.com/jQuery.ajax/

score 1 · Answer 3 · answered Apr 24 '13 at 07:02

Might not be very efficient, but you can do this.

$("#date").datepicker({"dateFormat": "yy-mm-dd"});

$("#date").change(function() {
    var date_from_date_picker = $(this).val();
    $('td.date').each(function() {
        if ($(this).text() != date_from_date_picker) {
            $(this).parent().hide();
        } else {
            $(this).parent().show();
        }
    });
});

Working demo at http://jsfiddle.net/djhPN/2/

score 1 · Answer 4 · edited May 23 '17 at 12:04

In your HTML:

<body>
  <form method="get" action="yourphpscript">
    <p>Date: <input type="text" name="date" id="datepicker" /></p>
    <input type="submit" value="Search" />
  </form>
</body>

In your PHP you can use PDO or mysqli, that way you can use prepared statements and parameterized queries that protect you against SQL-injection.

Check out this post for more information: Examples of PDO & mysqli

You could also escape the bad sql with the function "mysql_real_escape_string($bad_variable)"

I'll just adjust the code of Joel Harkes to make it work against SQL-injection also:

$query = "SELECT * FROM address"
if (isset( $_GET['date']) && ($date = mysql_real_escape_string($_GET['date']))){
  $query .= " WHERE date = '$date'";
}
$result = mysql_query($query)

score 1 · Answer 5 · answered Apr 24 '13 at 07:24

You want to use ajax to load the content like this.

<!doctype html>

<html lang="en">
<head>
<meta charset="utf-8" />
<title>jQuery UI Datepicker - Default functionality</title>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
<link rel="stylesheet" href="/resources/demos/style.css" />
<script>
   $(function() {
      $( "#datepicker" ).datepicker();
   });

   jQuery(document).ready(function() {
    $("#datepicker").change(function(){

    var new_date = $(this).val();

    jQuery.ajax({
      type: "POST",
      url: "http://www.url.php",
      data: { date:new_date },
      success: function( data ) {
               $('#displaycontent').val(data);
                }
       });
    });
  });
</script>

</head>
<body>
  <p>Date: <input type="text" id="datepicker" /></p>
  <div id="displaycontent"> </div>
</body>
</html>

and ajax file is ex. url.php is like this.

// Add  Logo
$image = "logo.png";
$width = 300;
$height = 280;
echo '<img src="'.$image.'" style=width:"' . $width . 'px;height:' . $height . 'px;">';

// Make a MySQL Connection
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("pmta_reporting") or die(mysql_error());

$newdate = $_REQUEST['date'];

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM address WHERE date=".$newdate) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Error</th> <th>Date</th> <th>Mail Address</th> </tr>";
 // keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['code'];
    echo "</td><td>";
    echo $row['date'];
    echo "</td><td>";
    echo $row['address'];
    echo "</td></tr>";
}

echo "</table>";
 // disconnect from the database

 mysql_close();
?>

Thank you, The only question is I have created 2 files. one with the date, and the other containing the table code. once i select a date, how do I get it to "submit" to display the tables? — rezizter, Apr 24 '13 at 08:02
@rezizter we call ajax so when we change the date, jquery .change event is fire and ajax is call and your data is update. — Bharat Chodvadiya, Apr 24 '13 at 08:08
I think there might be something misconfiguration on my server, as when I select the date it does not move. Is it possible to add a submit button instead? — rezizter, Apr 24 '13 at 08:42
@rezizter check using firebug that this ajax file is call when you change the date. — Bharat Chodvadiya, Apr 24 '13 at 09:36

score 0 · Accepted Answer · edited Mar 31 '14 at 11:22

Ok made some changes. I have created 2 files in the directory.

index.php - this contains the date picker and a submit

file2.php - this contains the database query and tables.

<!doctype html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>jQuery UI Datepicker - Default functionality</title>
        <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
        <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
        <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
        <link rel="stylesheet" href="/resources/demos/style.css" />
        <script>
            $(function() {
                $( "#datepicker" ).datepicker(({ dateFormat: "yy-mm-dd" }));
            });
        </script>
    </head>
    <body>
        <form action="file2.php" method="post">
            <p>Date: <input type="text" name="datepicker" id="datepicker" /></p>
            <div id="displaycontent"> </div>
            <input type="submit" value="Submit" />
        </form>
    </body>
</html>

Then file2.php looks like this:

<?php

// Make a MySQL Connection
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());
$newdate = $_POST['datepicker'];

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM table_name WHERE date='$newdate'") or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Error</th> <th>Date</th> <th>Mail Address</th> </tr>";
 // keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['code'];
    echo "</td><td>";
    echo $row['date'];
    echo "</td><td>";
    echo $row['address'];
    echo "</td></tr>";
}

echo "</table>";
 // disconnect from the database

 mysql_close();
?>

This allows me to filter by date.

Thanks everyone for your contributions

add a jquery date picker to php list

6 Answers6