...

View Full Version : Get next ID / Better way of doing paging



hinch
08-12-2008, 01:09 PM
So I wrote my own page system as shown in the code below but its having a few problems.

Mostly the problem is that to find the next ID in the database i increment by 1 each time until I get a hit. But due to my lack of PHP knowledge I do it by posting back to its self over and over until it hits. This is causing a few problems with modern browsers who class the page as an infiinite redirect and as such time the page out.

What I've been trying and failing to do is to work out a better way of finding the next entry ID but because it uses a different sql string depending on the user and the section they're in I'm failing to work it out.

Anyone that can point me in the right direction would be a star. Hope the block of code below makes sense :)



<?php
// get configuration
set_time_limit(30);
include "includes/config.php";
session_start();
//userid session fields
//userid,groupid,isadmin,isoffice
$sessionArr = explode(",", $_SESSION['userid']);
// DB Connection String
$database = mysql_pconnect($serverAddress, $databaseUsername, $databasePassword);
mysql_select_db($databaseToUse,$database);
// retrieve header vars
$subs = $_GET['Subs'];
$leadid = $_GET['lid'];
$action = $_GET['action'];
//set upper bound for ID search
$upperid = "select ID from `tblleads` order by ID DESC LIMIT 0,1";
$upperresult = mysql_query($upperid);
$upperresultid = mysql_fetch_row($upperresult);
$upperidbound = $upperresultid[0];
//set lower bound for ID search
$lowerid = "select ID from `tblleads` order by ID ASC LIMIT 0,1";
$lowerresult = mysql_query($lowerid);
$lowerresultid = mysql_fetch_row($lowerresult);
$loweridbound = $lowerresultid[0];


if ($action == "previous") {
$leadid = $leadid-1;
//check generated lead ID not out of lower bounds to stop infinite loop and errors, redirect to global lead list if not valid
if ($leadid < $loweridbound) {
header( 'Location: dashboard.php?LoadModule=leads&Subs='.$subs );
} else {
if ((!isset($subs)) || ($subs=="ALL")) {
$sql = "SELECT ID FROM `tblleads` where (ID=".$leadid.") AND (AssignedTo=".$sessionArr[0].") order by UpdatedOn DESC";
$result = mysql_query($sql);
$returnedamount = mysql_num_rows($result);
if ($returnedamount<1) {
header( 'Location: leadpage.php?lid='.$leadid.'&Subs=ALL&action=previous' );
} else {
header( 'Location: dashboard.php?LoadModule=leads&action=view&cid='.$leadid.'&Subs=ALL' );
}
} else {
if ($subs==1) {
$sql = "SELECT * FROM `tblleads` where (ID=".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by DateAdded DESC" ;
} else {
$sql = "SELECT * FROM `tblleads` where (ID=".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by UpdatedOn DESC" ;
}
$result = mysql_query($sql);
$returnedamount = mysql_num_rows($result);
if ($returnedamount<1) {
header( 'Location: leadpage.php?lid='.$leadid.'&Subs='.$subs.'&action=previous' );
} else {
header( 'Location: dashboard.php?LoadModule=leads&action=view&cid='.$leadid.'&Subs='.$subs );
}
}

}
} else {
$leadid = $leadid+1;
//check generated lead ID not out of upper bounds to stop infinite loop and errors, redirect to global lead list if not valid
if ($leadid > $upperidbound) {
header( 'Location: dashboard.php?LoadModule=leads&Subs='.$subs ) ;
} else {
if ((!isset($subs)) || ($subs=="ALL")) {
$sql = "SELECT ID FROM `tblleads` where (ID=".$leadid.") AND (AssignedTo=".$sessionArr[0].") order by UpdatedOn DESC";
$result = mysql_query($sql);
$returnedamount = mysql_num_rows($result);
if ($returnedamount<1) {
header( 'Location: leadpage.php?lid='.$leadid.'&Subs=ALL&action=next' );
} else {
header( 'Location: dashboard.php?LoadModule=leads&action=view&cid='.$leadid.'&Subs=ALL' );
}
} else {
if ($subs==1) {
$sql = "SELECT * FROM `tblleads` where (ID=".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by DateAdded DESC" ;
} else {
$sql = "SELECT * FROM `tblleads` where (ID=".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by UpdatedOn DESC" ;
}
$result = mysql_query($sql);
$returnedamount = mysql_num_rows($result);
if ($returnedamount<1) {
header( 'Location: leadpage.php?lid='.$leadid.'&Subs='.$subs.'&action=next' );
} else {
header( 'Location: dashboard.php?LoadModule=leads&action=view&cid='.$leadid.'&Subs='.$subs );
}
}
}

}

