PHP Paging Help

[PRodgers4284]

Im have got a php paging script working, but im trying to output a message if there are no records in the database.

 

I have the following code:

 

<?php
include("database.php");
// how many rows to show per page
$rowsPerPage = 2;

// 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;

$sessid = $_SESSION["username"] ;


$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

##########

// print the random numbers
while($row = mysql_fetch_array($result))
{
$username=$row['username'];
$id=$row['id'];
?>
<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcatergory">Job Catergory:</label>  
<input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
</fieldset> 
<fieldset>  
<table border="0" align=right width="40%" id="table14">
<tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
</td>
</tr>
</table>
</fieldset> 
</form> 
<?php
}
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, 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> ";
}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

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

 

 

 

 

 

The code im trying to add after the sql query and before the while loop marked as ########## in the above code, it doesnt same to work properly:

 

if(!$result){
   // check if is something wrong
   print "Error";
}else{
if(mysql_num_rows($result) == 0){  
     print "No Jobs Exist";
}else{

 

Can anyone help?

[Psycho]

it doesnt same to work properly

 

What does that mean. What does it do? The code looks OK as long as you add two closing brackets to the end of the code that displays the results. Personally, I wouldn't use nested IF statements and just use a single closing bracket after the result code, like this:

 

if(!$result){
   // check if is something wrong
   print "Error:" . mysql_error();
}else if (mysql_num_rows($result) == 0){  
   print "No Jobs Exist";
}else{

// Results code goes here

} //closing bracket

[PRodgers4284]

I have modified the code, it brings up the error message "No Jobs exist" if there are not records in the database, but the form still displays with the paging numbers below, how can i stop these appearing and just the error message. If i login as another user that has records in the database the page displays a single form which is not populated with the data , for example the user i logged in as has 3 records in the database, the page should display three forms for this user. I appreciate your help on this.

 

My Code is now:

 

<?php
// how many rows to show per page
$rowsPerPage = 2;

// 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;

$sessid = $_SESSION["username"] ;


$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

if(!$result){
   // check if is something wrong
   print "Error:" . mysql_error();
}else if (mysql_num_rows($result) == 0){  
   print "No Jobs Exist";
}else{


// print the random numbers
while($row = mysql_fetch_array($result))
{
$username=$row['username'];
$id=$row['id'];
}
?>
<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcatergory">Job Catergory:</label>  
<input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
</fieldset> 
<fieldset>  
<table border="0" align=right width="40%" id="table14">
<tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
</td>
</tr>
</table>
</fieldset> 
</form> 
<?php
}
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, 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> ";
}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

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

?>

 

[roxki]

<?php
// how many rows to show per page
$rowsPerPage = 2;

// 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;

$sessid = $_SESSION["username"] ;


$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

if(!$result){
   // check if is something wrong
   die("Error:" . mysql_error());
}

if(!mysql_num_rows($result)){  
   print "No Jobs Exist";
}else{


// print the random numbers
while($row = mysql_fetch_array($result))
{
$username=$row['username'];
$id=$row['id'];
}
?>
<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcatergory">Job Catergory:</label>  
<input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
</fieldset> 
<fieldset>  
<table border="0" align=right width="40%" id="table14">
<tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
</td>
</tr>
</table>
</fieldset> 
</form> 
<?php
}
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, 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> ";
}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

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

?>

 

Try the code above, it should only return the message "No Jobs Exist" if there're no records in the MySQL table. If there're just a single record in the MySQL table the forms should be shown.

[PRodgers4284]

Ok i have managed to stop the form displayin if there are no records on the database, but it still output the paging numbers, it seems to be still counting the all the rows in the database.

 

Code is now:

 

<?php
// how many rows to show per page
$rowsPerPage = 2;

// 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;

$sessid = $_SESSION["username"] ;


$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

if(!$result){
  // check if is something wrong
  print "Error:" . mysql_error();
}else if (mysql_num_rows($result) == 0){  
  print "No Jobs Exist, please select 'Add job' to add a job vacancy to the system.";
}else

// print the random numbers
while($row = mysql_fetch_array($result))
{
$username=$row['username'];
$id=$row['id'];
?>
<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcatergory">Job Catergory:</label>  
<input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
</fieldset> 
<fieldset>  
<table border="0" align=right width="40%" id="table14">
<tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
</td>
</tr>
</table>
</fieldset> 
</form> 
<?php
}
echo '<br>';
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, 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> ";
}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

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

