0

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?

Improve this question
1
  • 1
    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 Commented Apr 24, 2013 at 6:45

6 Answers 6

Reset to default
1

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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/

Improve this answer

Comments

1

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/

Improve this answer

Comments

1

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)
Improve this answer

Comments

1

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

5 Comments

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 we call ajax so when we change the date, jquery .change event is fire and ajax is call and your data is update.
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 check using firebug that this ajax file is call when you change the date.
@rezizter it also possible using submit button.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.