?>

NancyJ
08-12-2008, 02:34 PM
That seems like a lot of extraneous code for something very simple (I admit I didn't read it all)
It seems like you're passing the script the id of the current record and then an action - either next or previous, then trying to work out the next record, which is a really backwards way of going about it.

The 'regular' way of doing it would be to pass a page number and then use limit to find the next record/records.

ie.



$start = ($pagesize-1)*$page;
$sql = "select * from table limit $start, $pagesize";


If you wanted to keep using the current record id, you can still skip all that redirecting just by doing: (where leadid is the id of the current record)




//next
$sql = "SELECT * FROM `tblleads` where (ID >".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by DateAdded DESC limit 0,1";

//previous
$sql = "SELECT * FROM `tblleads` where (ID <".$leadid.") AND (LeadStatus=".$subs.") AND (AssignedTo=".$sessionArr[0].") order by DateAdded DESC limit 0,1";

hinch
08-12-2008, 02:48 PM
page size is always 1 so using just plain old limit on its own was never an option iirc.

your second idea may work though using > and < within the statement as the rest of the where clauses are already there.

I'll have a play around I must admit to being very very tired when I actually write this a few months ago so looking at it now I'm actually struggling to work out why i did it like this.

hinch
08-12-2008, 03:11 PM
hrmp interesting now it kinda/semi works.

but its ignoring the order by statement almost

for example on the full list page we have the id's in this order.

1536
3103
1082
3229
307
3069

They are created by the sql statement:
$sql = "SELECT * FROM `tblleads` where AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by UpdatedOn DESC";

However if I start on ID 3229 for example the paging is pulling them out in a different order

3229
1536
1082
307

Then returns to the listings page. Its being generated by the statement

$sql = "SELECT ID FROM `tblleads` where (ID <".$leadid.") AND (AssignedTo=".$sessionArr[0].") AND IsDeleted=0 order by UpdatedOn DESC limit 0,1";

This would make perfect sense if the paging was happening WITHOUT an order by clause but because it does it should pull the ID's out in the same order as the listings page? Am I correct or getting confused ?

NancyJ
08-12-2008, 03:17 PM
page size is always 1 so using just plain old limit on its own was never an option iirc.


Limit works with a page size of 1 just fine - it doesn't care how big or small the page size is.

eg.

Page 1: limit 0,1
page 2: limit 1,1
page 3: limit 2, 1 ...etc

hinch
08-12-2008, 03:21 PM
now I know why I did it the way I did it originally :)

by using ID<$x in the sql statement your always pulling the data out of the DB sorted on ID regardless of how it was sorted on the listings page.

What I needed to do in the paging was to page based on how it was displayed on the listings page thats why I was looping through to find the next ID as the next ID in the list is not always a < or > on direction. previous may go 5,3,6,10 and next may go 1,10,3,5

If I'm explaining that good enough I hope. Any idea's on how using the methods above (as that resolves my time out problems) I can get it pulling out in the correct order.

NancyJ
08-12-2008, 03:57 PM
You're specifying the id of the record to fetch so your order by is redundant. You're just grabbing the record with an id that is one greater or one less than the specified lead id.
This still doesn't explain why you aren't just using limit $page-1,1, that would solve all the problems you're having.

hinch
08-12-2008, 04:06 PM
order is very relevent as I dont want to select the next id either one greater or one lower i need to grab the next id needed in an out of order list.

Think I've got it working now using stupid loops (was trying to avoid doing it this way) will post up when I'm done see if it makes more sense then.

