help with displaying selecting a date range and displaying results

[PRosales]

I wish to create a simple form that allows the selection of a start date and a stop date with the ability to select what columns to be displayed in the result page.

I am new to php and this is posing to be a problem for me.

Any help or guidance by you experts is greatly appreciated.

Thanks,
Pete Rosales

[ramoneguru]

So, did you just want this form to have several drop down boxes containing something like (this file is called date.php. What do you mean by columns?):
<?php
function getDates($start,$end)
{
$number = $start;

$menu .= "<option value=\"\"></option>\n";

while ($number <= $end)
{
$menu .= "<option value=\"$number\">$number</option>\n";

$number++;
}//end while

return $menu;

}//end getDates

function getForm($monthMenu, $dayMenu, $yearMenu)
{
print "<form name=\"dateForm\" action=\"date.php\" method=\"POST\")";
echo "</br>";
echo "start date: ";
echo '<select name = "startMonth">';
print $monthMenu;
echo '</select>';
echo "/";
echo '<select name = "startDay">';
print $dayMenu;
echo '</select>';
echo "/";
echo '<select name = "startYear">';
print $yearMenu;
echo '</select>';

echo "end date: ";
echo '<select name = "endMonth">';
print $monthMenu;
echo '</select>';
echo "/";
echo '<select name = "endDay">';
print $dayMenu;
echo '</select>';
echo "/";
echo '<select name = "endYear">';
print $yearMenu;
echo '</select>';

//don't know what you mean by "column" are you talking about the month/day/year columns?

echo '<input type="submit" name="submitDate" value="Submit">';
echo '</form>';

}// end getForm

if (isset($_POST[submitDate]))
{
//if you want to display one column then just remove whatever POST variable you don't want
echo "Start Date: $_POST[startMonth] / $_POST[startDay] / $_POST[startYear] <br/>";
echo "to <br/>";
echo "End Date: $_POST[endMonth] / $_POST[endDay] / $_POST[endYear] <br/>";

}//end if
else
{
$startDay = 1;
$endDay = 31;

$startMonth = 1;
$endMonth = 12;

$startYear = 2000;
$endYear = 2020;

$dayMenu = $monthMenu = getDates($startDay, $endDay);
$monthMenu = getDates($startMonth, $endMonth);
$yearMenu = getDates($startYear, $endYear);

getForm( $monthMenu,$dayMenu, $yearMenu);
}//end else

?>

this should do it for you....
--Nick

[suhshe]

I have a similar problem where i have to display the records in a date range which will be selected by the user in two dropdown boxes picked up from the date field. The database contains numerous instances of similar dates for different managers and team leads.
The database contains fields :- Sr.No, Date,Manager,Team Lead, Attendance and calls taken.
How can i populate the drop down with one instance of repeating dates in two drop downs so that the user can select the date range and data can be displayed to the user in a proper formatted row.
I am new to PHP. Nick can u pls help me. Would appreciate ur efforts deeply.

Thanks in advance, Suhshe

[ramoneguru]

A few mods to the original program are necessary. But, why do you need to populate the date fields first (the only reason I see is for optimization)? From your description you want to do the following:
1. show a start date and end date in dropdown boxes
2. have user select and submit those dates
3. return data between the two dates selected by the user.
If that is correct you can use my program that I wrote above and just modify the processing section .If not, then tell me what part is incorrect.

