Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    Get next ID / Better way of doing paging

    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 Code:
    <?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 );
                }
            }
        }

    }

    ?>
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    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.

    PHP Code:
    $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)

    PHP Code:

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

  • #3
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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 ?
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #5
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    Quote Originally Posted by hinch View Post
    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

  • #6
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #7
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    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.

  • #8
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #9
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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 Code:
    <?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);
                }
            }
    }

    ?>
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •