Jump to content

Need help with pagination

irti_h

By irti_h
in PHP Coding Help

 Share

Recommended Posts

irti_h Newbie

irti_h

Posted
Posted

Hi I wanted to implement pagination on a database driven website...Here is the code I wrote....I don't know whats wrong..Can anyone please help me with this....

 

 

<div style="text-align:left;margin-left:14px;">

<div class="crumb"><a href="index.php">Home - </a><b>Doctor's Profiles</b></div>

<div class="text"><b>Choose Department:</b></div><br/>

<table><tr>

<td style="vertical-align:top;">

 

<form action="profiles.php" method="post">

<select name="dept">

<option value="0">All Doctors</option>

<option value="1">Internal Medicine</option>

<option value="2">Cardiology</option>

<option value="3">Pediatrics</option>

<option value="4">General Surgery</option>

<option value="5">Orthopedics</option>

<option value="6">Obstetrics & Gynecology</option>

<option value="7">Anesthesiology & Intensive Care</option>

<option value="8">Accident & Emergency</option>

<option value="9">Ophthalmology</option>

<option value="10">Dermatology</option>

<option value="11">Otolaryngology (ENT)</option>

<option value="12">Dental</option>

<option value="13">Pathology</option>

<option value="14">Radiology & Imaging</option>

<option value="15">Rehabitilation & Physiotherapy</option>

</select>

<input type="submit" value="Sort"/>

</form><br/>

<?php

include("includes/db.php");

mysql_connect($host,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

 

if ($dept != "" and $dept == "0")

{

$srch = "Department";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

if ($dept != "" and $dept == "1")

{

$srch = "Internal Medicine";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "2")

{

$srch = "Cardiology";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "3")

{

$srch = "Pediatrics";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "4")

{

$srch = "General Surgery";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "5")

{

$srch = "Orthopedics";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "6")

{

$srch = "Obstetrics & Gynecology";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "7")

{

$srch = "Anesthesiology & Intensive Care";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "8")

{

$srch = "Accident & Emergency";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "9")

{

$srch = "Ophthalmology";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "10")

{

$srch = "Dermatology";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "11")

{

$srch = "Otolaryngology (ENT)";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "12")

{

$srch = "Dental";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "13")

{

$srch = "Pathology";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "14")

{

$srch = "Radiology & Imaging";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "15")

{

$srch = "Rehabitilation & Physiotherapy";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else if ($dept != "" and $dept == "16")

{

$srch = "Orthopedics";

$query2  = "SELECT * FROM articles WHERE cat='2' and descr LIKE

'%$srch%' ORDER by id DESC";

}

else

{

 

 

 

 

 

/*

  First get total number of rows in data table.

  If you have a WHERE clause in your query, make sure you mirror it here.

*/

$query3 = "SELECT COUNT(*) FROM articles WHERE cat='2'";

$total_pages = mysql_fetch_array(mysql_query($query3));

$total_pages = $total_pages[num];

 

// How many adjacent pages should be shown on each side?

$adjacents = 3;

 

/* Setup vars for query. */

$targetpage = "profile1.php"; //your file name  $limit = 10; //how many items to show per page

$page = $_GET['page'];

if($page)

$start = ($page - 1) * $limit; //first item to display on this page

else

$start = 0; //if no page var is given, set start to 0

 

/* Get data. */

$query2 = "SELECT * FROM articles WHERE cat='2' ORDER by id ASC LIMIT $start, $limit";

 

 

/* Setup page vars for display. */

if ($page == 0) $page = 1; //if no page var is given, default to 1.

$prev = $page - 1; //previous page is page - 1

$next = $page + 1; //next page is page + 1

$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.

$lpm1 = $lastpage - 1; //last page minus 1

 

/*

Now we apply our rules and draw the pagination object.

We're actually saving the code to a variable in case we want to draw it more than once.

*/

$pagination = "";

if($lastpage > 1)

{

$pagination .= "<div class=\"pagination\">";

//previous button

if ($page > 1)

$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";

else

$pagination.= "<span class=\"disabled\">« previous</span>";

 

//pages

if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up

{

for ($counter = 1; $counter <= $lastpage; $counter++)

{

if ($counter == $page)

$pagination.= "<span class=\"current\">$counter</span>";

else

$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";

}

}

elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some

{

//close to beginning; only hide later pages

if($page < 1 + ($adjacents * 2))

{

for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)

{

if ($counter == $page)

$pagination.= "<span class=\"current\">$counter</span>";

else

$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";

}

$pagination.= "...";

$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";

$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";

}

//in middle; hide some front and some back

elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))

{

$pagination.= "<a href=\"$targetpage?page=1\">1</a>";

$pagination.= "<a href=\"$targetpage?page=2\">2</a>";

$pagination.= "...";

for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)

{

if ($counter == $page)

$pagination.= "<span class=\"current\">$counter</span>";

else

$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";

}

$pagination.= "...";

$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";

$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";

}

//close to end; only hide early pages

else

{

$pagination.= "<a href=\"$targetpage?page=1\">1</a>";

$pagination.= "<a href=\"$targetpage?page=2\">2</a>";

$pagination.= "...";

for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)

{

if ($counter == $page)

$pagination.= "<span class=\"current\">$counter</span>";

else

$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";

}

}

}

 

