my $sth = $dbh->prepare( "SELECT COUNT(*) FROM my_table WHERE somethin
+g LIKE something_else" ) or die DBI->errstr;
$sth->execute( ) or die DBI->errstr;
my ( $count ) = $sth->fetchrow_array();
[download]

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

Nit: unnecessary parens provide lurking places for sneaky little bugs caused by asking for a list where a scalar is wanted, which can bring the cops to your door.

I assume you're talking about this line:

my ( $count ) = $sth->fetchrow_array();
[download]

fetchrow_array() returns an array. If you assign it like:

my $count = $sth->fetchrow_array();
[download]

assuming the SQL that I gave as an example, $count will always equal 1, the scalar context of the array on the right hand side. The parans are there to keep the array context and are quite necessary in this case.

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

my $query = 'SELECT a_column from table";
my $dbh = however_you_connect;
my $sth =$dbh->do($query);
my $rows_per_page = 10;
my $pages = $sth->rows / $rows_per_page;
[download]

thanks a lot, using that count number i can divide it by /30 which gives me the number of page, like 2.3 so that would be 3 pages (2 and a bit) but how can i use that number, say 45 to generate this html: e.g. << 1 .. 2...3 >>

oh good point, use int() to force it into integer

The advice you already got answers your question, but you may not need to know how many pages you get. You will if you want to index them all on each page, but you can just try a page number check if you get results.

You can also get mysql to do more of the work for you. The LIMIT clause in sql can take placeholders (at least in the mysql driver). Here is code which searches a zipcode database in chunks of 30 for towns starting and ending with 'A':

my $limited = $dbi->prepare('select Zipcode, Zipname from zips
                                 where Zipname like ? order by 1 limit
+ ?,?');
my ( $pagelines, $page) = ( 30, 0);
while ($limited->execute( 'A%A', $page++ * $pagelines, $pagelines)
           && $limited->rows) {
    my @rec = $limited->fetchrow_array;
    printf "%2d records starting with:  %05d %s$/",
           $limited->rows,
           $rec[0], $rec[1];
}

=pod
Output is:
30 records starting with:  04330 AUGUSTA
30 records starting with:  22301 ALEXANDRIA
30 records starting with:  30316 ATLANTA
30 records starting with:  33920 ALVA
30 records starting with:  47001 AURORA
30 records starting with:  54720 ALTOONA
30 records starting with:  61414 ALTONA
30 records starting with:  67010 AUGUSTA
30 records starting with:  72611 ALPENA
30 records starting with:  80018 AURORA
 6 records starting with:  97103 ASTORIA

The number of pages can be obtained from:
$pages = int(($limited->execute( 'A%A', 0, -1), $limited->rows)/$pagel
+ines)
            + ($limited->rows % $pagelines | 0 ? 1 : 0);
This is a ceil() function, it acts like int but rounds up.
=cut
[download]

Notice that we don't need to know how many there are, we just check if the return has zero rows. You'll need to keep track of what page the user just saw, that is saving state in a session. There are lots of ways to do that, but a form value with the current page number would work fine.

After Compline,
Zaxo

You want to do as Masem says and use COUNT(*) (which is optimized in MySQL) to get the total number of rows. Divide those into groups depending on the scale of the results. I ended up doing this:

        for (my $i=100; $i <= 1000; $i += 100) {
            if ($num_matched <= $i) {
                $rows = $i / 10;
                last;
            }
        }
        unless ($rows) {
            for (my $i=2000; $i <= 10000; $i += 1000) {
                if ($num_matched <= $i) {
                    $rows = $i / 10;
                    last;
                }
            }
        }
        $rows = 1000 if $num_matched > 10000;
[download]

Basically I had to play with it to get the number of results per page to be reasonable (if there are 50 results, then you want 1-10, 11-20, etc.., but then if there are 500 results you might want 1-100, 101-200, etc..).

Then the other main part is maintaining the "state" of the search results. So, if they're on page 3, you have to know that corresponds to "LIMIT 21,30" in the SQL, say. If you look at the link above, there is a CGI param called "offset" for that.