Data::Page module assistance please.

[bazz]

Hi,

I am trying to paginate my data but seem only to be able to get the total number of records to show, total pages, number per page indeed, almost all I could want.

Unfortunately, it doesn't actually split the data across several pages. it still just lists it on the one page.

Please can any of you give me a step up.

Do I need to use a conditional clause in the while loop, based on the values got by Data::Page?


$sth->execute;

my $i;
my $rows = $sth->rows();


$page->total_entries($rows);
#$page->entries_per_page($entries_per_page);
#$page->current_page($current_page);

print qq(
<h5>Your search has retrieved $rows results</h5>
);

print "Entries:", $page->total_entries, "\n";
print "First page: ", $page->first_page, "\n";
print "Last page: ", $page->last_page, "\n";
print "First entry on page: ", $page->first, "\n";
print "Last entry on page: ", $page->last, "\n";
print "Per page:", $page->entries_per_page, "\n";
print "Page: ", $page->current_page, "\n";
print "There are ", $page->entries_on_this_page, " entries displayed\n";
print "Showing entries from: ", $page->first, "\n";


while ( my($business_id,$business_name, $name_no, $address_1, $address_2, $address_3, $town_or_townland, $city_or_county, $post_code, $country_name) = $sth->fetchrow_array() )
{
this part outputs the data to the page.
}


all the print statements output correctly.

bazz

[FishMonger]

Please show us your select statement.

[bazz]

OK.


my $sth = $dbhconnect->prepare ("SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?
GROUP BY BD.business_id
ORDER BY RAND()
") or die "prepare statement failed: $DBI::errstr\n";

$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category );




bazz

[FishMonger]

You need to add a limit clause.

See the "skipped" section.
http://search.cpan.org/~lbrocard/Data-Page-2.01/lib/Data/Page.pm#skipped

[bazz]

Thanks FishMonger; I have done that and get an error.

I am confused about how the query can be run with values for the (limit) placeholders when those values semm to me, not to be available until after the query is run.

btw, the query is now like this


my $sth = $dbhconnect->prepare ("SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?
LIMIT ?, ?
GROUP BY BD.business_id
ORDER BY RAND()
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category, $page->skipped, $page->entries_per_page );


and the error is this


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'
GROUP BY BD.business_id
' at line 27 at ../cgi-bin/business_search line 101.



??

bazz

[oesxyl]

Thanks FishMonger; I have done that and get an error.

I am confused about how the query can be run with values for the (limit) placeholders when those values semm to me, not to be available until after the query is run.

btw, the query is now like this


my $sth = $dbhconnect->prepare ("SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?
LIMIT ?, ?
GROUP BY BD.business_id
ORDER BY RAND()
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category, $page->skipped, $page->entries_per_page );


and the error is this


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'
GROUP BY BD.business_id
' at line 27 at ../cgi-bin/business_search line 101.



??

bazz
hi,

limit ?, ? must be the last thing in your query.

best regards

[bazz]

Thanks oesxyl but, I get the same error message.

here is the code as it's currently written.