//next button

if ($page < $counter - 1)

$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";

else

$pagination.= "<span class=\"disabled\">next »</span>";

$pagination.= "</div>\n";

}

?>

 

<?php

$result2 = mysql_query($query2);

mysql_close();

$ln = 0;

while ($row2 = mysql_fetch_array($result2))

{

$img = $row2['img'];

$img = stripslashes($img);

$title = $row2['title'];

$title = stripslashes($title);

$desc = $row2['descr'];

$desc = stripslashes($desc);

$desc = substr($desc,0,2500);

$id = $row2['id'];

 

 

?>

 

<?=$pagination?>

Link to comment
Share on other sites
TeddyKiller Advanced Member

TeddyKiller

Posted
Posted

Might involve syntax errors... I use this script many times but I've had to twist it slightly.

If you read through you'll see what you need to change.

 

<?php
$result = mysql_query("SELECT * FROM articles WHERE cat = '2'") or trigger_error('Query failed: '. mysql_error());
$numrows = mysql_num_rows($result);

$rowsperpage = 10;
$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['page']) && is_numeric($_GET['page'])) {
   $currentpage = (int) $_GET['page'];
} else {
   $currentpage = 1;
}

if ($currentpage > $totalpages) {
   $currentpage = $totalpages;
}
if ($currentpage < 1) {
   $currentpage = 1;
}

$offset = ($currentpage - 1) * $rowsperpage;

$result = mysql_query("SELECT * FROM articles WHERE cat = '2' LIMIT $offset, $rowsperpage") or trigger_error('Query failed: '. mysql_error());

while ($list = mysql_fetch_assoc($result)) {
      // WHAT SHOULD BE DISPLAYED. EXAMPLE;
  echo $list['name'];
}
echo 'Pages: ';

$range = 3;

if ($currentpage > 1) {
   echo " <a href='{$_SERVER['PHP_SELF']}?page=1'><<</a> ";
   $prevpage = $currentpage - 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?page=$prevpage'><</a> ";
}
else
{
   echo " <a href='{$_SERVER['PHP_SELF']}?page=1'><<</a> ";
   $prevpage = $currentpage - 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?page=1'><</a> ";
}

for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   if (($x > 0) && ($x <= $totalpages)) {
      if ($x == $currentpage) {
         echo " [<b>$x</b>] ";
      } else {
         echo " <a href='{$_SERVER['PHP_SELF']}?page=$x'>$x</a> ";
      }
   } 
}
                         
if ($currentpage != $totalpages) {
   $nextpage = $currentpage + 1;
   echo " <a href='{$_SERVER['PHP_SELF']}?page=$nextpage'>></a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>>></a> <br /><br /><br />";
}
else
{
   echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>></a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>>></a> <br /><br /><br />";
}
?>

 

Change the content within the "While" operator.

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

There are a number of things that are problems in your original code. Just trying other code that is either linked to or posted won't solve your problem if you are not even receiving the data values that you expect.

 

1) You should either build a list of the options and values from a database table or at a minimum use an array to define them, so that you can easily add new values. This will also allow you to simplify the code (any time you find yourself repeating blocks of code where only a value changes or that you must edit code in more than one place anytime a new choice is added, it is time to reconsider and optimize your code.) Doing this will also make it easier to see and find problems in your code because there will be less of it (i.e. you currently have a mismatch between what values are going into the <select> menu and what values your form processing code is using.)

 

2) Your form processing code currently does not test if the form was submitted AND your code relies on register_globals to magically populate the $dept variable from the correct $_POST['dept'] variable, so it is likely that your code won't see any value. Use the correct $_POST['dept'] variable (register_globals was turned off by default nearly 8 years ago, finally throws a depreciated error message when on in php5.3, and has been completely removed in upcoming php6.)

 

