batching results over multple pages

[raouleduke]

I am trying to display results over multiple pages, Here is what I have

<?php
include_once("header.php");

$From = $_POST['From'];
$To = $_POST['To'];
$SANO = $_POST['SANO'];
$REPORTS = $_POST['REPORTS'];
$limit = '10'; 

mysql_connect(""); 
mysql_select_db(""); 
if($REPORTS=='1'){

if (empty($offset) || $offset < 0) { 
$offset=0; 
} 
if (empty($index)) $index=0; /* This is to count the line index of the results */ 

$getrows = mysql_query("SELECT * FROM KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y'"); 
$numrows=mysql_num_rows($getrows); 

$query = mysql_query("select * from KEYCAD WHERE (PROCESSEDDATE BETWEEN '$From' AND '$To') AND ACTIVE LIKE 'Y' limit $offset,$limit"); 

while ($row=mysql_fetch_array($query)){ 
$index++; 
//print out results
}
if ($numrows <= $limit) { 
} 
else { 

if ($offset!=0) { 
$prevoffset=$offset-$limit; 
echo "<a onMouseOver=\"window.status='Previous $limit Results'; return true\"; href=\"$PHP_SELF?offset=$prevoffset&index=$prevoffset\"><B>[Previous]</B></a>   "; 
} 
else echo "<b><font color=666666>[Previous]</font></b>   "; 

$pages = intval($numrows/$limit); 

if ($numrows%$limit) { 

$pages++; 
} 

for ($i=1;$i<=$pages;$i++) { 

if (($offset/$limit) == ($i-1)) { 

echo " <b><font color=666666>$i</font></b> "; 
} else { 

$newoffset=$limit*($i-1); 
echo " <a onMouseOver=\"window.status='Page $i Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>$i</B></a> \n"; 
} 

} 

if (!((($offset/$limit)+1)==$pages) && $pages!=1) { 

$newoffset=$offset+$limit; 
echo "    <a onMouseOver=\"window.status='Next $limit Results'; return true\"; href=\"$PHP_SELF?offset=$newoffset&index=$newoffset\"><B>[Next]</B></a><p>\n"; 
}  

else echo "   <b><font color=666666>[Next]</font></b>"; 
}
}
?>

[nnichols]

I haven't checked right through your code but the first thing I noticed was that you are using $_POST to
access some post vars so this probably means you have globals off (Yippeee!!!).

So you need to use $_GET to access "offset". You also need to pass "From" and "To" from page to page.

Hope this helps 😉

[raouleduke]

How do I pass the To and From variable from page to page. I think that might be the problem.

[nnichols]

The easiest way is to add them to the url for next/prev page links.

Globals on - boo! hiss! turn 'em off!!

[raouleduke]

I would like to print out records over multiple pages, I found this by ednark, but do not know how to put it in my code, can you help.
Here is ednarks code:

<?php
/**
*   This passes three important pieces of information between page calls, the current page we wish to view, the total number of results, and the numberof results displayed on each page
*
*   it is up to the recieveing page to determine defualt values for the first call to this function, as well as use this info passed to actually retieve and display each results, this only gives you the menu, but should give each page enough info to determine what to do
**/