sub show_businesses {


print qq(
<div id="Home_Page">
);

my $sth = $dbhconnect->prepare ("SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?

GROUP BY BD.business_id
ORDER BY RAND()
LIMIT ?, ?
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category, $page->skipped, $page->entries_per_page );

my $i;
my $rows = $sth->rows();


$page->total_entries($rows);
#$page->entries_per_page($entries_per_page);
#$page->current_page($current_page);

print qq(
<h5>Your search has retrieved $rows results.</h5>
);
print "Entries:", $page->total_entries, "<br />";
print "First page: ", $page->first_page, "<br />";
print "Last page: ", $page->last_page, "<br />";
print "First entry on page: ", $page->first, "<br />";
print "Last entry on page: ", $page->last, "<br />";
print "Per page:", $page->entries_per_page, "<br />";
print "Page: ", $page->current_page, "<br />";
print "There are ", $page->entries_on_this_page, " entries displayed <br />";
print "Showing entries from: ", $page->first, "<br />";

if ($page->previous_page) {
print "Previous page number: ", $page->previous_page, "\n";
}



All the print statements work after the query has been made.
As far as I can understand from the module doc, $page->total_entries($rows) must get a value sent to it.($rows in this case).

However, that value isn't available until after the query has been run and the query needs it beforehand :confused:

Maybe there is a differnet (better), module I should use?

ALSO, I don't seem to be able to figure out how to put this into a standrad variable. Is it possible?


my total = "$page->total_entries";


bazz

[oesxyl]

Thanks oesxyl but, I get the same error message.
can't be same error, probably you must clean the cache. Once I work few days because of this.
When I say same error I mean same message as in your previous post.

here is the code as it's currently written.

sub show_businesses {


print qq(
<div id="Home_Page">
);

my $sth = $dbhconnect->prepare ("SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?

GROUP BY BD.business_id
ORDER BY RAND()
LIMIT ?, ?
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category, $page->skipped, $page->entries_per_page );

my $i;
my $rows = $sth->rows();


$page->total_entries($rows);
#$page->entries_per_page($entries_per_page);
#$page->current_page($current_page);

print qq(
<h5>Your search has retrieved $rows results.</h5>
);
print "Entries:", $page->total_entries, "<br />";
print "First page: ", $page->first_page, "<br />";
print "Last page: ", $page->last_page, "<br />";
print "First entry on page: ", $page->first, "<br />";
print "Last entry on page: ", $page->last, "<br />";
print "Per page:", $page->entries_per_page, "<br />";
print "Page: ", $page->current_page, "<br />";
print "There are ", $page->entries_on_this_page, " entries displayed <br />";
print "Showing entries from: ", $page->first, "<br />";

if ($page->previous_page) {
print "Previous page number: ", $page->previous_page, "\n";
}



All the print statements work after the query has been made.
As far as I can understand from the module doc, $page->total_entries($rows) must get a value sent to it.($rows in this case).

However, that value isn't available until after the query has been run and the query needs it beforehand :confused:

Maybe there is a differnet (better), module I should use?

ALSO, I don't seem to be able to figure out how to put this into a standrad variable. Is it possible?


my total = "$page->total_entries";


bazz
is a typo here, missing $ to total and is better to remove quote:

my $total = $page->total_entries;

there are other few things:
- this is inside a sub, how you pass $page to the sub? it's global?
- what is $page? is clear that is a structure but what kind hash or array or more complex?

best regards

[bazz]

Hi oesxyl,


there are other few things:
- this is inside a sub, how you pass $page to the sub? it's global?
- what is $page? is clear that is a structure but what kind hash or array or more complex?



my $page = Data::Page->new();


the error message is the same.


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'' at line 29 at ../cgi-bin/business_search line 126.




DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'
GROUP BY BD.business_id
' at line 27 at ../cgi-bin/business_search line 126.


as far as I can see, the only difference is because of where the limit clause is positioned. it seems to me the fault is caused by the 0 value of $page->skipped. I am really struggling to work out how to give it a vlue when its not there until aftre the query - and the query needs the value. :bangs head off desk:

very frustrating. :(


bazz

[FishMonger]

You need 2 select statements. The first does a select count(*) and uses that result as the total records to be used in the pagination.

Also:
my total = "$page->total_entries";
is better written as
my $total = $page->total_entries;

[bazz]

:bows to FishMonger:

I had discounted that idea because it seems like duplication to me. thought I must be wrong.

anyway, I shall work a bit more in this and let you know how I get on.

either, with a bandaged head
or a smiley face (and a drink)

Thanks both of you

bazz

[oesxyl]

Hi oesxyl,



my $page = Data::Page->new();


the error message is the same.


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'' at line 29 at ../cgi-bin/business_search line 126.




DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'
GROUP BY BD.business_id
' at line 27 at ../cgi-bin/business_search line 126.


as far as I can see, the only difference is because of where the limit clause is positioned. it seems to me the fault is caused by the 0 value of $page->skipped. I am really struggling to work out how to give it a vlue when its not there until aftre the query - and the query needs the value. :bangs head off desk:

very frustrating. :(


bazz
you can use limit ? instead of limit ?, ? and this way the offset 0 will be default. That if you don't need to pass another offset then 0. Anyway check the cache to be sure that you don't waste your time with endless modification without effect. I use to disable all what is cache if I can when I develop.

best regards

[bazz]

Thanks oesxyl.

I have made 2 queries the first of which gets me the total number of results. :cool:

the second query should get me the actual results and list them. guess what. I have the same problem as before. It doesn't like the placeholders, whether I have the limt ?,? or Limit ?

If I have limit ? i get the following error.


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 29 at ../cgi-bin/business_search line 159.


if I use two placeholders I get this error


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'' at line 29 at ../cgi-bin/business_search line 159.


here are the two queries as they are now.


use Data::Page;
my $page = Data::Page->new();
my $entries_per_page = '10';

my $Path = $ENV{"PATH_INFO"};
$Path =~ s/\///;
my $current_page;

if (!$Path || $Path eq '')
{
$current_page = 1;
}


print qq(
<div id="Home_Page">
);

my $stuff = $dbhconnect->prepare("SELECT count(*),
BD.business_id
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id

AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?

") or die "prepare statement failed: $DBI::errstr\n";




$stuff->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category);

my @fields;
my @count;

while ( ( @fields) = $stuff->fetchrow_array() )
{
print qq( acols = $fields[0] );
push (@count, $fields[0]);
}

$page->total_entries($count[0]);
$page->entries_per_page($entries_per_page);
$page->current_page($current_page);
print "Entries:", $page->total_entries, "<br />";
print "First page: ", $page->first_page, "<br />";
print "Last page: ", $page->last_page, "<br />";
print "First entry on page: ", $page->first, "<br />";
print "Last entry on page: ", $page->last, "<br />";
print "Per page:", $page->entries_per_page, "<br />";
print "Page: ", $page->current_page, "<br />";
print "There are ", $page->entries_on_this_page, " entries displayed <br />";
print "Showing entries from: ", $page->first, "<br />";

if ($page->previous_page) {
print "Previous page number: ", $page->previous_page, "\n";
}


my $sth = $dbhconnect->prepare("
SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?
GROUP BY BD.business_id
ORDER BY RAND()
LIMIT ?, ?
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category, $page->skipped, $page->entries_per_page );


The reason for this error is beyond me. :(

bazz

[oesxyl]

Thanks oesxyl.

I have made 2 queries the first of which gets me the total number of results. :cool:

the second query should get me the actual results and list them. guess what. I have the same problem as before. It doesn't like the placeholders, whether I have the limt ?,? or Limit ?

If I have limit ? i get the following error.


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 29 at ../cgi-bin/business_search line 159.

it's a string and expect a integer? I guessing.

The reason for this error is beyond me. :(

I don't see anything wrong in your code.

best regards

[bazz]

feel free to guess. It's about as good as I get right now. :D

When you mention it might be an string instead of an integer the only one that this could apply to is that which is returned by the first query? so how would I make sure it is an integer? I thought it would be in integer (once you raised the subject).

bazz

[oesxyl]

feel free to guess. It's about as good as I get right now. :D

When you mention it might be an string instead of an integer the only one that this could apply to is that which is returned by the first query? so how would I make sure it is an integer? I thought it would be in integer (once you raised the subject).

bazz
force him to be int and see if after this change you get rid of error, use int.

I don't have something better in my mind at this moment, :)

best regards

[bazz]

well I set it up to add three to it and I got four, so I guess it must already be int.

I shall sleep on it but if you happen to think of something please come back with your ideas/guesses/dreams. I am ready to try pretty much anything. :D

bazz

[oesxyl]

well I set it up to add three to it and I got four, so I guess it must already be int.

I shall sleep on it but if you happen to think of something please come back with your ideas/guesses/dreams. I am ready to try pretty much anything. :D

bazz
I will do, :)
sometimes the best thing is to take a break and start fresh later, :)

best regards

[bazz]

just for information this is the result of the first query interacting with the module.



Entries:11
First page: 1
Last page: 2
First entry on page: 1
Last entry on page: 10
Per page:10
Page: 1
There are 10 entries displayed
Showing entries from: 1
skipped:0


and I have read the limit docs for MySQL but success eludes me - again :(
bazz

[FishMonger]

Try putting the values for the limit clause in the prepare statement instead of using the placeholders.
my $sth = $dbhconnect->prepare("
SELECT
BD.business_id
, BD.business
, ADDR.name_no
, ADDR.address_1
, ADDR.address_2
, ADDR.address_3
, ADDR.town_or_townland
, ADDR.city_or_county_name
, ADDR.post_code
, ADDR.country_name
FROM business_details BD
INNER
JOIN address ADDR
ON BD.business_id = ADDR.business_id
INNER
JOIN touristInformationCentres TIC
ON BD.tic = TIC.tic
INNER
JOIN business_type BT
ON BD.business_type_id = BT.business_type_id
AND ADDR.city_or_county_name = ?
AND ADDR.town_or_townland = ?
AND BT.business_type = ?
AND BT.business_sub_type = ?
AND BT.business_category = ?
GROUP BY BD.business_id
ORDER BY RAND()
LIMIT $page->skipped, $page->entries_per_page
") or die "prepare statement failed: $DBI::errstr\n";




$sth->execute($city_or_county, $town_or_townland,$business_type, $business_sub_type, $business_category );

[bazz]

Fishmonger, I had tried that to but it returned an error


DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Data::Page=HASH(0x9a5cb78)->skipped, Data::Page=HASH(0x9a5cb78)->entries_per_pag' at line 29 at ../cgi-bin/business_search line 159.


But you got me thinking (after half a night's sleep) and I assigned them each to a variable ($skipped and $entries_per_page) and it works now.

Thank you very much

I had found a MySQL term before shut-eye called FOUND_ROWS. It mightb enable me to do one query (without the need for the count query), and to get the total records (without the limit), and to use it along with $entries_per_page and the offset value. If so I might be able to the pagination without the module.

I'll fiddle with it over the next couple of days and let you know if it works out.

thanks again.

bazz