3) The last block of repeated code has an unused open else{ statement that is producing a fatal parse error and the current code is not even being executed. You should be learning php (or learning anything new in php), developing php code, and debugging php code on a development system with error_reporting set to E_ALL and display_errors set to ON in the master php.ini so that php will help you by displaying all the errors it detects. You will save a ton of time.

 

4) Doing some of the above will help you see what query you are actually forming so that doing the following will be clear -

      If you have a WHERE clause in your query, make sure you mirror it here.

 

Your pagination code (and the code that TeddyKiller posted as well) may in fact be usable, but it requires that you use the same WHERE clause to find the total number of matching rows that you use in the actual query that retrieves the data.

 

@TeddyKiller, the query in the code you posted that gets the total number of rows is extremely wasteful. It is selecting all the columns and returning all the rows in the result set, only to throw them away. You should use count(*) to only get and return a count of the matching rows, your web host well be most happy.

Link to comment
Share on other sites
TeddyKiller Advanced Member

TeddyKiller

Posted
Posted

@TeddyKiller, the query in the code you posted that gets the total number of rows is extremely wasteful. It is selecting all the columns and returning all the rows in the result set, only to throw them away. You should use count(*) to only get and return a count of the matching rows, your web host well be most happy.

 

Ah. Thanks. I've never used "count(*)" before. I assumed that was just getting the rows, and not the data.

Link to comment
Share on other sites
irti_h Newbie

irti_h

Posted
  • Author
Posted

Hi thanks a lot for the help...but the page is still coming up blank...i think its my lack of knowledge to blame...could you please have a look at the implementation of your code....thanks a lot

I have changed result to result2 as I have a kind of backend for this site and i have used query and result as variables in that so I was afraid that these both could clash..So i have changed result to result2 and query to query2....and I have replaced my while loop in the area where you pointed out......Thanks a lot once again for helping me out

 

 


<?php
$result2 = mysql_query("SELECT * FROM articles WHERE cat = '2'") or trigger_error('Query failed: '. mysql_error());
$numrows = mysql_num_rows($result);

$rowsperpage = 10;

$totalpages = ceil($numrows / $rowsperpage);

if (isset($_GET['page']) && is_numeric($_GET['page'])) {   

$currentpage = (int) $_GET['page'];} 

else 
{   
$currentpage = 1;}

if ($currentpage > $totalpages) {   
$currentpage = $totalpages;}

if ($currentpage < 1) {   
$currentpage = 1;} $offset = ($currentpage - 1) * $rowsperpage;

$result2 = mysql_query("SELECT * FROM articles WHERE cat = '2' LIMIT $offset, $rowsperpage") or trigger_error('Query failed: '. mysql_error());

while ($row2 = mysql_fetch_array($result2)) 
{      // WHAT SHOULD BE DISPLAYED. EXAMPLE;	  

$img = $row2['img'];
$img = stripslashes($img);
$title = $row2['title'];
$title = stripslashes($title);
$desc = $row2['descr'];
$desc = stripslashes($desc);
$desc = substr($desc,0,2500);
$id = $row2['id'];

}

echo 'Pages: ';
$range = 3;

if ($currentpage > 1) {   
echo " <a href='{$_SERVER['PHP_SELF']}?page=1'><<</a> ";   
$prevpage = $currentpage - 1;   
echo " <a href='{$_SERVER['PHP_SELF']}?page=$prevpage'><</a> ";}

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

$prevpage = $currentpage - 1;   

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

for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {   

if (($x > 0) && ($x <= $totalpages)) {      
if ($x == $currentpage) {         
	echo " [<b>$x</b>] ";      } 
else {         
	echo " <a href='{$_SERVER['PHP_SELF']}?page=$x'>$x</a> ";      }   } }                         
