Drop Down Date Interval Selection Box Help Plz

CodeMama · August 5, 2009

anyone having problems posting

CodeMama · August 5, 2009

here is my code....trying to get a drop down to pass to a query:

              $seven = $_GET['seven'];
                                  $thirty = $_GET['thirty'];
                                  $sixty = $_GET['sixty'];                         

                                     
                                     if (!empty($_GET['seven']))
                                        {
                                        $seven = $_GET['seven'];
                                        $_SESSION['seven'] = $seven;
                                        
                                        $searchDate = TRUE;
                                        }
                                        else
                                        {
                                            $seven = '';                                      
                                            }
                                                   
                                     if (!empty($_GET['thirty']))
                                        {
                                        $thirty = $_GET['thirty'];
                                        $_SESSION['thirty'] = $thirty;
                                        
                                        $searchDate = TRUE;
                                        }
                                        else
                                        {
                                            $thirty = '';                                      
                                            }
                                              
                                           if (!empty($_GET['sixty']))
                                        {
                                        $thirty = $_GET['sixty'];
                                        $_SESSION['sixty'] = $sixty;
                                        
                                        $searchDate = TRUE;
                                        }
                                        else
                                        {
                                            $sixty = '';                                      
                                            }    
                                            
                                            
                                            
                                    // Grab all restaurants in alphabetical order
                                    
                                        $pastDays = strtotime("-30 days");
                                    $today = getdate();

                              //Working Date Query
                              $sql = "SELECT r.ID, r.name, r.address, i.inDate
                              FROM restaurants r, inspections i
                              WHERE r.ID = i.ID
                              AND r.name != '' ";
  
                            if ($seven) {
                               $sql .= "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= i.inDate  ";
                           }
                          
                            if ($thirty) {
                               $sql .= "DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.inDate  ";
                           }
                              if ($sixty) {
                               $sql .= "DATE_SUB(CURDATE(),INTERVAL 60 DAY) <= i.inDate  ";
                           }
                           $sql .= "GROUP BY r.ID, r.name, r.address, i.inDate
                              ORDER BY  i.inDate DESC ";
                           
                               
                           $result = mysql_query($sql) or die(mysql_error());

CodeMama · August 5, 2009

Updated Code caught some of my more obvious mistakes ...I know it lies in my sessions...right now it appears to ALMOST WORK...the date selection part works but then it is also outputting all the restaurants...so it will say...output the last seven days, then after that..list everything...

