Paging error

[NoobPHP]

'm working on paging the results.. However, I got this problem that i can't solve.. I'm able to limit the results to 10 per page, but when i click on next in hope to display the other 10 results.. the whole page refreshes.. and all the values are gone.. That is to say.. it can't display the following results.. The other thing is.. when i search on field that would return only 2 results.. why is it that it displays page 1 page 2 page 3 links.. together with next and last page..

if im not wrong its due to this query..

$query   = "SELECT COUNT(*) AS numrows FROM Report";
$resultAircraftDisplay  = mysql_query($query)
or die("couldn't execute query.");
$row     = mysql_fetch_array($resultAircraftDisplay, MYSQL_ASSOC);
$numrows = $row['numrows'];

how should i only count the number of rows of the results... just for your info.. i'm doing a optional search engine.. user can choose to do a search on any fields he like.. Any help would be appreciated..

// how many rows to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$queryAircraftDisplay = "SELECT * from report where reportID != 0"; 
if (!empty($_GET['AircraftType'])) 
{ 
$queryAircraftDisplay  .= " and aircraftType='".$_GET['AircraftType']."' "; 
}
if (!empty($_GET['Keyword']))
{ 
$queryAircraftDisplay  .= " and reportID in ($newarr)";
}

echo $queryAircraftDisplay;
$queryAircraftDisplay .= "LIMIT $offset, $rowsPerPage";
$resultAircraftDisplay = mysql_query($queryAircraftDisplay) or die ("couldn't execute query ".mysql_error()); 
$num= mysql_numrows($resultAircraftDisplay); 



$query   = "SELECT COUNT(*) AS numrows FROM Report";
$resultAircraftDisplay  = mysql_query($query)
or die("couldn't execute query1.");
$row     = mysql_fetch_array($resultAircraftDisplay, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);




// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}
if ($pageNum > 1)
{
$page  = $pageNum - 1;
$prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev  = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;
?>

[kburger]

NoobPHP wrote:

how should i only count the number of rows of the results

This query is counting ALL the rows in your table:

$query   = "SELECT COUNT(*) AS numrows FROM Report";

Instead, use the query you created from the user's input.

[NoobPHP]

kburger.. thanks for the reply.. i got that solved.. But the other thing is..

Why is it when i click next or page 2 or page 3.. the values are all gone.. What is wrong with it?

the address bar went from this - PAGE1

http://localhost/searchCriteria4.php?AircraftType=AS-332&ServicingType=&day=-&month=-&year=&day1=-&month1=-&year1=&YearOfOccurence=&AirFrameHours=&AirFrameHours2=&Keyword=&Submit=Search

to this - PAGE2

http://localhost/searchCriteria4.php?page=2

This it the query that was displayed
SELECT * from report where reportID != 0couldn't execute query You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0, 10' at line 1

// how many rows to show per page 
$rowsPerPage = 10; 

// by default we show first page 
$pageNum = 1; 

// if $_GET['page'] defined, use it as page number 
if(isset($_GET['search'])) 
{ 
$pageNum = $_GET['search']; 
} 

// counting the offset 
$offset = ($pageNum - 1) * $rowsPerPage; 

// optional search query  

$queryAircraftDisplay = "SELECT * from report where reportID != 0"; 
if (!empty($_GET['AircraftType'])) 
{ 
$queryAircraftDisplay  .= " and aircraftType='".$_GET['AircraftType']."' "; 
} 
if (!empty($_GET['ServicingType'])) 
{ 
$queryAircraftDisplay  .= " and servicingType='".$_GET['ServicingType']."' "; 
} 
if (!empty($_GET['YearOfOccurence'])) 
{ 
$queryAircraftDisplay  .= " and Year(DateOfOccurence) ='".$_GET['YearOfOccurence']."' "; 
} 
echo $queryAircraftDisplay; 
$queryAircraftDisplay .="LIMIT $offset, $rowsPerPage"; 

$resultAircraftDisplay = mysql_query($queryAircraftDisplay) or die ("couldn't execute query ".mysql_error()); 

// how many rows we have in database 
$queryAircraftDisplay = "SELECT COUNT(*) AS numrows from report where reportID != 0"; 
if (!empty($_GET['AircraftType'])) 
{ 
$queryAircraftDisplay  .= " and aircraftType='".$_GET['AircraftType']."' "; 
} 
if (!empty($_GET['ServicingType'])) 
{ 
$queryAircraftDisplay  .= " and servicingType='".$_GET['ServicingType']."' "; 
} 
if (!empty($_GET['YearOfOccurence'])) 
{ 
$queryAircraftDisplay  .= " and Year(DateOfOccurence) ='".$_GET['YearOfOccurence']."' "; 
} 

$resultAircraftDisplay1 = mysql_query($queryAircraftDisplay) or die ("couldn't execute query ".mysql_error()); 
$row     = mysql_fetch_array($resultAircraftDisplay1, MYSQL_ASSOC); 
$numrows = $row['numrows']; 

// how many pages we have when using paging? 
$maxPage = ceil($numrows/$rowsPerPage); 

// print the link to access each page 
$self = $_SERVER['PHP_SELF']; 
$nav  = ''; 

for($page = 1; $page <= $maxPage; $page++) 
{ 
if ($page == $pageNum) 
{ 
$nav .= " $page "; // no need to create a link to current page 
} 
else 
{ 
$nav .= " <a href=\"$self?page=$page\">$page</a> "; 
} 
} 

if ($pageNum > 1) 
{ 
$page  = $pageNum - 1; 
$prev  = " <a href=\"$self?page=$page\">[Prev]</a> "; 

$first = " <a href=\"$self?page=1\">[First Page]</a> "; 
} 
else 
{ 
$prev  = '&nbsp;'; // we're on page one, don't print previous link 
$first = '&nbsp;'; // nor the first page link 
} 

if ($pageNum < $maxPage) 
{ 
$page = $pageNum + 1; 
$next = " <a href=\"$self?page=$page\">[Next]</a> "; 

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> "; 
} 
else 
{ 
$next = '&nbsp;'; // we're on the last page, don't print next link 
$last = '&nbsp;'; // nor the last page link 
} 

// print the navigation link 
echo $first . $prev . $nav . $next . $last;  

[redlorry919]

You'll need to pass the variables over aswell. As they don't exist on the link off the first page then mysql is bombing out on the following pages.

i.e.

$prev  = " <a href=\"$self?day=$day&month=$month...{etc}..&page=$page\">[Prev]</a> ";  

Alternatively add them to the $self variable:

$self = $_SERVER['PHP_SELF']."?".day=$day&month=$month...{etc};  

Regards,
Adam.

[NoobPHP]

These are my changes.. I do not know which part of it is wrong..

if ($pageNum > 1)
{
$page  = $pageNum - 1;
//$prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
//AircraftType=AS-332&ServicingType=&day=-&month=-&year=&day1=-&month1=-&year1=&YearOfOccurence=&AirFrameHours=&AirFrameHours2=&Keyword
$prev  = " <a href=\"$self?"."&AircraftType=".$_GET['AircraftType']. "&ServicingType=".$_GET['ServicingType']."&AirFrameHours=".$_GET['AirFrameHours']. "&AirFrameHours2=".$_GET['AirFrameHours2']. "&YearOfOccurence=".$_GET['YearOfOccurence']. "&Keyword=".$_GET['Keyword']."&page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?"."&AircraftType=".$_GET['AircraftType']. "&ServicingType=".$_GET['ServicingType']."&AirFrameHours=".$_GET['AirFrameHours']. "&AirFrameHours2=".$_GET['AirFrameHours2']. "&YearOfOccurence=".$_GET['YearOfOccurence']. "&Keyword=".$_GET['Keyword']."page=1\">[First Page]</a> ";
}
else
{
$prev  = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?"."&AircraftType=".$_GET['AircraftType']. "&ServicingType=".$_GET['ServicingType']."&AirFrameHours=".$_GET['AirFrameHours']. "&AirFrameHours2=".$_GET['AirFrameHours2']. "&YearOfOccurence=".$_GET['YearOfOccurence']. "&Keyword=".$_GET['Keyword']."page=$page\">[Next]</a> ";

$last = " <a href=\"$self?"."&AircraftType=".$_GET['AircraftType']. "&ServicingType=".$_GET['ServicingType']."&AirFrameHours=".$_GET['AirFrameHours']. "&AirFrameHours2=".$_GET['AirFrameHours2']. "&YearOfOccurence=".$_GET['YearOfOccurence']. "&Keyword=".$_GET['Keyword']."page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}

When i click on "next" page.. it does a search on "page=2" on my search keyword textfield..

Address Bar
http://localhost/searchCriteria4.php?&AircraftType=AS-332&ServicingType=&AirFrameHours=&AirFrameHours2=&YearOfOccurence=&Keyword=?&AircraftType=AS-332&ServicingType=&AirFrameHours=&AirFrameHours2=&YearOfOccurence=&Keyword=page=2

Before i do any search there is already this error query display
SELECT * from report where 1couldn't execute query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0, 10' at line 1

[vaaaska]

I don't know why you aren't using:

LIMIT $start_no,$limit

It always works for me with pagination...with proper validation and such, of course...

[gabriel]

Sorry this is just a quick and dirty example of how I hacndle paging. Hope you can extract SOMETHING useful from all this

$getpgnumloop1 = mysql_query($getpgnumloop);
$pagecount = mysql_num_rows($getpgnumloop1);

$pgc = $pagecount / $pagelimit;
$pgc = ceil($pgc);
$j=0;
for($i = 0; $i < $pgc; $i++){
$j++;

if($j == 1){
 $ssdd = ("<select name=\"$selectname\" size=\"1\" onChange=\"javascript:formHandler('$selectname')\" style=\"position: relative\">
    <option value=\"".$basename."?".$var."=".$var1."&pgs=0\"\">1</option>");
$nextpage = $u;
$backpage = 0;
}else{
$addll = $j * $pagelimit - $pagelimit;
$addul = $pagelimit;
$get_page = $pgs / $u;
$get_page2 = ceil($get_page);
$get_page2 ++;

if($get_page2 == $j){
$ssdd .= ("<option selected value=\"".$basename."?".$var."=".$var1."&pgs=".$addll."\"\">$j</option>");
$nextpage = $addll + $u;
$backpage = $addll - $u;
}else{
$ssdd .= ("<option value=\"".$basename."?".$var."=".$var1."&pgs=".$addll."\"\">$j</option>");
}

}


}
@$ssdd .= ("</select>");
if(@$addll != $pgs){
$nexturl = ("&nbsp;&nbsp;<a href = \"".$_SERVER['PHP_SELF']."?".$var."=".$var1."&pgs=".$nextpage."\">Next &gt;</a>&nbsp;&nbsp;");
}
if($pgs != 0){
$backurl = ("&nbsp;&nbsp;<a href = \"".$_SERVER['PHP_SELF']."?".$var."=".$var1."&pgs=".$backpage."\">&lt; Back</a>&nbsp;&nbsp;");
}
if($pagecount > $pagelimit){
echo "<p align = \"right\">". @$backurl . $ssdd . @$nexturl ."</p>";
}

Then I call the pages with

$storyloop = "SELECT  * FROM $dbstory WHERE catid = $cat AND series = $subcat AND cid = 0 order by updated desc LIMIT $pgs,$u";
$storyloop1 = mysql_query($storyloop); 
while ($sl = mysql_fetch_array($storyloop1))
{
	$sid = $sl['sid'];

require "story_layout.php";


}

paging($selectname = "page1");
echo "</form>";