if (isset($_POST[submitDate]))
{

echo "Selected Start Date: $_POST[startMonth] / $_POST[startDay] / $_POST[startYear] <br/>";
echo "to <br/>";
echo "Selected End Date: $_POST[endMonth] / $_POST[endDay] / $_POST[endYear] <br/>";

$start = $_POST[startYear] ."-". $_POST[startMonth] ."-". $_POST[startDay];
$end = $_POST[endYear] ."-". $_POST[endMonth] ."-". $_POST[endDay];

$getData = mysql_query("SELECT date_field, some_field FROM table WHERE date BETWEEN '$start' AND '$end' ");

if ($getData) //if our query was successful then process the results
{
$data = mysql_fetch_array($getData);//get the frist row in our query

while ($data[some_field]) //test to see if the rows we returned are not NULL
{
echo "The comments on $data[date_field] are the following $data[some_field] <br/>";
echo "whatever you want here<br/>";
$data = mysql_fetch_array($getData);//get next row if there are any

}//end while
}//end if
else
{
echo "sorry no results in that date were returned, press back to try again<br/>";

}//end else if no results were returned from the database

}//end if for button pressed test

--Nick

[PRosales]

ramoneguru, thanks for your willingness to help me out.

I have a database that has:

id=1, insert_time=1104500000, nodes_available=64, nodes_busy=10, cpu_available=128, cpu_busy=20, jobs_running=1

The id, insert_time, nodes_available, etc. are the column headings.

The form I am trying to create would allow a query against this table that display the appropriate datas. I am new to this, so I am finding it extremely challenging.

In theory, I see having to have a value of insert_time to =< day_start and another value of insert_time to => day_stop to give me the range. Then I would like to include check boxes for node_available, nodes_busy, etc., to select what datas to view for the time range selected. On submit this would display the desired datas in a table.

I look forward to any guidance you might have. I apologize for the latent reply, but I was out of the net for a couple of days.

Thanks
Pete

[Sarah Atkinson]

for the form you will need to pass the veriables from the checkboxes (values) to the sql quarry. here is what i have for date range but my variables are hard coded.

the form:

<form action="viewmenu.php" method="GET"> 
 
 <label class="labelstyle">Date Range (y-m-d): <br /><input type="text" name="xdate" /> to <input type="text" name="zdate" /></label>
 <br />
 <label class="labelstyle">Menu Area: <br />
 <select name="fromwhere">
  <option value="meal_menus_d">Decatur County</option>
  <option value="meal_menus_bbjj">Bartholomew, Brown, Jackson, Jennings Counties</option>
 </select>
 </label>
 <br />
 <input type ="submit" value="SUBMIT"/>
</form>

the sql:

 if(isset($_GET['fromwhere'])):
$fromwhere = $_GET['fromwhere']; 
$x = DATE($_GET['xdate']);
$z = DATE($_GET['zdate']);
$result = @mysql_query("SELECT id, maincourse, veggie, veggie2, fruit, bread, dessert, drink, DATE_FORMAT(day, '%a %b, %D') as date_string FROM $fromwhere WHERE day>='$x' AND day<='$z'");
if (!$result) {
 exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
?>
<table class="myTable2">
<tr><th>Day</th><th>Main Course</th><th>Veggie1</th> <th>Veggie2</th><th>Fruit</th><th>Bread</th><th>Dessert</th><th>Drink</th><th></th></tr>
<?php
while ($row = mysql_fetch_array($result)) {
   $id=$row['id'];
   $dbdate=$row["date_string"];
   echo '<tr><td>' . $dbdate . '</td>';
   echo '<td>' . $row['maincourse'] . '</td>';
   echo '<td>' . $row['veggie'] . '</td>';
   echo '<td>' . $row['veggie2'] . '</td>';
   echo '<td>' . $row['fruit'] . '</td>';
   echo '<td>' . $row['bread'] . '</td>';
   echo '<td>' . $row['dessert'] . '</td>';
   echo '<td>' . $row['drink'] . '</td>';
   echo ("<td><a href='editmenu.php?id=" . $id . '&fromwhere=' . $fromwhere . '&xdate=' . $x . '&zdate=' . $z . "' class=\"link\">Edit</a><br />");
  echo ("----<br /><a href='deletemenu.php?id=" . $id . '&fromwhere=' . $fromwhere . '&xdate=' . $x . '&zdate=' . $z . "' class=\"link\" >Delete<//a><//td>");
   echo "</tr>";
}?></table>

like i said you will need to pass your veriables also so you will need more things like $foo=$_GET['foo']; then have your php veriables refernced in your quarry.

$result = @mysql_query("SELECT $foo, $bar FROM $fromwhere WHERE day>='$x' AND day<='$z'");
Sarah

[PRosales]

Sarah, thank you for assisting. I benefitted from your contribution by realizing the example you included opened my eyes to the option of allowing the selection of which tables to pull the data from. This is awesome. Thank you.

However, I am not getting the results to display. The url reads view.php?xdate=1104500240&zdate=1104505340&fromwhere=test_Nodes_Available_ACRL_Atlantis
but displays only a blank page.

I tried to put the date range by yyyy-mm-dd and that had the same results. The view.php is:

<?php
// Make a MySQL Connection

if(isset($_GET['fromwhere'])):
$fromwhere = $_GET['fromwhere'];
$x = DATE($_GET['xdate']);
$z = DATE($_GET['zdate']);

$result = @mysql_query("SELECT id, insert_time, nodes_available, nodes_busy, cpu_available, cpu_busy, jobs_running, FROM_UNIXTIME(insert_time) as date_string FROM $fromwhere WHERE insert_time>='$x' AND insert_time<='$z'");
if (!$result) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}
?>
<table>
<tr><th>Day</th><th>Nodes Available</th><th>Nodes Busy</th> <th>CPU Available</th><th>CPU Busy</th><th>Jobs Running</th><th></th></tr>

