PHP pagination by a date field

[bedelc]

Hi, I am currently working on a project that has me banging my head in frustration.

 

I am trying to use page numbers but I need to have the results of my search displayed together with records of the same date, not by using a specified number of records per page. I am using PHP 5.0 with a MySQL DB.

 

Sorry if that was confusing, my brain is fried. Thanks all.

 

 

[Jax2]

In your pagination links, you can simply have it call 2 different variables, page number and $date, such as:

 

http://foo.com/nextpage?currentpage=3&date=$date

 

and then use a sanitized GET or REQUEST on the next page:

 

$date = sanitize($_REQUEST['date'];

using whatever method you use to prevent injection in your get/post data calls.

 

[bedelc]

I don't think I am understanding you completely (I am still not an expert with php and mysql), but let me explain a little more. I am pulling records using  BETWEEN in my Mysql statement. ie... "SELECT * FROM meals WHERE menudate BETWEEN '$sd' AND '$ed' ORDER BY menudate, meal ASC". Sometimes, there are 5 records, sometimes there are 20. I want to show each days records for the length of the date range, but only one days records per page.

 

When setting up the pagination for the results, I only know of how to do so using record counts. Can you give me a little more assistance in how I would write the php that would lead to the seperation by my datefield?

 

Thank you so much.

[Jax2]

I don't know for sure that this will work for you, but give it a try... :

 

complete pagination script:

$rowsperpage = 1; // how many items per page
$range = 3;// how many pages to show in page link
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) 
{
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} 
else 
{
   // default page num
   $currentpage = 1;
} // end if
// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

$sql = "SELECT COUNT(*) FROM meals WHERE menudate BETWEEN '$sd' AND '$ed'";
$result = mysql_query($sql, $db) or die(mysql_error());
$r = mysql_fetch_row($result);
$numrows = $r[0];
$totalpages = ceil($numrows / $rowsperpage);

if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

$sql = "SELECT * FROM meals WHERE menudate BETWEEN '$sd' AND '$ed' ORDER BY menudate, meal ASC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $db) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {?>

YOUR CODE TO SHOW EACH DAYS INFO HERE

<?php
};
$result = mysql_query("SELECT * FROM meals WHERE menudate BETWEEN '$sd' AND '$ed'");
$num_rows = mysql_num_rows($result);
echo "Page ".$currentpage." of ".$totalpages."<br>";   
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&'>$x</a> ";
      } // end else
   } // end if 
} // end for
              
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
  
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
}

 

That works perfectly on MY site, you may need to mess around with it a bit though. It should work. Just make sure your connected to the database how you usually do before your queries, I have db.php set up with my connection, but wasn't sure if you did so I left it out.

 

Had to change some things, reload the page ...

 

 

 

[bedelc]

Thank you so much Jax2 for that script. However I have been trying to adapt it for the last 4 hours and still cant get past the first couple issues. I am using php5 but it looks like it is having issue with the ceil() tag in addition to others. I am sure the script is great, but I cant get it past this error. Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/content/c/b/e/cbedell74/html/cacfp/cacfp_report.php on line 52. I have tried repeatedly to get it to accept the code, but alas...no go.  Thanks so much for the help, and if I figure out a solution, I will be sure to post it on here in case anybody else is trying to do this.

[harristweed]

I think the way you are attempting to solve this is wrong. Each page needs to display a menu for a specific day, therefore have a mysql query for each day that finds the menu for that specific day.

 

I don't know where you are getting the start day and end day from but it would not be difficult to count the days between these two dates and put the dates in to an array.

 

The number of days will equal the number of pages, so a page for each day with the relevant query.....

[bedelc]

The dates come from user input. I have it set up to ask for a start date and end date. The range is not predetermined nor are the dates that would be searched. What is your suggestion as to how I would create a page for each day? Thanks so much!

[harristweed]

Something like this?

date_default_timezone_set('America/Los_Angeles');// or where you are

function date_diff($start, $end)
{
        $sdate = strtotime($start);
        $edate = strtotime($end);
        $time = $edate - $sdate;
        if($time>=86400) {
                $days = ($edate - $sdate) / 86400;
        }
        return $days;
}
// example dates
  $sd="2010/3/2";
  $ed="2010/3/9";
  
  $diff= date_diff($sd, $ed);
  // this is the total days therefore total pages
  echo"days=$diff";
  // create the pages as you would with normal pagination
  
  // get the date for the page/day
  // e.g. page 3
  $page_number=3;
$starting_day=strtotime($sd);
$calculated_day = $starting_day+(60*60*24*$page_number);
$date_for_this_day=date("Y-m-d",$calculated_day);
// the mysql_query would be something like
$result = mysql_query("SELECT * FROM meals WHERE menudate = '$date_for_this_day''");