Question for pagination.

[Gnub]

I've found a tutorial on pagination, seems like a decent tutorial.  However, all the tutorials i have seen on this subject, always use the "Select * FROM <table>".

 

My queries almost always have a Where statement inbedded into it.  How can i use my queries while still maintaining the tutorials guide, example of where my problem lies is below:

 

$result = mysql_query("SELECT count(*) FROM news_table");
$total = mysql_result($result, 0);
$news = mysql_query("SELECT * FROM news_table ORDER BY date_field
ASC LIMIT $start, $display");

 

My problem lies with the $result part of the code.  I have a table holding over 40-50k rows.  which is why i dont want the select * from table.

 

thanks in advance

 

Gnub

[Hell Toupee]

Just adjust it, and add the WHERE in yourself? If you don't want to select all the fields use "Select field1,field2,field3" instead of "Select *".

 

"Select *" means SELECT ALL fields for each record, not select all the rows so whether or not there are 40,000 rows makes no difference, that's where the LIMIT matters.

 

Hope you understand that, feel free to paste your query with all its conditions if you don't.

[Gnub]

Ok, i've tried to do this on my own.  But im now stumped.

 

All my results are being shown on page 1, and are being repeated on every consecutive page.

 

Help ?

 

$db = mysql_connect("<>", "<>", "<>");
mysql_select_db("<>",$db);

if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
}

$query = "SELECT count(`Teletext`.*) DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC";

$result1 = mysql_query($sql, $db) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result1);
$numrows = $query_data[0];
$rows_per_page = 5;
$lastpage      = ceil($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) {
   $pageno = 1;
} elseif ($pageno > $lastpage) {
   $pageno = $lastpage;
}

$result = mysql_query($sql,$db) or die(mysql_error() . '<br />'. $query);
if (mysql_num_rows($result) >0)
{
while($row = mysql_fetch_array($result)) 

{

***Results***

}

if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
}
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}
}

?>

[Hell Toupee]

$db = mysql_connect("<>", "<>", "<>");

mysql_select_db("<>",$db);

 

if (isset($_GET['pageno'])) {

  $pageno = $_GET['pageno'];

} else {

  $pageno = 1;

}

 

 

$result1 = mysql_query($sql, $db) or trigger_error("SQL", E_USER_ERROR);

$query_data = mysql_fetch_row($result1);

$numrows = $query_data[0];

$rows_per_page = 5;

$lastpage      = ceil($numrows/$rows_per_page);

 

$pageno = (int)$pageno;

if ($pageno < 1) {

  $pageno = 1;

} elseif ($pageno > $lastpage) {

  $pageno = $lastpage;

}]]

 

$start=(($pageno-1)*$rows_per_page);

$display=$start+($rows_per_page-1);

 

$query = "SELECT count(`Teletext`.*) DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC LIMIT $start,$display";

 

$result = mysql_query($sql,$db) or die(mysql_error() . '<br />'. $query);

if (mysql_num_rows($result) >0)

{

while($row = mysql_fetch_array($result))

 

{

 

***Results***

 

}

 

if ($pageno == 1) {

  echo " FIRST PREV ";

} else {

  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";

  $prevpage = $pageno-1;

  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";

}

echo " ( Page $pageno of $lastpage ) ";

if ($pageno == $lastpage) {

  echo " NEXT LAST ";

} else {

  $nextpage = $pageno+1;

  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";

  echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";

}

}

 

?>

 

The stuff I've changed is in red. I moved the query down, so that the variables the pagination system needs are declared before the query is declared, so it can use them.

 

Notice the "LIMIT $start,$display" that has been added, aswell as the start and display variables themselves, if you need help understanding the math involved or if it doesn't work, feel free to ask again, hope it works!

[Gnub]

No change in results, still getting all results on 1 page, and every subsequent page after.

[Hell Toupee]

Where is $sql defined? That's the query that you're running, and that's the query that needs to have LIMITs on it, not $query, sorry I didn't see that.

[Gnub]

Sorry for bringing this back from the dead, but im sure some of you know, that sometimes you need to drop what your doing, and start something that's needed urgently.

 

What i get now:

I get the first 25 of the Query, with the limiter, and offset. The number of pages, that is listed on the bottom "First, Prev, 1,2,3,4,5 Next, Last" is correct. But when i click on any of the links, the results change to the first 25 of all of the data in the table.

 

At the very beginning of the script i have the form Post variables, could they be messing up with my queries, which are generated based on the variables at the beginning?

 

if ($HotelName =="")
{
	 	$limit = 25;                
   $tsql1    = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC;";     
   $result_count   = mysql_query($tsql1);     
   $totalrows      = mysql_num_rows($result_count);  
	if(empty($page)){ 
       $page = 1; 
   } 
	$limitvalue = $page * $limit - ($limit);  
	$tsql = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE ". $From . $To . $Star . $BoardB . $Night ." `DepartureDate` BETWEEN '$DateFrom' AND '$DateTo' Order by Price ASC LIMIT $limitvalue, $limit;"; 
}
if ($HotelName !=="")
{
	 	$tsql = "Select `Teletext`.*, DATE_FORMAT(`DepartureDate`, '%a %D %b') AS revised_date, (`Price`-(`FLIGHTS`+`ACCOM`)) AS YMARGIN FROM `Teletext` WHERE `AccName` LIKE '%$HotelName%' Order BY Price ASC LIMIT $limitvalue, $limit;";
}

$sql = $tsql;

$result = mysql_query($sql) or die("Error: " . mysql_error());  

<<Results Posted>>

if($page != 1){  
       $pageprev = $page--; 
        
       echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> ");  
   }else{ 
       echo("PREV".$limit." "); 
   } 

	$numofpages = $totalrows / $limit;  
   for($i = 1; $i <= $numofpages; $i++){ 
       if($i == $page){ 
           echo($i." "); 
       }else{ 
           echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
       } 
   } 
	if(($totalrows % $limit) != 0){ 
       if($i == $page){ 
           echo($i." "); 
       }else{ 
           echo("<a href=\"$PHP_SELF?page=$i\">$i</a> "); 
       } 
   } 
       if(($totalrows - ($limit * $page)) > 0){ 
       $pagenext = $page++; 
       echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");  
   }else{ 
       echo("NEXT".$limit);  
   }