updated code:

  <?php
						    								    								    								    
							    // Check if page is set to show all
							    if(isset($_GET['show']) && $_GET['show'] == 'all')
							    {
							      unset($_SESSION['results']);
							      unset($_SESSION['searchname']);
							      unset($_SESSION['address']);
							      
							     
							    }
							    
							    // Check if there was an empty search sent
							    if(isset($_SESSION['noVarsSent']))
							    {
							      echo "<p><b>No values were submitted for the search.</b></p>";
							      // Unset it so a reload of page doesn't redisplay the error
							      unset($_SESSION['noVarsSent']);
							      // unset($_SESSION['results']);
							    }
							    
							    
							    
                                                               //unset($_SESSION['fullRestaurantList']);

							    // Check if full list of restaurants has been created and stored yet
							    // Store full results in $_SESSION to limit database hits
							   // if(!isset($_SESSION['fullRestaurantList']))
							    {
							      // List not grabbed yet, so run query and store in $_SESSION
							     
							     
							     //check for range
							     
							    if (!(isset($rangenum)))
							      {
							      $rangenum = 1;
							      }
										     

							     
							     if (!empty($_POST['seven']))
								    {
									$seven = $_POST['seven'];
									$_SESSION['seven'] = $seven;

									$searchDate = TRUE;
									}
									else
									{
									    $seven = '';									  
									    }
									  	     
							     if (!empty($_POST['thirty']))
								    {
									$thirty = $_POST['thirty'];
									$_SESSION['thirty'] = $thirty;

									$searchDate = TRUE;
									}
									else
									{
									    $thirty = '';									  
									    }
									      
									   if (!empty($_POST['sixty']))
								    {
									$thirty = $_POST['sixty'];
									$_SESSION['sixty'] = $sixty;

									$searchDate = TRUE;
									}
									else
									{
									    $sixty = '';									  
									    }    
									    
									    
									    
							    // Grab all restaurants in alphabetical order
							    
							        $pastDays = strtotime("-30 days");
								$today = getdate();

					      //Working Date Query
					      $sql = "SELECT r.ID, r.name, r.address, i.inDate
						  FROM restaurants r, inspections i
						  WHERE r.ID = i.ID
						  AND r.name != '' ";
  
					    if ($seven) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= i.inDate  ";
					   }
					  
					    if ($thirty) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.inDate  ";
					   }
					      if ($sixty) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 60 DAY) <= i.inDate  ";
					   }
					   $sql .= "GROUP BY r.ID, r.name, r.address, i.inDate
						  ORDER BY  i.inDate DESC ";
					   
					       
					   $result = mysql_query($sql) or die(mysql_error());


							    
							    //trying to grab it by ranges from the db?
							         $rows = mysql_num_rows($result);




							      $page_rows = 100;
							      $last_row = ceil($rows/$page_rows);
							      
							      if ($rangenum < 1)
							    {
							      $rangenum = 1;
							      }
							      elseif ($rangenum > $last_row)
							      {
							      $rangenum = $last_row;
							      }
							      
							      //This sets the range to display in our query
							      $max = 'limit ' .($rangenum - 1) * $page_rows .',' .$page_rows; 
							      
							      

							      
							      // Process all results into $_SESSION array
							      
							      $position = 1;
							      
							      while ($row = mysql_fetch_array($result))
							      {
								  $_SESSION['searchDates'][$position] = $row;
								  $position++;
							      }
							      
							      $_SESSION['totalNumberOfRestaurants'] = $position;
							      

							    }
							  ?>

darkfreaks · August 5, 2009

are you using session_start() at the top of the page :confused:

CodeMama · August 5, 2009

why yes...lmao..that much I do know....roflmao...