<?php
while ($row = mysql_fetch_array($result)) {
$id=$row['id'];
$dbdate=$row["date_string"];
echo '<tr><td>' . $dbdate . '</td>';
echo '<td>' . $row['nodes_available'] . '</td>';
echo '<td>' . $row['nodes_busy'] . '</td>';
echo '<td>' . $row['cpu_available'] . '</td>';
echo '<td>' . $row['cpu_busy'] . '</td>';
echo '<td>' . $row['job_running'] . '</td>';
echo ("<td><a href='editmenu.php?id=" . $id . '&fromwhere=' . $fromwhere . '&xdate=' . $x . '&zdate=' . $z . "' class=\"link\">Edit</a><br />");
echo ("----<br /><a href='deletemenu.php?id=" . $id . '&fromwhere=' . $fromwhere . '&xdate=' . $x . '&zdate=' . $z . "' class=\"link\" >Delete<//a><//td>");
echo "</tr>";
}?></table>

I am assuming that I have to make the database connection on this page and not necessarily in the form.htm.

Once again, thank you. I hope you can guide me some more.

Pete

[Sarah Atkinson]

you need to close your if statment

add this to the bottom

<?php endif;?>

and yes you do have to make the DB connection on this page
I do it as an include file

[PRosales]

Sarah,

That did the trick, I am able to view the results now.

I do still have a problem. I had to copy 1104503600 and 1104571940 and paste them in the start and stop fields for it to work. How can I get it to accept 2004-12-31 and 2005-01-01 as date inputs?

Ideally I would love for it to be able to use this code I came across:

<?php
// Make a MySQL Connection

$sql = "SELECT FROM_UNIXTIME(insert_time) as day
FROM
test_Nodes_Available_ACRL_Atlantis";
$result = mysql_query($sql) or die(mysql_error());
echo "<form action='view.php'>";
echo "<select name='xdate'>";
while ($row = mysql_fetch_assoc($result)) {
echo "<option value='{$row['day']}'>{$row['day']}</option>";
}
echo "</select>";
echo "</form>";
?>

which creates a drop down select list from the database that I can choose a date from. I don't know how to enable these drop down lists in the same form. Might you be able to assist me with this?

Many thanks for what you have done for me.
Pete

[Sarah Atkinson]

when you get your dates from the form convert them to unix time.

something like this i think

$xdate=$_GET['xdate'];
$xdate=strtotime($xdate);