[thelist] Page breaks in mysql?

[Anthony Baratta]

Adrian Fischer wrote:
> 
> Hi Gang,
> 
> I was hoping someone may have a handy bit of script available that works
> with myself that allows for page breaks when retrieving data.  The type of
> thing that you can customize to suit the number of items you want displayed
> at one time with the option to go to page 2,3,4 etc.  There was a posting
> last week for CF I think but I don't think I can modify it to suit.
> 
> Can anybody point me in the right direction? Oh... the language is
> perl/myself.

Adrain...

I think your spell checker ran amok! LOL

Here is a bit of code I wrote for someone a while ago, hope it helps:
(Big Note: I hacked this up from the original. You will need to read it 
and modify it so that it will work in your environment. Watch for line wrap.)

# You need to pass the Starting page to the 
# function, if no parameter starts at Page 1
# e.g. page_through_data($input{'Page'})

sub page_through_data {  
    if ($_[0] > 0) {
        $StartNumber = $_[0] * 10;
    } else {
        $StartNumber = 1;
    }##end if 

    ## Opening Connection to Database
    $DatabaseString = "DBI:mysql:$varDatabase";
    $DatabaseConnection = DBI->connect($DatabaseString, $varUser, $varPasswd);

    ## Selecting Information into Database
    $QueryString = "SELECT * FROM tablename WHERE column1 = '$valueone'";
    $SelectInfo = $DatabaseConnection->prepare("$QueryString");
    $RetValue = $SelectInfo->execute;
    
    if ($RetValue < 1) {
        &error_in_select;
        exit;
    }##end if 
    
    # Setting up Page Count
    # This assumes 10 items listed per page
    $varReturnValue = $RetValue
    if (chop($varReturnValue) > 0) {
        $varPageCount = $varReturnValue + 1;
    } else {
        $varPageCount = $varReturnValue;
    }##end if 
        
    ## Setting Counter to Zero
    $counter = "0";

    $HTMLPage = "<table><tr><td>Column One</td><td>Column two</td><td>Column
three</td><td>Column four</td></tr>\n"
    
    ## Opening up record set
    while ($varDBInfo = $SelectInfo->fetchrow_arrayref) {

        ## Incriment Counter for each new record
        $counter++;

        ## If counter is between $StartNumber and $StartNumber + 10 then print out
info
        if ($counter >= $StartNumber && $counter <= $StartNumber+10) {
            HTMLPage .=
"<tr><td>$$varDBInfo[0]</td><td>$$varDBInfo[1]</td><td>$$varDBInfo[2]</td><td>$$varDBInfo[3]</td></tr>\n";
        }##end if 
    }##end while


    ## Setting up Page Navigation
    $HTMLNav = "<br>"
    for ($i=1;i<=$varPageCount;$i++) {
        $HTMLNav = "<a href=\"./scriptname.cgi?Page=" . $i . "\">Page&nbsp;" . $i .
"</a>";
    }#end for
    
    ## Print Nav Info
    $HTMLPage .= "<tr><td colspan=\"4\">$HTMLNav</td></tr>\n";

    $HTMLPage .= "</table>\n"

    ## Disconnect from Database
    $SelectUserInfo->finish;
    $DatabaseConnection->disconnect;
    
    ## Print Captured HTML Page
    return $HTMLPage;
}#END Parse_CraftRentals