ok here is my most recent updated code...got the full db results to stop outputting..but now I realize that my 7, 30, and 60 day interval query isn't working:

	  <?php
						    								    								    								    
							    // Check if page is set to show all
							    //if(isset($_GET['show']) && $_GET['show'] == 'all')
							    {
							      unset($_SESSION['seven']);
							      unset($_SESSION['thirty']);
							      unset($_SESSION['sixty']);
							      unset($_SESSION['searchDate']);
							      
							     
							    }
							    
							    // Check if there was an empty search sent
							    if(isset($_SESSION['noVarsSent']))
							    {
							      echo "<p><b>No values were submitted for the search.</b></p>";
							      // Unset it so a reload of page doesn't redisplay the error
							      unset($_SESSION['noVarsSent']);
							       unset($_SESSION['results']);
							    }
							    
							    
							    
                                                               //unset($_SESSION['fullRestaurantList']);

							    // Check if full list of restaurants has been created and stored yet
							    // Store results in $_SESSION to limit database hits
							    if(!isset($_SESSION['results']))
							    {
							      // List not grabbed yet, so run query and store in $_SESSION
							     
							     
							     //check for range
							     
							    if (!(isset($rangenum)))
							      {
							      $rangenum = 1;
							      }
										     

							     
							     if (!empty($_POST['seven']))
								    {
									$seven = $_POST['seven'];
									$_SESSION['seven'] = $seven;

									$searchDate = TRUE;
									}
									else
									{
									    $seven = '';									  
									    }
									  	     
							     if (!empty($_POST['thirty']))
								    {
									$thirty = $_POST['thirty'];
									$_SESSION['thirty'] = $thirty;

									$searchDate = TRUE;
									}
									else
									{
									    $thirty = '';									  
									    }
									      
									   if (!empty($_POST['sixty']))
								    {
									$thirty = $_POST['sixty'];
									$_SESSION['sixty'] = $sixty;

									$searchDate = TRUE;
									}
									else
									{
									    $sixty = '';									  
									    }    
  
							        $pastDays = strtotime("-30 days");
								$today = getdate();

					      //Working Date Query
					      $sql = "SELECT r.ID, r.name, r.address, i.inDate
						  FROM restaurants r, inspections i
						  WHERE r.ID = i.ID
						  AND r.name != '' ";
  
					    if ($seven) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= i.inDate  ";
					   }
					  
					    if ($thirty) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.inDate  ";
					   }
					      if ($sixty) {
						   $sql .= "DATE_SUB(CURDATE(),INTERVAL 60 DAY) <= i.inDate  ";
					   }
					   $sql .= "GROUP BY r.ID, r.name, r.address, i.inDate
						  ORDER BY  i.inDate DESC ";
					   
					       
					   $result = mysql_query($sql) or die(mysql_error());


							    
							    //trying to grab it by ranges from the db?
							         $rows = mysql_num_rows($result);




							      $page_rows = 100;
							      $last_row = ceil($rows/$page_rows);
							      
							      if ($rangenum < 1)
							    {
							      $rangenum = 1;
							      }
							      elseif ($rangenum > $last_row)
							      {
							      $rangenum = $last_row;
							      }
							      
							      //This sets the range to display in our query
							      $max = 'limit ' .($rangenum - 1) * $page_rows .',' .$page_rows; 
							      
							      

							      
							      // Process all results into $_SESSION array
							      
							      $position = 1;
							      
							      while ($row = mysql_fetch_array($result))
							      {
								  $_SESSION['searchDate'][$position] = $row;
								  $position++;
							      }
							      
							      //$_SESSION['totalNumberOfRestaurants'] = $position;
							      

							    }
							  ?>

							<table width="900" border="0">
						     
							<Tr>
							  <td height="30" colspan="2"></td>
							  
							  <h3>Restaurant Inspections</h3>
						      <form action="<?=$_SERVER['PHP_SELF']?>" method="post">

							  <select name="searchDate">			    
							    <option value="seven">Last 7 Days</option>
							    <option value="thirty">Last 30 Days</option>
							    <option value="sixty">Last 60 Days</options>
							  </select>
							  <input name="searchDate" type="submit" value="Search">
						    


						      </tr>

					  
						 <tr align="center">
						    <td align="center" colspan="3">
						  
						      </td>
						      </tr>
						 <tr>  <td align="center" colspan="3"><p>The following listings are the Greene County Department of Health Restaurant Inspections from the last 30 days.</p></td></tr>
					      </table>
					      <?php

						if(isset($_GET['Page']))
						  $Page = $_GET['Page'];
						else
						  $Page = 1;
						  
						  

						  
						  
						$PerPage = 50;
						if(isset($_SESSION['numResults']))
						{
						  $TotalPages = ceil($_SESSION['numResults']/$PerPage);
						}
						else
						{
						  $TotalPages = 1;
						}

						$StartPage = ($Page - 1) * $PerPage;

						// Output table header
						  ?>
					      
						  <TABLE WIDTH="100%" cellspacing="3" cellpadding="4">
						    <TR>
						      <TD ALIGN="CENTER" width="50" VALIGN="TOP" BGCOLOR="#000000"><FONT size="-2" COLOR="#FFFFFF"><B>Name</B></FONT></TD>
						      <TD ALIGN="CENTER"  width="50" VALIGN="TOP" BGCOLOR="#000000"><FONT size="-2" COLOR="#FFFFFF"><B>Address</B></FONT></TD>
						      <TD ALIGN="CENTER" width="20" VALIGN="TOP" BGCOLOR="#000000"><FONT size="-2" COLOR="#FFFFFF"><B>Inspection Date</B></FONT></TD>
						      
						    </TR>
						  <?php

						// Control logic to determine if displaying results or not
						if(isset($_SESSION['results']))
						{
						  // Output results

						  if($_SESSION['results'] == "No results found")
						  {
						    // Query came back with nothing
						    ?>
						    <TR bgcolor="<?php echo $row_color ?>">
						      <td>
							<p>No results where found matching your criteria.</p>
						      </td>
						    </tr>
						    <?php
						      
						  }
						  else
						  {
						    // There are results  
						  //$ID= $_SESSION['results'][$i]['ID'];
						  

						  
						    for($i = $StartPage + 1; $i < ($PerPage*$Page) && $i < $_SESSION['numResults']; $i++)
						    {
						      
						      $color1 = "#FFFFFF"; 
						      $color2 = "#EAEAEA"; 

						      $row_color = ($i % 2) ? $color1 : $color2;
						      
						      ?>
						      <TR bgcolor="<?php echo $row_color ?>">
							<TD><FONT SIZE="-2"><a href="view.php?ID=<?php echo ($_SESSION['results'][$i]['ID']); ?>"><?php echo htmlspecialchars(stripslashes($_SESSION['results'][$i]['name'])); ?></a></FONT></TD>
							<TD><FONT SIZE="-2"><?php echo stripslashes($_SESSION['results'][$i]['address']); ?></FONT></TD>
							<TD><FONT SIZE="-2">
							<?php 

							      
						      $date = strtotime($_SESSION['results'][$i]['inDate']);
                                                      $formatDate = date('M j Y', $date);

						      echo $formatDate;


						       ?></FONT></TD>

							     
						      </TR>



						    <?php
						    }
						    ?>
						    </TABLE>
						    <?php
						  
						  }
						  
						}
						else
						{

						  
						  for($i = $StartPage + 1; $i < ($PerPage*$Page) && $i < $_SESSION['searchDate']; $i++)
						  {
						    
						    $color1 = "#FFFFFF"; 
						    $color2 = "#EAEAEA"; 
					      
						    $row_color = ($i % 2) ? $color1 : $color2;
						    ?>
						  
					      
					        <TR bgcolor="<?php echo $row_color ?>">
						      <TD><FONT SIZE="-2"><a href="view.php?ID=<?php echo $_SESSION['searchDate'][$i]['ID']?>"><?php echo htmlspecialchars(stripslashes($_SESSION['searchDate'][$i]['name'])); ?></a></FONT></TD>
						      <TD><FONT SIZE="-2"><?php echo stripslashes($_SESSION['searchDate'][$i]['address']); ?></FONT></TD>
						      <TD><FONT SIZE="-2"><?php
						      
						      $date = strtotime($_SESSION['searchDate'][$i]['inDate']);
                                                      $formatDate = date('M j Y', $date);

						      echo $formatDate;
						       ?></FONT></TD>
						    
						    </TR>
						      
						  <?php
						  }
						  ?>
						  </TABLE>
						  <?php
						  
						  // Calculate total number of pages
						  $TotalPages = ceil($_SESSION['results']/$PerPage);
						  
						}


						// Output paging control
						// Only need to output this if there is more than 1 page of results
						if ($TotalPages > 1)
						{
						  
						  echo "<hr /> \n <p>Click on the following links for more pages of results</p> \n <p>";
						  
						  if ($Page != 1)
						  {
						    // If on first page, no need for Previous Page button
						    echo "<a href='restaurants.php?Page=";
						    echo $Page-1;
						    echo "'><< Previous</a> ";
						  }
						    
						  for($i = 1; $i <= $TotalPages; $i++)
						  {
						    
						    if($i == $Page)
						    {
						      // Outputting link for current page - doesn't need to be a link
						      echo "$i ";
						    }
						    else
						    {
						      // Outputting link for other pages - needs to be a link
						      echo "<a href='restaurants.php?Page=$i'>$i</a> ";
						    }
						    
						  }
						  
						  if ($Page != $TotalPages)
						  {
						    // If on last page, no need for Next Page button
						    echo "<a href='restaurants.php?Page=";
						    echo $Page+1;
						    echo "'>Next >></a> ";
						  }
						  
						}

					      ?>

darkfreaks · August 5, 2009

have you tried using the SUBDATE function instead of DATE_SUB?

also

<= i.inDate

needs to be after the set interval so like

INTERVAL 30 days <= i.inDate)";

Sign In

Drop Down Date Interval Selection Box Help Plz

Recommended Posts

CodeMama

Link to comment

Share on other sites

CodeMama

Link to comment

Share on other sites

CodeMama

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

CodeMama

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information