hinch
08-12-2008, 04:13 PM
Nasty code but it works. will have to look over it again later on see if I can tidy it up some but meh if it works I'm not going to complain at this point.



<?php
// get configuration
set_time_limit(30);
include "includes/config.php";
session_start();
//userid session fields
//userid,groupid,isadmin,isoffice
$sessionArr = explode(",", $_SESSION['userid']);
// DB Connection String
$database = mysql_pconnect($serverAddress, $databaseUsername, $databasePassword);
mysql_select_db($databaseToUse,$database);
// retrieve header vars
$subs = $_GET['Subs'];
$leadid = $_GET['lid'];
$action = $_GET['action'];

if ($action == "previous") {
if ((!isset($subs)) || ($subs=="ALL")) {
$idarraysql = "SELECT ID FROM `tblleads` where AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by UpdatedOn DESC";
$idresult = mysql_query($idarraysql);
$lastloop=0;
$idnext=0;
while ($idlist = mysql_fetch_array($idresult)) {
if ($lastloop==1){
$idnext=$idlist[0];
$lastloop=0;
}
if ($idlist[0]==$leadid) {
$lastloop=1;
}
}
if ($idnext==0) {
header('Location: dashboard.php?LoadModule=leads&Subs=ALL');
}
if ($idnext!=0) {
header('Location: dashboard.php?LoadModule=leads&action=view&cid='.$idnext.'&Subs=ALL' );
}
} else {
if ($subs==1) {
$idarraysql = "SELECT ID FROM `tblleads` where LeadStatus=".$subs." AND AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by DateAdded DESC";
} else {
$idarraysql = "SELECT ID FROM `tblleads` where LeadStatus=".$subs." AND AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by UpdatedOn DESC";
}
$idresult = mysql_query($idarraysql);
$lastloop=0;
$idnext=0;
while ($idlist = mysql_fetch_array($idresult)) {
if ($lastloop==1){
$idnext=$idlist[0];
$lastloop=0;
}
if ($idlist[0]==$leadid) {
$lastloop=1;
}
}
if ($idnext==0) {
header('Location: dashboard.php?LoadModule=leads&Subs='.$subs);
}
if ($idnext!=0) {
header('Location: dashboard.php?LoadModule=leads&action=view&cid='.$idnext.'&Subs='.$subs);
}
}
} else {
if ((!isset($subs)) || ($subs=="ALL")) {
$idarraysql = "SELECT ID FROM `tblleads` where AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by UpdatedOn ASC";
$idresult = mysql_query($idarraysql);
$lastloop=0;
$idnext=0;
while ($idlist = mysql_fetch_array($idresult)) {
if ($lastloop==1){
$idnext=$idlist[0];
$lastloop=0;
}
if ($idlist[0]==$leadid) {
$lastloop=1;
}
}
if ($idnext==0) {
header('Location: dashboard.php?LoadModule=leads&Subs=ALL');
}
if ($idnext!=0) {
header('Location: dashboard.php?LoadModule=leads&action=view&cid='.$idnext.'&Subs=ALL' );
}
} else {
if ($subs==1) {
$idarraysql = "SELECT ID FROM `tblleads` where LeadStatus=".$subs." AND AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by DateAdded ASC";
} else {
$idarraysql = "SELECT ID FROM `tblleads` where LeadStatus=".$subs." AND AssignedTo=".$sessionArr[0]." AND IsDeleted=0 order by UpdatedOn ASC";
}
$idresult = mysql_query($idarraysql);
$lastloop=0;
$idnext=0;
while ($idlist = mysql_fetch_array($idresult)) {
if ($lastloop==1){
$idnext=$idlist[0];
$lastloop=0;
}
if ($idlist[0]==$leadid) {
$lastloop=1;
}
}
if ($idnext==0) {
header('Location: dashboard.php?LoadModule=leads&Subs='.$subs);
}
if ($idnext!=0) {
header('Location: dashboard.php?LoadModule=leads&action=view&cid='.$idnext.'&Subs='.$subs);
}
}
}

?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum