PHP Script for Datepicker to slect and diaplay database from Mysql required

[prakashghai]

Friends, I am zero in PHP, but still i have managed to do something to fulfill my requirement.

I am stuck with one thing now..So i need help on....

I am using one html+php form to submit database into mysql.
I created a display of that table through php script on a webpage.
Now i want a datepicker option on that displayed page by which i should able to select the date range and display the data of that date range from my mysql table.
And then take a export of data displayed of selected date range in excel.

This displayed page is login protected, so i want after login the next thing comes in should show a sate selection option which should be fromdate to to date , and then records should displayed from the database and i can take export of those displayed results in excel file.
The code i am using on this page is below which do not have any thing included for excel export and datepicker script, I am pasting the code here and request you to please include the required code in it as required.

Thanks In advance

<?php
    //database connections
    $db_host = 'localhost';
    $db_user = '***********';
    $db_pwd = '*************';
   
    $database = 'qserves1_uksurvey';
    $table = 'forms'; 
    $file = 'export';
    if (!mysql_connect($db_host, $db_user, $db_pwd))
        die("Can't connect to database");
   
    if (!mysql_select_db($database))
        die("Can't select database");
   
    // sending query
    $result = mysql_query("SELECT * FROM {$table} ORDER BY date desc");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    $num_rows = mysql_num_rows($result);
    $fields_num = mysql_num_fields($result);
   
    echo "$num_rows";
    echo "<h1></h1>";
    echo "<table border='1'><tr>";
    // printing table headers
    for($i=0; $i<$fields_num; $i++)
    {
        $field = mysql_fetch_field($result);
        echo "<td>{$field->name}</td>";
    }
    echo "</tr>\n";
    // printing table rows
    while($row = mysql_fetch_row($result))
    {
        echo "<tr>";
   
        // $row is array... foreach( .. ) puts every element
        // of $row to $cell variable
        foreach($row as $cell)
            echo "<td>$cell</td>";
   
        echo "</tr>\n";
    }
    mysql_free_result($result);
    ?>
    </body></html>

[diafol]

Sorry, I got slightly lost at the end of your explanation.
If you want a datepicker, there are hundreds to choose from.
A well-rounded one is the datepicker from jQueryUI.

[prakashghai]

Hi Ardav

Well Thanks for your reply, I know this that there are so many date pickers available, but as i said i am new and zero to development world.

Let me explain my requirement once again.

www.q2serves.com/uksurvey.php

This is the url where i am using the php code to display the records from mysql.

this is a login protected page, now what i want is that after the successful login, i want a date picker option to display first from where i will select the date range like fromdate to todate and then records should display between that date range from my mysql database table.

After displaying the records there should be an option on the page to take a export for the displaying records of that date range.

I don't know how to use the date pickers code or jQueryUI in my code as i know nothing about these.

I will really appreciate if you can help me out with this or add the required code in my above mentioned code for excel export of displaying records and date selection option.

It will be a really big help.

Thanks

Prakash Ghai

[diafol]

OK, the first thing you need to do is to get up to speed on php/mysql and some basic javascript. I'm afraid there are no shortcuts here. I could give you an example, but that won't be very useful if you don't understand it. Besides, forum guidelines dictate that you must show some effort yourself before expecting any help. I realise that this is awkward if you don't know where to start. That's why you need to learn the basics of the language(s) first.

If you go to the jQueryUI site, there are a number of example uses. I'm sure there'll be one there to suit your purposes.

Many datepickers work by hijacking <input> elements within forms. So the implementation is reasonably straightforward. In essence what you need is the following:

Datepicker page:
a form with 2 input elements and a submit button.
js file(s) referenced in the head area of the page.
a js script element which links the inputs to the datepicker

Datahandler file:
this accepts the data from the form. Usually you get the data something like this, depending on what you've called the inputs (id/name attributes):

$from = $_POST['from'];
$to = $_POST['to'];

$sql = mysql_query("SELECT field1,field2 FROM table1 WHERE datefield >= '$from' AND datefield <= '$to'");

Hope that gives you an idea.

[prakashghai]

I have tried as per your guidance and this is what happening now.

this is my code now for index.php

<?php
session_start(); // start session cookies
require("Login.class.php"); // pull in file
$login = new Login; // create object login
$login->authorize(); // make user login
?>
<h2>Reports</h2>
<a href="index.php?action=clear_login">logout</a>
<html>
<head>
<title>Q 2 Serves UK Survey Live CRM</title>
</head><body><form method="POST" action="datepicker.php">
  <div align="center">
    <input type="text" id="fromDate" name="fromDate" />
    <input type="text" id="toDate" name="toDate" />
    <input type="submit" name="filterDate" value="Submit" />
  </div>
</form>
<div align="center">
  <script>
  $(function() {
    $("#fromDate").datepicker();
    $("#toDate").datepicker();
  });
</script>

</div>
</body></html>
<?php
//database connections
$db_host = 'localhost';
$db_user = '***************';
$db_pwd = '*****************';

$database = 'qserves1_uksurvey';
$table = 'forms';  
$file = 'export';
if (!mysql_connect($db_host, $db_user, $db_pwd))
    die("Can't connect to database");

if (!mysql_select_db($database))
    die("Can't select database");

// sending query
$result = mysql_query("SELECT * FROM {$table} ORDER BY date desc");
if (!$result) {
    die("Query to show fields from table failed");
}
$num_rows = mysql_num_rows($result);
$fields_num = mysql_num_fields($result);

echo "$num_rows"; 
echo "<h1></h1>";
echo "<table border='1'><tr>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
    $field = mysql_fetch_field($result);
    echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
    echo "<tr>";

    // $row is array... foreach( .. ) puts every element
    // of $row to $cell variable
    foreach($row as $cell)
        echo "<td>$cell</td>";

    echo "</tr>\n";
}
mysql_free_result($result);
?>

and this is the code for datepicker.php file

<meta charset="utf-8">
    <script>
    $(function() {
        var dates = $( "#from, #to" ).datepicker({
            defaultDate: "+1w",
            changeMonth: true,
            numberOfMonths: 3,
            onSelect: function( selectedDate ) {
                var option = this.id == "from" ? "minDate" : "maxDate",
                    instance = $( this ).data( "datepicker" ),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings );
                dates.not( this ).datepicker( "option", option, date );
            }
        });
    });
    </script>

<div align="center">
<label for="from">From</label>
<input type="text" id="from" name="from"/>
<label for="to">to</label>
<input type="text" id="to" name="to"/>
<p>Select the date range to search for.</p>
</div>

Till yet i have just tried for Datepicker thing not for excel export .. so kindly check this and let me know where i am wrong in this... Whats happening is the date selection option is showing after login page and all the data of Mysql Table as well below the datepicker option, now to select the dates from the date options there is not calender popup coming and secondly if we are entering a date mannualy then after clicking the submit button it is not showing any records just a second page again with date selector option only without any records.

I am going to crazy now.....please help....

Thanks in Advance

Prakash Ghai

[diafol]

Listen, your html is all to hell. You output html before the html tag, you output even more after the </html> tag. I can't see the script reference in the <head> section.

I'm sorry to say this, but, work out how to use html properly before attempting to use javascript and php. Otherwise, as you can see, things get very messy very quickly.

I will be prepared to your php questions once you get a grip on your wild html / document structure. Until then, good luck.

[reygcalantaol]

Having a good and neat coding structure makes your code very useful.