function batchDisplayMenu( $link_location, $current_page, $total_batch_size, $batch_step_size, $extra_get_params=array(), $menu_properties=array() )
{
    /// display options
    $display_all_pages  = FALSE;
    $page_display_range = 4;

$show_total_pages = TRUE;
$show_ellipses    = TRUE;
$show_next        = TRUE;
$show_previous    = TRUE;
$show_last        = TRUE;
$show_first       = TRUE;

/// format templates for displaying menu
$total_pages_format_string  = "%s results in %s page(s) : ";
$ellipse_format_string      = "...";
$no_ellipse_format_string   = "   ";
$next_format_string         = "&gt;&gt;";
$previous_format_string     = "&lt;&lt;";

$first_format_string        = "first";
$last_format_string         = "last";
$current_page_format_string = "<b>%s</b>";
$blank_page_format_string   = "  ";

$width = 20;

/// this should overwrite any previous variables
extract($menu_properties);

/// display ranges for pages
$total_pages = ceil( $total_batch_size / $batch_step_size );

$page_range_begin = max( ($current_page-$page_display_range), 1 );
$page_range_span  = ($page_display_range*2);
$page_range_end   = ($page_range_begin+$page_range_span);


/// pass along, via GET, anything in the extra_get_params array, when a user clicks on new page of results to view
$link_format_string = "<a href=\"{$link_location}?current_page=%s&batch_step_size={$batch_step_size}&total_batch_size={$total_batch_size}";
foreach ( $extra_get_params as $item=>$value ) {
    $safe_value = str_replace( '%', '%%', urlencode($value) );
    $link_format_string .= "&{$item}={$safe_value}";
}
$link_format_string .= "\">%s</a>";

/// show the X out of Y page results
if ( $show_total_pages ) {
    $string .= '<center>';
    $string .=  sprintf($total_pages_format_string,$total_batch_size,$total_pages);
    $string .= '</center>';
}

$string .= '<table border="0" class="batch_list"><tr align="center">';

$string .= '<td nowrap="nowrap">';

/// show link to jump to first page
if ( $show_first ) {
    if ( $current_page > 1 ) {
        $string .=  sprintf($link_format_string,1,$first_format_string
) ." ";
        } else {
            $string .= $first_format_string ." ";
        }
    }

/// show link to go back one page
if ( $show_previous ) {
    if ( $current_page > 1 ) {
        $string .= sprintf($link_format_string,max(($current_page-1),1),$previous_format_string) ." ";
    } else {
        $string .= $previous_format_string ." ";

    }
}

/// to ellipse or not ellipse
$string .= ( $show_ellipses and $page_range_begin>1 ) ? sprintf($ellipse_format_string)." " : sprintf($no_ellipse_format_string)." ";
$string .= '</td>';

/// show either all pages on screen at once, or show only X at a time, like google, a batch of batch pages 
if ( $display_all_pages ) {
    $link_range = range( 1, $total_pages );
} else{
    $link_range = range( $page_range_begin, $page_range_end );
}

/// them meat, show a link to each page of items we have
foreach ( $link_range as $index ) {
    $string .= '<td width="'.$width.'" nowrap="nowrap">';
    if ( $index == $current_page ) {
        $string .= sprintf($current_page_format_string,$index) ." ";
    } elseif ( $index <= $total_pages ) {
        $string .= sprintf($link_format_string,$index,$index) ." ";
    } elseif ( $index > $total_pages ) {
        $string .= $blank_page_format_string ." ";
    }
    $string .= '</td>';
}

$string .= '<td nowrap="nowrap">';
$string .= ( $show_ellipses and $page_range_end<$total_pages ) ? $ellipse_format_string ." " : $no_ellipse_format_string ." ";

/// show a link to skip foreward on epage
if ( $show_next ) {
    if ( $current_page < $total_pages ) {
        $string .=  sprintf($link_format_string,min(($current_page+1),
$total_pages),$next_format_string) ." ";
        } else {
            $string .= $next_format_string ." ";
        }
    }

/// show a link to the last page
if ( $show_last ) {
    if ( $current_page < $total_pages ){
        $string .=  sprintf($link_format_string,$total_pages,$last_format_string) ." ";
    } else {
        $string .= $last_format_string ." ";
    }
}
$string .= '</td>';

$string .= '</tr></table>';

return $string;
}
?>

And here is my code:

<?php

$From = $_POST['From'];
$To = $_POST['To'];