if ($currentpage != $totalpages) {   $nextpage = $currentpage + 1;   
	echo " <a href='{$_SERVER['PHP_SELF']}?page=$nextpage'>></a> ";   
	echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>>></a> <br /><br /><br />";}
else{   
	echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>></a> ";   
	echo " <a href='{$_SERVER['PHP_SELF']}?page=$totalpages'>>></a> <br /><br /><br />";} 


?>

Link to comment
Share on other sites
TeddyKiller Advanced Member

TeddyKiller

Posted
Posted 

$result2 = mysql_query("SELECT * FROM articles WHERE cat = '2'") or trigger_error('Query failed: '. mysql_error());
$numrows = mysql_num_rows($result);

 

Um.. you're turning $result into $numrows.. when $result isn't defined.

Remove the 2. (Dont add 2 onto "$result" because there is a "$result2" used futher on in the code)

Link to comment
Share on other sites
irti_h Newbie

irti_h

Posted
  • Author
Posted

@ PFMaBiSmAd

 

still a long way to go i guess...hehe...ok am gonna start by getting all those select options from a table....switch on error reporting and find out how to use arrays......still if only i could get through this pagination............

Link to comment
Share on other sites
PFMaBiSmAd Advanced Member

PFMaBiSmAd

Posted
Posted

I recommend getting your page to work first without any pagination (a blank page usually indicates a fatal parse error due to bad php syntax.)

 

Get your form to submit the data you want and the page to retrieve and display the data (even if it is all of the data.) You can then add the pagination.

 

Here is an outline showing some of the things I mentioned (this also shows using count(*) to get the number of rows) -

 

<?php
include("includes/db.php");
mysql_connect($host,$username,$password) or die("Unable to connect to database server");
mysql_select_db($database) or die("Unable to select database");

// get or produce a list of the option values (ether a DISTINCT query, a specific table with the value/option, or an array)
// for demo purposes an array is used -
$options = array();
$options[0] = "Department";
$options[1] = "Internal Medicine";
$options[2] = "Cardiology";
$options[3] = "Pediatrics";
$options[4] = "General Surgery";
$options[5] = "Orthopedics";
$options[6] = "Obstetrics & Gynecology";
$options[7] = "Anesthesiology & Intensive Care";
$options[8] = "Accident & Emergency";
$options[9] = "Ophthalmology";
$options[10] = "Dermatology";
$options[11] = "Otolaryngology (ENT)";
$options[12] = "Dental";
$options[13] = "Pathology";
$options[14] = "Radiology & Imaging";
$options[15] = "Rehabitilation & Physiotherapy";
$options[16] = "Orthopedics";
?>
<div style="text-align:left;margin-left:14px;">
<div class="crumb"><a href="index.php">Home - </a><b>Doctor's Profiles</b></div>
<div class="text"><b>Choose Department:</b></div><br/>
<table><tr>
<td style="vertical-align:top;">

<form action="profiles.php" method="post">
<select name="dept">
<?php
foreach($options as $key => $value){
if($key == 0){
	// special handling of the zero'th entry
	echo "<option value=\"0\">All Doctors</option>\n";
} else {
	echo "<option value=\"$key\">$value</option>\n";	
}
}
?>
</select>
<input type="submit" name="submit" value="Sort"/>
</form><br/>
<?php
// check if the form was submitted -
if(isset($_POST['submit'])){
$errors = array(); // array to hold validation errors
// condition and validate the form data -
$_POST['dept'] = isset($_POST['dept']) && is_numeric($_POST['dept']) ? (int)$_POST['dept'] : NULL;
if(!is_numeric($_POST['dept'])){
// a number was not provided
	$errors[] = "The form data is not valid<br />";
}
// check if the choice exists
if(isset($options[$_POST['dept']])){
	// found the index value, get the corresponding string
	$srch = $options[$_POST['dept']];
} else {
	$errors[] = "The supplid Department is not an available choice<br />";
}

// process the form data if no errors -
if(empty($errors)){
	$table_name = "articles";
	$where_clause = "WHERE cat='2' and descr LIKE '%$srch%'";
	$count_query = "SELECT COUNT(*) as num FROM $table_name $where_clause";
	$base_query = "SELECT * FROM $table_name $where_clause ORDER by id DESC"; // the pagination will add a LIMIT clause to the end of this

	// put your code here for the pagination, the actual query, and the presentation code that displays the results

	/*
		First get total number of rows in data table.
		If you have a WHERE clause in your query, make sure you mirror it here.
	*/
	$result = mysql_query($count_query) or die(mysql_error());
	$row = mysql_fetch_assoc($result);
	$numrows = $row['num'];

//.... pagination code to produce links ...
   
	// the actual query that gets the data for the page (the pagination produces the $offset and $rowsperpage)
	$query = "$base_query LIMIT $offset, $rowsperpage";

//.... code to display the results of the query and to display the pagination links ...
   
} // end of no errors
} // end of form was submitted

// display any validation errors -
if(!empty($errors)){
echo "The following errors occured -<br />";
foreach($errors as $error){
	echo "$error<br />";
}
}
?>

 

 

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.