Pagination is old and so is my code...

LTCreations · January 13, 2010

After many years of reading posts on pagination I'm finally asking for help as opposed to trying to figure this out myself. The following code has been in my site since it went live and is supposed to limit the information to 10 lines per page. Currently, the information is all displayed on one page, and, there since the lines have grown large enough there are additional page numbers but no other pages. I mean there is page one link and page 2 link, but no actual pages other than the one.

<?

$size=$conf_admin_page_size;

$start=0;

$page=0;

$pagecount=0;

$pagestring="Move To ";

if (isset($_GET["page"])) $page=intval($_GET["page"],10);

$rsp=mysql_query("Select count(*) rcount from tbl_users u where u.mem_is_active=1 and u.mem_is_deleted=0");

$rowp=mysql_fetch_array($rsp);

$pagecount=ceil($rowp["rcount"]/$size);

$page=$page>0 && $pagecount>=$page?$page:1;

$start=($page-1)*$size;

for ($i=1;$i<=$pagecount;$i++)

{

if ($page==$i)

$pagestring.=" <a style='color:#FF0000;' href='manageusers.php?page=$i'>$i</a>";

else

$pagestring.=" <a style='color:#000000;' href='manageusers.php?page=$i'>$i</a>";

}

?>

Somewhere in that code there is something wrong because the page doesn't break at line 10 and create page 2, etc.

Thanks.

jeremy0 · January 13, 2010

would look at the code for manageusers.php. Should just be a simple database query to retrieve the nth-nth rows of data for a particular page.. also, you'll need to escape these strings:

$pagestring.=" <a style='color:#FF0000;' href='manageusers.php?page=$i'>$i</a>"; (to)

$pagestring.=" <a style='color:#ff0000;' href='manageusers.php?page=".$i."'>".$i."</a>";

JAY6390 · January 13, 2010

OK, download my pagination class from my website http://www.jaygilford.com/php/completely-customisable-php-pagination-class/ to the same folder as this file named pagination.class.php, and then use this code

<?php

include('pagination.class.php');
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$query = "Select COUNT(*) rcount FROM tbl_users u WHERE u.mem_is_active = 1 AND u.mem_is_deleted = 0";

$paginator = new pagination($page, $query);
$paginator->results_per_page = $conf_admin_page_size;;
$paginator->padding(3);
$paginator->link_prefix = 'manageusers.php?page=';
$paginator->link_suffix = '';
$paginator->page_nums_separator = ' ';

$links = $paginator->paginate();


echo  "Move To " . $links;

while($row = mysql_fetch_assoc($paginator->resource())) {
    //Do whatever you want to with the results here
    echo '<pre>'.print_r($row, true).'</pre>';
}

LTCreations · January 13, 2010

would look at the code for manageusers.php. Should just be a simple database query to retrieve the nth-nth rows of data for a particular page.. also, you'll need to escape these strings:

$pagestring.=" <a style='color:#FF0000;' href='manageusers.php?page=$i'>$i</a>"; (to)

$pagestring.=" <a style='color:#ff0000;' href='manageusers.php?page=".$i."'>".$i."</a>";

Here is the entire page...

<?

include("header.php");

$type_index=0;

$keyword="";

$sqlfilter="";

?>

<tr>

<?

include ("links.php");

?></td><td>

<tr>

<tr>

<td width="100%" align="left" valign="middle"><span class="h1_black"><span class="h1_orange">MANAGE</span> USERS<br />

Manage Members</span><br />

</td>

</tr>

<tr>

<?

$strheadline="Add New Member";

$p=1;

if(isset($_GET["page"]))$p=$_GET["page"];

if(isset($_POST["page"]))$p=$_POST["page"];

$sel_state="";

$action="";

$usr_chk="";

$msg="";

if(isset($_GET["msg"]))$msg=$_GET["msg"];

$msge="";

if(isset($_GET["msge"]))$msge=$_GET["msge"];

$sqlqry="";

$id=0;

$id="";

if (isset($_GET["mode"]) && isset($_GET["id"]))