?>

[roxki]

Yes the code you've shown does NOT prevent the page from showing the Page counter. If you want that you'll have to put the page counting scripts within the brackets which checks if there're any records in the MySQL tabel.

Try this code:

<?php
// how many rows to show per page
$rowsPerPage = 2;

// 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;

$sessid = $_SESSION["username"] ;


$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

if(!$result){
   // check if is something wrong
   print "Error:" . mysql_error();
}else if (mysql_num_rows($result) == 0){  
   print "No Jobs Exist, please select 'Add job' to add a job vacancy to the system.";
}else

// print the random numbers
while($row = mysql_fetch_array($result))
{
$username=$row['username'];
$id=$row['id'];
?>
<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcatergory">Job Catergory:</label>  
<input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
</fieldset> 
<fieldset>  
<table border="0" align=right width="40%" id="table14">
<tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
</td>
</tr>
</table>
</fieldset> 
</form> 
<?php
echo '<br>';
echo '<br>';

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, 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> ";
}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

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  = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}

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

}
?>

 

Hope it is as you want it now.

[Psycho]

...but the form still displays with the paging numbers below, how can i stop these appearing and just the error message.

 

Your original post didn't mention anything about that!

 

I see several problems in the above code:

 

1. The final "else" for the logic to display the records if there are any is not enclosed withing opening and closing brackets, so even when there are 0 records that code will run

 

2. The code for determining the records to display includes a WHERE clause, but the code to determine the max page count does not. So, there will be page links for ALL the records, not just the records in the result set.

 

3. There is no validation for "user" input - e.g. the page number. The code would error if the user put an invalid value on the query string. Or, worse, the user could use SQL inqection to cause serious problems.

 

Here is a quick rewrite (may be some syntax errors, since I didn't test):

<?php

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

//Get user ID
$sessid = $_SESSION["username"] ;

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM job  WHERE username='" . $sessid. "'";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_assoc($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

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

// determine the current page number
// if $_GET['page'] defined, use it as page number
// NEED TO VALIDATE THAT $_GET['page'] IS A VALID NUMBER
$pageNum = 1; //Default value
if(isset($_GET['page']) && $_GET['page']>=1 && $_GET['page']<=$maxPage)
{
$pageNum = $_GET['page'];
}

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

// run the query
$query  = "SELECT * FROM job WHERE username='" . $sessid. "' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

//Determine if there are results to display
if(!$result)
{
   // check if is something wrong
   print "Error:" . mysql_error();
}
else if (mysql_num_rows($result) == 0)
{
   print "No Jobs Exist, please select 'Add job' to add a job vacancy to the system.";
}
else
{

   // print the random numbers
   while($row = mysql_fetch_array($result))
   {
       $username=$row['username'];
       $id=$row['id'];
?>
   <form class="jobform" action="">  
   <fieldset>  
       <p class="edit"> 
   </fieldset> 
   <fieldset>  
       <label for="jobtitle">Job Title:</label>  
       <input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $row["jobtitle"]; ?>" /><br />  
   </fieldset>
   <fieldset style="width: 602; height: 58">  
       <label for="jobcatergory">Job Catergory:</label>  
       <input readonly name="jobcatergory" type="text" id="jobcatergory" value="<?php echo $row["jobcatergory"]; ?>" />  
   </fieldset> 
   <fieldset>  
       <table border="0" align=right width="40%" id="table14">
    <tr>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='editjob.php?username=$username&id=$id'>Edit/Update Job</a>"?></p></td>
	<td align="right"><span class="navyboldtxt"><p align="right"><?php echo "<a href='deletejob.php?username=$username&id=$id'>Delete Job</a>"?></p></td>
    </tr>
       </table>
   </fieldset> 
   </form> 
<?php
   } // end while loop

   echo '<br>';
   echo '<br>';

   // 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> ";
       }		
   }

   // creating previous and next link
   // plus the link to go straight to
   // the first and last page

   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  = ' '; // we're on page one, don't print previous link
       $first = ' '; // 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 = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
   }

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

}

?>