mysql_connect(""); 
mysql_select_db(""); 
$query = "SELECT * FROM KEYCAD WHERE KEYCAD.`PROCESSEDDATE` BETWEEN '$From' AND '$To' AND ACTIVE LIKE 'Y' AND FOLLOWUPCONTACT LIKE 'Y' ORDER BY SANO"; 
    $result = mysql_query($query);
	while($row = mysql_fetch_array($result)) {
	echo"<hr>";
	echo"<table border='0' cellpadding='0' cellspacing='0' width='650'>";
    printf("
	<tr>
      <td width='200'>
         <table border='0' cellpadding='0' cellspacing='0' width='220'>
            <tr>
              <td colspan='2' width='220'><b>%s</b></td>
            </tr>
            <tr>
              <td width='101'><b>CU</b></td>
              <td width='97'>%s</td>
            </tr>
            <tr>
              <td width='101'><b>(H)</b></td>
              <td width='97'>%s</td>
            </tr>
            <tr>
              <td width='101'><b>(W)</b></td>
              <td width='97'>%s</td>
            </tr>
            <tr>
              <td width='109'><b>Service Date:</b></td>
              <td width='109'>%s</td>
            </tr>
            <tr>
              <td width='109'><b>Contact Date:</b></td>
              <td width='109'>%s</td>
            </tr>
          </table>
      </td>
      <td width='200'>
         <table border='0' cellpadding='0' cellspacing='0' width='202'>
            <tr>
              <td width='200' colspan='2'>
                <div align='left'>
                  <table border='0' cellpadding='0' cellspacing='0' width='200'>
                    <tr>
                      <td width='20'>%s</td>
                      <td width='20'>%s</td>
                      <td width='20'>%s</td>
                      <td width='20'>%s</td>
                      <td width='20'>%s</td>
                    </tr>
                  </table>
                </div>
              </td>
            </tr>
            <tr>
              <td width='200' colspan='2'>&nbsp;</td>
            </tr>
			<tr>
              <td width='200' colspan='2'>&nbsp;</td>
            </tr>
            <tr>
              <td width='26'><b>VIN</b></td>
              <td width='172'>%s</td>
            </tr>
            <tr>
              <td width='68'><b>Repair #</b></td>
              <td width='132'>%s</td>
            </tr>
            <tr>
              <td width='68'><b>Tech #</b></td>
              <td width='132'>%s</td>
            </tr>
          </table>
        </div>
      </td>
      <td>
        <div align='left'>
          <table border='0' cellpadding='0' cellspacing='0' width='250' height='104'>
          <tr>
            <td height='104'>%s</td>
          </tr>
        </table>
        <table border='0' cellpadding='0' cellspacing='0' width='250' height='15'>
          <tr>
            <td height='15' width='155'><b>Hotspot:</b></td>
            <td height='15' width='91'>%s</td>
          </tr>
        </table>
        </div>
      </td>
    </tr>
  </table>\n",

  $row["CUSTOMERNAME"], 
  $row["CUSTNO"], 
  $row["HOMEPHONE"], 
  $row["BUSPHONE"], 
  $row["CLOSEDATE"], 
  $row["CONTACTDATE"], 
  $row["Q1"], 
  $row["Q2"],
  $row["Q3"], 
  $row["Q4"], 
  $row["Q5"], 
  $row["VEHID"], 
  $row["RO"], 
  $row["TECH"], 
  $row["COMMENTS"], 
  $row["FOLLOWUPCONTACT"]  

  );
}
echo"<table height='50'></table>\n";

?>

Thanks for your help.

[ednark]

ok here is the basic trick

you want to use the LIMIT functionality of Mysql, which you are doing it correctly... but i put a reference at the bottom just in case you aren't sure what the magic of this is...

your problem seems to be pointed out correctly by nnichols, and he gave you the correct solution...

any variables you are passing between pages in the url string... like

?offset=10

php will store in $GET, while any form variables in <from method="post"> will be stored in $POST

since you are sending variables across in the <a href="" > tag

you need to change $POST into $GET

$From = $_POST['From'];

into

$From = $_GET['From'];

see if that works

read more http://www.mysql.com/doc/en/SELECT.html

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT # OFFSET #.

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset upto the end of the result set, you can use -1 for the second parameter:

mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.

If one argument is given, it indicates the maximum number of rows to return:

mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT n is equivalent to LIMIT 0,n.

[ednark]

another bit of advice....

the printf() function i was using is a relic from the conversion of this original script from python and is better used for short strings

for your script i suggest using the heredoc syntax for a large string like you are using, this allows you to name the variable you are using in the place you want to put it... instead of placemarking it with that nasty %s

while($row = mysql_fetch_array($result)) {

print <<<MY_ENDING_TOKEN
    <tr>
      <td width='200'>
         <table border='0' cellpadding='0' cellspacing='0' width='220'>
            <tr>
              <td colspan='2' width='220'><b>{$row["CUSTOMERNAME"]}</b></td>
            </tr>
            <tr>
              <td width='101'><b>CU</b></td>
              <td width='97'>{$row["CUSTNO"]}</td>
            </tr>
            ... etc ...
MY_ENDING_TOKEN;

} /// end while

[raouleduke]

Do you know of a way that I can put this whole thing into a PDF, went and downloaded several PDF Classes from post that I have found in phpbuilder but none of them tell me how I can take a query like this and put it into a PDF.
Thanks,