{

$id=$_GET["id"];

$action=strtoupper($_GET["mode"]);

switch ($action)

{

case "DELETE":

mysql_query("Update tbl_users set

mem_is_active=0,

mem_is_deleted=1

where mem_id='$id'")or die(mysql_error());

mysql_query("update tbl_listings set listing_is_deleted=1 where listing_mem_id ='$id'");

$msg="User Information Deleted!";

$id=0;

break;

}

?>

function showHide(id)

{

if (document.getElementById(id).style.display=='')

document.getElementById(id).style.display='none';

else

document.getElementById(id).style.display='';

}

</script>

<?

if($msg!="")

{

?>

<tr>

</tr>

<tr>

</tr>

<?

}

?>

<tr>

<td>

<?

$size=$conf_admin_page_size;

$start=0;

$page=0;

$pagecount=0;

$pagestring="Move To ";

if (isset($_GET["page"])) $page=intval($_GET["page"],10);

$rsp=mysql_query("Select count(*) rcount from tbl_users u where u.mem_is_active=1 and u.mem_is_deleted=0");

$rowp=mysql_fetch_array($rsp);

$pagecount=ceil($rowp["rcount"]/$size);

$page=$page>0 && $pagecount>=$page?$page:1;

$start=($page-1)*$size;

for ($i=1;$i<=$pagecount;$i++)

{

if ($page==$i)

$pagestring.=" <a style='color:#FF0000;' href='manageusers.php?page=$i'>$i</a>";

else

$pagestring.=" <a style='color:#000000;' href='manageusers.php?page=$i'>$i</a>";

}

?>

</tr>

<tr >

</tr>

<tr>

<td width="28%" ><strong>Email</strong></td>

<td width="20%" ><strong>Contact Information </strong></td>

<td width="6%" ><strong>Listings</strong></td>

</tr>

<tr >

</tr>

<?

$rs=mysql_query($sqlQuery="Select u.*,count(L.listing_id) as listings from tbl_users u

left join tbl_listings L on L.`listing_mem_id`=u.`mem_id` and L.listing_is_deleted=0

where u.mem_is_deleted=0

group by u.`mem_id` order by u.mem_id desc");

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

?>

<tr>

</td>

<?

if ($row["mem_is_admin"] != 1)

{

?>

<a href="manageusers.php?mode=DELETE&id=<?=$row["mem_id"]?>"><img title="Delete User" alt="Delete User" style="cursor:pointer;cursor:hand;" src="images/delete.gif" border="0" ></a><?

}

?>

</td>

</tr>

<tr>

</tr>

<?

}

?>

</tr>

document.getElementById("TRHEADERPAGING").innerHTML="<?=$pagestring;?>";

</script>

</table>

</form></td>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table>

</td></tr>

<?

include("footer.php");

?>

jeremy0 · January 13, 2010

from what I can see just by browsing around, you're not doing anything with the variable $p in your page besides setting it from your $_GET/$_POST parameters. That's also the variable you are assigning when the user submits one of the page options (?page=1, ?page=2...)

LTCreations · January 13, 2010

from what I can see just by browsing around, you're not doing anything with the variable $p in your page besides setting it from your $_GET/$_POST parameters. That's also the variable you are assigning when the user submits one of the page options (?page=1, ?page=2...)

I "threw in" the $p=1; just to see if it would make any difference because I have another page, managestates.php, that works perfectly using very similar code...but I don't know enough of php to figure out what little tweak is different that makes one work and the other not.

Managestates.has has every state in the US listed, breaks at 50, and works great. What's next?

jeremy0 · January 13, 2010

$rs=mysql_query($sqlQuery="Select u.*,count(L.listing_id) as listings from tbl_users u

left join tbl_listings L on L.`listing_mem_id`=u.`mem_id` and L.listing_is_deleted=0

where u.mem_is_deleted=0 and rownum >= ".$p." and rownum < ".$p+50."

group by u.`mem_id` order by u.mem_id desc");

LTCreations · January 13, 2010

$rs=mysql_query($sqlQuery="Select u.*,count(L.listing_id) as listings from tbl_users u

left join tbl_listings L on L.`listing_mem_id`=u.`mem_id` and L.listing_is_deleted=0

where u.mem_is_deleted=0 and rownum >= ".$p." and rownum < ".$p+50."

group by u.`mem_id` order by u.mem_id desc");

That gave me an error...

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/citydirectory/domains/citydirectoryonline.com/public_html/manager/manageusers.php on line 142

I see where you're going, I think. The line wasn't completed to tell the page the remaining information.

Where do I put a quote or semi colon (or ?) to allow the code to parse correctly?

jeremy0 · January 13, 2010

sorry about the syntax error - basically what I'm saying is use the variable $p to indicate a range of output from the database query:

e.g.

say $p = 2:

$start = ($p-1)*50; //should give 50 for page 2, 100 for page 3, 150 for page 4, etc.

if ($start == 0) { $start = 1; } //to set $start to 1 for page 1

$end = $p*50; //should give 50 for page 1, 100 for page 2, etc.

//should result in displaying 1-49, 50-99, 100-149, etc. depending on $p (or ?page)

rownum >= $start and rownum < $end...

LTCreations · January 13, 2010

sorry about the syntax error - basically what I'm saying is use the variable $p to indicate a range of output from the database query:

e.g.

say $p = 2:

$start = ($p-1)*50; //should give 50 for page 2, 100 for page 3, 150 for page 4, etc.

if ($start == 0) { $start = 1; } //to set $start to 1 for page 1

$end = $p*50; //should give 50 for page 1, 100 for page 2, etc.

//should result in displaying 1-49, 50-99, 100-149, etc. depending on $p (or ?page)

rownum >= $start and rownum < $end...

I get the idea, but as much as I can read php I don't know how to "speak" php. So I'm confused as to how to get the script to have the correct calls.

To me, the one page works and the other doesn't tells me that there must be one little forgotten bit of code. But "I" don't know that missing piece.

Here is the page that works with 50 rows (and like you suggest, it uses the $p, but I don't know how it's referenced);

<?php

require("header.php");

$_SESSION["timestamp"]=(isset($_SESSION["timestamp"])==true)?$_SESSION["timestamp"]:"";

?>

/*function check()

{

window.opener.location.href=window.opener.location.href;

}

window.onload=check();

*/</script>

</td></tr>

<tr>

<?

include ("links.php");

?></td><td>

<tr>

MANAGE</span> STATES </span><br />

Manage States</td>

</tr>

<tr>

<?php

$strheadline="Add New State";

$p=1;

if(isset($_GET["page"]))$p=$_GET["page"];

if(isset($_POST["page"]))$p=$_POST["page"];

$sqlqry="";

$id=0;

$status="";

$mode="";

$msg="";

if(isset($_GET["msg"]))$msg=$_GET["msg"];

$newmode="ADD";

$loc_id="";

$loc_name="";

$loc_code="";

$state_label="";

$set_step="";

if (isset($_GET["order"])) $order=$_GET["order"];

if (isset($_GET["id"])) $id=intval($_GET["id"],10);

if (isset($_POST["id"]) && $id==0) $id=intval($_POST["id"],10);

if (isset($_GET["status"])) $status=intval($_GET["status"],10);

if (isset($_GET["mode"])) $mode=$_GET["mode"];

if (isset($_POST["mode"]) && $mode=="") $mode=$_POST["mode"];

switch(strtoupper($mode))

{

case "ADD":

if ($_SERVER['REQUEST_METHOD']=="POST")

{

$loc_name=$_POST["loc_name"];

$loc_id=$_POST["id"];

if($_SESSION["timestamp"]!=$_POST["timestamp"])

{

$_SESSION["timestamp"]=$_POST["timestamp"];

if($_POST["user_type"]==0)

{

mysql_query("insert into tbl_location set loc_name='$loc_name'");

$loc_id=mysql_insert_id();

mysql_query("update tbl_location set loc_code=right(concat('00000',$loc_id),5) where loc_id='$loc_id'");

}

else if($_POST["user_type"]==1)

{

copy($_FILES["file"]['tmp_name'],"uploads/states.csv");

$handle = fopen("uploads/states.csv", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

{

foreach ($data as $value)

{

if($value!="")

{

mysql_query("insert into tbl_location set loc_name='".trim($value)."'");

$loc_id=mysql_insert_id();

mysql_query("update tbl_location set loc_code=right(concat('00000',$loc_id),5) where loc_id='$loc_id'");

}

fclose($handle);

}

$msg="Added Successful!";

header("location:managestates.php?msg=$msg");

}

else

{

$msg="Invalid Request to Add Record!";

}

break;

case "EDIT":

if ($_SERVER['REQUEST_METHOD']=="POST")

{

$loc_name=$_POST["loc_name"];

$loc_id=$_POST["id"];

mysql_query("update tbl_location

set loc_name='$loc_name'

where loc_id='$loc_id'");

$msg="Update successful!";

}

else

{

$rs=mysql_query("select * from tbl_location where loc_id='$id'");

if ($row=mysql_fetch_array($rs))

{

$loc_name=$row["loc_name"];

$loc_id=$row["loc_id"];

}

$msg="Change the values and Submit again to update the changes.";

}

$newmode="EDIT";

$strheadline="Update State [<a href='managestates.php'>Cancel</a>]";

break;

case "DELETE":

mysql_query("delete from tbl_location where loc_id=$id");

$msg="Deleted Successfully!<meta http-equiv=REFRESH content='2;url=managestates.php'>";

break;

}

?>

function showHide(id)

{

if (document.getElementById(id).style.display=='')

document.getElementById(id).style.display='none';

else

document.getElementById(id).style.display='';

}

</script>

//-->

</script>

<tr>

</tr>

<tr>

<?=$strheadline;?>

</legend>

</tr>

<td rowspan="2" valign="top" bgcolor="#fafafa"><? if($newmode=='ADD') echo'<a href="#" onclick="showdiv2()">Browse File</a>'; ?></td>

</tr>

</tr>

<tr>

</tr>

</tr>

</table>

</div> </td>

</tr>

</tr>

</table>

</fieldset>

</form></td>

</tr>

<tr>

<td>

<?

$size=$conf_admin_page_size;

$start=0;

$page=0;

$pagecount=0;

$pagestring="Move To ";

if (isset($_GET["page"])) $page=intval($_GET["page"],10);

$rsp=mysql_query("Select count(loc_id) as rcount from tbl_location where length(loc_code)=5 ");

$rowp=mysql_fetch_array($rsp);

$pagecount=ceil($rowp["rcount"]/$size);

$page=$page>0 && $pagecount>=$page?$page:1;

$start=($page-1)*$size;

for ($i=1;$i<=$pagecount;$i++)

{

if ($page==$i)

$pagestring.=" <a style='color:#FF0000;' href='managestates.php?page=$i'>$i</a>";

else

$pagestring.=" <a style='color:#000000;' href='managestates.php?page=$i'>$i</a>";

}

$rs=mysql_query("Select c.loc_id,c.loc_code,c.loc_name,count(l.loc_id) as loc_count

from tbl_location c left outer join tbl_location l on left(l.loc_code,5)=c.loc_code and length(l.loc_code)>5

where length(c.loc_code)=5

group by c.loc_id,c.loc_name

order by c.loc_name ASC limit $start,$size");

?>

<br />

</tr>

</tr>

<tr >

</tr>

<?

while ($row=mysql_fetch_array($rs))

{

?>

<tr >

<a href="managestates.php?page=<?=$p?>&mode=DELETE&id=<?=$row["loc_id"]?>" onClick="return confirm('Do you really want to delete this Record?');">

<?if ($row["loc_count"]<=0) {?><img src="images/delete.gif" title="Delete Country" alt="Delete Country" border="0" /><?}?></a> </td>

</tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<?

}

?>

</tr>

document.getElementById("TRHEADERPAGING").innerHTML="<?=$pagestring;?>";

</script>

</table> </form></td>

</tr>

<tr>

</tr>

<tr>

</tr>

</table></td>

</tr> </table>

</td></tr>

</table></td></tr>

<?php

include("footer.php");

?>

function showdiv2()

{

document.getElementById("div2").style.display="";

document.getElementById("user_type").value=1;

}

function checkform()

{

if(document.getElementById("user_type").value==0)

{

if(document.getElementById("loc_name").value=="")

{

alert("Please Enter State Name");

document.getElementById("loc_name").focus();

}

else

document.form1.submit();

}

else

{

if(document.getElementById("file").value=="")

{

alert("Please Enter File Name");

document.getElementById("file").focus();

}

else

document.form1.submit();

}

</script>

jeremy0 · January 13, 2010

In your page that works, the query:

$rs=mysql_query("Select c.loc_id,c.loc_code,c.loc_name,count(l.loc_id) as loc_count
      from tbl_location c left outer join tbl_location l on left(l.loc_code,5)=c.loc_code and  length(l.loc_code)>5
      where length(c.loc_code)=5
      group by c.loc_id,c.loc_name
      order by c.loc_name  ASC limit $start,$size

has a limit on it that uses the results of $_POST['page'] to affect the output returned by the database. So, similarly you can do this on your page that doesn't work:

$rs=mysql_query($sqlQuery="Select u.*,count(L.listing_id) as listings from tbl_users u
                                 left join tbl_listings L on L.`listing_mem_id`=u.`mem_id` and L.listing_is_deleted=0
                           where u.mem_is_deleted=0
                            group by u.`mem_id` order by u.mem_id desc limit $start,$size");

LTCreations · January 13, 2010

In your page that works, the query:

$rs=mysql_query("Select c.loc_id,c.loc_code,c.loc_name,count(l.loc_id) as loc_count
      from tbl_location c left outer join tbl_location l on left(l.loc_code,5)=c.loc_code and  length(l.loc_code)>5
      where length(c.loc_code)=5
      group by c.loc_id,c.loc_name
      order by c.loc_name  ASC limit $start,$size

has a limit on it that uses the results of $_POST['page'] to affect the output returned by the database. So, similarly you can do this on your page that doesn't work:

$rs=mysql_query($sqlQuery="Select u.*,count(L.listing_id) as listings from tbl_users u
                                 left join tbl_listings L on L.`listing_mem_id`=u.`mem_id` and L.listing_is_deleted=0
                           where u.mem_is_deleted=0
                            group by u.`mem_id` order by u.mem_id desc limit $start,$size");

That was "too" simple....just like I figured. The only thing left is for me to understand where I tell the page to break at 10, or 20, instead of 50.

I thought it didn't work at first. I added the code, tested the page and saw so many entries that I was sure it didn't work. Then I clicked on "2" to go to page 2 and voila - it works.

Any last words of wisdom where I can alter the rows? I don't see anything that relates to 50 rows as one page. Any thoughts? And, by the way, thank you for the help. If I never figure out how to get the rows down to a more manageable number it was worth it to find how to at least get to 50. The site and entries will, eventually, be in the millions.

Thanks again.

jeremy0 · January 13, 2010

adjusting the variable $conf_admin_page_size should do the trick, but it looks like that would change the number of results for both of your pages (I think that variable is in an include file like 'header.php')

I would find where the variable $conf_admin_page_size is declared at, and replace it with $conf_admin_results_size and set that to 20, 30, 50, or whatever you want - that way it only affects the page we were working on.

[correction] add the variable $conf_admin_results_size to 'header.php' and replace $conf_admin_page_size with that on the page you want to bring down to 20 results. Setting that variable to 20 is what to do.

And, no problem with the help - it was my pleasure.

LTCreations · January 13, 2010

adjusting the variable $conf_admin_page_size should do the trick, but it looks like that would change the number of results for both of your pages (I think that variable is in an include file like 'header.php')

I would find where the variable $conf_admin_page_size is declared at, and replace it with $conf_admin_results_size and set that to 20, 30, 50, or whatever you want - that way it only affects the page we were working on.

[correction] add the variable $conf_admin_results_size to 'header.php' and replace $conf_admin_page_size with that on the page you want to bring down to 20 results. Setting that variable to 20 is what to do.

And, no problem with the help - it was my pleasure.

Perfect! I found where the "page" was declared, added "results", changed "page" to "results" in the manageusers.php code and there are now 20 lines per page - and showing 4 pages. Cool. I've been wanting to get that cleaned up for more than a year.

Thanks again.

Sign In

Pagination is old and so is my code...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information