CODEWITHSUNDEEP
phpjquery
rezizter
rezizter

Reputation: 5168

add a jquery date picker to php list

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?

Upvotes: 0

Views: 11459

Answers (6)

rezizter
rezizter

Reputation: 5168

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

Upvotes: 0

Bharat Chodvadiya
Bharat Chodvadiya

Reputation: 1650

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();
?>

Upvotes: 1

MMeersseman
MMeersseman

Reputation: 2631

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)

Upvotes: 1

viclim
viclim

Reputation: 959

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/

Upvotes: 1

Cezary Wojcik
Cezary Wojcik

Reputation: 21845

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/

Upvotes: 1

Joel Harkes
Joel Harkes

Reputation: 11661

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!

Upvotes: 1

Related Questions