SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    next - previous buttons

    I have a SQL-statement which produces a list of customers...
    Because of the restricted size of the window I want to limit the number of record-results to 15 lines per screen...

    What I need is a script that will allow me to have a next and previous button which works on the same mysql_result.

    Does anyone know what I should do?
    Please advise

    Jazz

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I read your other post on this topic, you probably try to refrain from duplicate posts. Anyways, in short you are doing your query wrong, you need to grab only the results that are in question using MySQL's LIMIT function. In short is here how to do it. Notice I pass the var $num in the next/prev links. Any var that is critical to performing your query such as a var used in a WHERE clause needs to be passed in the next / prev links.

    PHP Code:
    $dbhost = '';
    $dbuser = '';
    $dbpass = '';
    $dbname = '';
    $tablename = '';

    $db = mysql_connect($dbhost, $dbuser, $dbpass) or DIE(mysql_error());
    mysql_select_db($dbname);

    //Run one query grabbing only row and column to get th\e total number of rows.
    $countQuery = mysql_query("SELECT COUNT(*) as totalnum from $tablename WHERE id < $num");
    $totalnum = mysql_result($countQuery, 0);


    $offset  = ($offset) ? $offset : 0;
    $limit = 15;

    $result = mysql_query("SELECT * from $tablename WHERE id < $num LIMIT $offset, $limit");
    while($row = mysql_fetch_array($result)) {
        extract($row);
        print "$ipnumber<br>";
        }

    //If the offset is greater than 0 we know we need to make a previous link
    if($offset > 0) {
        $newoffset = $offset - $limit;
        ?>
        [<a href="<?=$PHP_SELF?>?offset=<?=$newoffset?>&num=<?=$num?>">prev</a>]
        <?
        
    }

    //If the offset is less than 0 we know we need to make a previous link
    if($offset $totalnum) {
        
    $newoffset $offset $limit;
        
    ?>
        [<a href="<?=$PHP_SELF?>?offset=<?=$newoffset?>&num=<?=$num?>">next</a>]
        <?
        
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi freddy,

    Sorry about the reposting...the last one wasn't very clear,that's why I reposted...

    In your code it read:
    PHP Code:
    $countQuery mysql_query("SELECT COUNT(*) as totalnum from $tablename WHERE id < $num"); 
    This query (id < $num) will look at the id of the records, right?
    What if the first set of records have the id's (291,393,327,282,494,393)?
    Would it then screw it up?

    The id of the records (in my case it's called $rid) don't necessarily start from 1 and work their way up. (records get deleted and added all the time)

    Would this be a problem?

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'll have to excuse but I don't have your db structure memorized. The WHERE clause was put in there to show you how to query based on some criteria and carry that criteria from page to page. I didn't actually expect you to use the exact query I have shown you, you'll have to adjust to suit your needs, this code was meant as abasis for you to use and port into your own scripts. Don't take it literally.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Freddy,

    You're right... (as usual), sorry about that. I had been working for the last 12 hours, my senses weren't as sharp as they should be.

    The previous / next thing works like a charm...

    There's one problem though... (uh oh)
    say I have:
    $limit = 5
    $totalnum = 9 (the count statement)

    In the next-statement it reads that 'if($offset < $totalnum)'
    but when I'm on the page with the last records (the last 4 in my example) it still prints a next-button. When I click it, the pages comes up blank (with no records).

    What can I do about this?

    this is the code as I use it now:
    PHP Code:
    <?  // Run one query grabbing only row and column to get the total number of rows.
        
    $countQuery mysql_query("SELECT COUNT(*) 
                    FROM relatie RE LEFT JOIN relatienaw RN ON (RN.rid=RE.rid)
                            LEFT JOIN netwerk NW ON (NW.nid=RE.nid)
                    WHERE RE.sid='1' AND RN.bedrijfsnaam LIKE '
    $Alphabet%'
                    
    $SortBy");
        
    $totalnum mysql_result($countQuery0);
        
        
        
    $offset  = ($offset) ? $offset 0;
        
    $limit 5;
        
        
    $result mysql_query("SELECT * 
                    FROM relatie RE LEFT JOIN relatienaw RN ON (RN.rid=RE.rid)
                            LEFT JOIN netwerk NW ON (NW.nid=RE.nid)
                    WHERE RE.sid='1' AND RN.bedrijfsnaam LIKE '
    $Alphabet%'
                    
    $SortBy LIMIT $offset$limit");
        print 
    "<b>Aantal records: $totalnum<BR>
            offset: 
    $offset </b><BR><BR>";
        while(
    $row mysql_fetch_array($result)) {
            
    extract($row);
            print 
    "$achternaam<br>";
        }
        
        
    // If the offset is greater than 0 we know we need to make a previous link
        
    if($offset 0) {
            
    $newoffset $offset $limit;
            
    ?>
            [<a href="leden.php?SortBy=1&Alphabet=<?=$Alphabet?>&offset=<?=$newoffset?>">prev</a>]
            <?
        
    }
        
        
    // If the offset is less than 0 we know we need to make a next link
        
    if($offset $totalnum) {
            
    $newoffset $offset $limit;
            
    ?>
            [<a href="leden.php?SortBy=1&Alphabet=<?=$Alphabet?>&offset=<?=$newoffset?>">next</a>]
            <?
        
    }

  6. #6
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the solution already...
    I changed the script of the (next)statement to:
    PHP Code:
    <?
        
    //If the offset is less than 0 we know we need to make a next link
        
    if($offset < ($totalnum $limit)) {
            
    $newoffset $offset $limit;
            
    ?>
            [<a href="leden.php?SortBy=<?=$SortBy1?>&Alphabet=<?=$Alphabet?>&offset=<?=$newoffset?>">next</a>]
            <?
        
    }
    ?>
    This seems to work.

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There ya go, see now I provided the base and you made work for your needs, way to go.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

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