PERL CGI Beginners - Database Rows Returned

Author

Database Rows Returned

Cat

2005-01-04, 3:55 am

Hi folks,

I am hoping that I can get some help here.

When I search my database it comes back with the max rows and then prints a note to say that I need to refine my search. Yep you guessed it...someone elses script.

I don't understand the concept of what happens to create the code to have it present the remaining rows in linked pages. So I am asking for a little help....

local($db_status, $total_rows_returned) = @_;
local($warn_message);
$warn_message = "";

if ($db_status ne "")
{
if ($db_status =~ /max.*row.*exceed.*/i)
{
$warn_message = qq!
<CENTER>
<BLOCKQUOTE>
Your query returned $total_rows_returned. This is more than
the maximum we allow ($sc_db_max_rows_returned). You will need to
restrict your query further.
</BLOCKQUOTE></CENTER><P>!;
}
}

Hoping someone might be willing to assist.

Thanks in advance

Cat

Sean Davis

2005-01-04, 3:55 am

Cat,

Unfortunately, you haven't given us the "business" end of your script.
Unless you can tell us where $db_status and $total_rows_returned comes from,
it is unlikely that we will be able to help much.

Sean

----- Original Message -----
From: "Cat" <cat@literatecat.id.au>
To: <beginners-cgi@perl.org>
Sent: Monday, January 03, 2005 7:50 PM
Subject: Database Rows Returned

Hi folks,

I am hoping that I can get some help here.

When I search my database it comes back with the max rows and then
prints a note to say that I need to refine my search. Yep you guessed
it...someone elses script.

I don't understand the concept of what happens to create the code to
have it present the remaining rows in linked pages. So I am asking for
a little help....

local($db_status, $total_rows_returned) = @_;
local($warn_message);
$warn_message = "";

if ($db_status ne "")
{
if ($db_status =~ /max.*row.*exceed.*/i)
{
$warn_message = qq!
<CENTER>
<BLOCKQUOTE>
Your query returned $total_rows_returned. This is more than
the maximum we allow ($sc_db_max_rows_returned). You will need to
restrict your query further.
</BLOCKQUOTE></CENTER><P>!;
}
}

Hoping someone might be willing to assist.

Thanks in advance

Cat

Literatecat

2005-01-05, 3:55 am

Hi Sean,

The max_rows_returned is set in the setup file, but it simply determines the
number of rows per page. Then when it reads line by line that there are
more than the predetermined maximum rows per page, it prints the warning
that the number of rows have been exceeded, and suggests refining the search
parameters.

Problem is that I want to be able to pick up all the rows although not all
on the same page, there must be some way of getting it to change to creating
a new page with the next line read exceeding the max rows, and so on till
the search parameter is filled. Then placing the link to the next set of
"25" for instance at the bottom of the page instead of suggested that the
search be refined.

You can see this in operation at
http://www.etheria.com.au/cgi-bin/web_store.pl?

Just in case looking at it working helps. I don't understand what happens
to create the next couple of pages.

thanks in advance

cat
----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Literatecat" <cat@literatecat.id.au>
Sent: Tuesday, January 04, 2005 9:47 PM
Subject: Re: Database Rows Returned

> Cat,
>
> For whatever reason, the original developer felt it necessary to place a
> limit on the number of rows. It seems that the important variable
> governing that number is $sc_db_max_rows_returned. You could simply set
> that to a larger number. Will that do what you need?
>
> Sean
>
> On Jan 3, 2005, at 10:24 PM, Literatecat wrote:
>
>

Sean Davis

2005-01-05, 3:55 am

In order to do this, you will need to have some way of determining where you
are in the count of pages, number of rows, etc., so you will need to store
some stuff in hidden fields on your page (or in the query string). Also,
based on those values, you will need to generate a page and make a way to
move from one page to the next. This is a tedious process, but it isn't
difficult. However, you don't necessarily need to reinvent the wheel. If
you incorporate a data model into your design, like Class::DBI, you can take
advantage of some of their extension modules, like Class::DBI::Pager.
Unfortunately, any solution here will require learning a fair amount about
how your existing code works and how to generate CGI forms. Again, none of
this is hard, but I would suggest getting a CGI book if all of the above
terminology (like hidden fields) sounds foreign.

Sean

----- Original Message -----
From: "Literatecat" <cat@literatecat.id.au>
To: "Sean Davis" <sdavis2@mail.nih.gov>
Cc: <beginners-cgi@perl.org>
Sent: Tuesday, January 04, 2005 7:29 PM
Subject: Re: Database Rows Returned

> Hi Sean,
>
> The max_rows_returned is set in the setup file, but it simply determines
> the number of rows per page. Then when it reads line by line that there
> are more than the predetermined maximum rows per page, it prints the
> warning that the number of rows have been exceeded, and suggests refining
> the search parameters.

In this case, could refining search parameters be as simple as setting a
limit in the SQL query?

> Problem is that I want to be able to pick up all the rows although not all
> on the same page, there must be some way of getting it to change to
> creating a new page with the next line read exceeding the max rows, and so
> on till the search parameter is filled. Then placing the link to the next
> set of "25" for instance at the bottom of the page instead of suggested
> that the search be refined.

Unfortunately, CGI scripts cannot make decisions like "make a new page".
Their only job is to generate ONE HTML page based on input from the request.
At each page generation, you have to provide enough information on the page
so that the next request (based on a submit button click, for example)
allows the script to generate the next page when it runs again (eg., you
have to tell the script what page number in a multiple-page output to
generate; you can do this with form parameters).

> You can see this in operation at
> http://www.etheria.com.au/cgi-bin/web_store.pl?
>

Literatecat

2005-01-05, 3:55 am

Hi Sean,

I do already have some of the books that you mention, and I have just
recently picked up the perl medic to help convert these scripts to the
latest version of perl.

However, as I already use hidden fields and have started working on script
generated forms, pattern matching, mysql to mention a few things, (although
I did see some other things in a book I was flicking through last night that
will lead to some other questions here, like using tk toolkit on the web,
perl embed, perl sessions etc) I have already altered these script so far
that I doubt that copyright exists on them anymore.

I just don't understand the concept of what happens when a script tries to
create multiple pages from one query. This makes it difficult to create the
code. If you say perl DBI then I already have a book on its way, and I will
just have to wait for it to come in.

Thank for your assistance

Cat
----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Literatecat" <cat@literatecat.id.au>
Cc: <beginners-cgi@perl.org>
Sent: Wednesday, January 05, 2005 12:40 PM
Subject: Re: Database Rows Returned

> In order to do this, you will need to have some way of determining where
> you are in the count of pages, number of rows, etc., so you will need to
> store some stuff in hidden fields on your page (or in the query string).
> Also, based on those values, you will need to generate a page and make a
> way to move from one page to the next. This is a tedious process, but it
> isn't difficult. However, you don't necessarily need to reinvent the
> wheel. If you incorporate a data model into your design, like Class::DBI,
> you can take advantage of some of their extension modules, like
> Class::DBI::Pager. Unfortunately, any solution here will require learning
> a fair amount about how your existing code works and how to generate CGI
> forms. Again, none of this is hard, but I would suggest getting a CGI
> book if all of the above terminology (like hidden fields) sounds foreign.
>
> Sean
>
> ----- Original Message -----
> From: "Literatecat" <cat@literatecat.id.au>
> To: "Sean Davis" <sdavis2@mail.nih.gov>
> Cc: <beginners-cgi@perl.org>
> Sent: Tuesday, January 04, 2005 7:29 PM
> Subject: Re: Database Rows Returned
>
>
>
> In this case, could refining search parameters be as simple as setting a
> limit in the SQL query?
>
>
> Unfortunately, CGI scripts cannot make decisions like "make a new page".
> Their only job is to generate ONE HTML page based on input from the
> request. At each page generation, you have to provide enough information
> on the page so that the next request (based on a submit button click, for
> example) allows the script to generate the next page when it runs again
> (eg., you have to tell the script what page number in a multiple-page
> output to generate; you can do this with form parameters).
>
>
>

Sean Davis

2005-01-05, 8:55 am

On Jan 4, 2005, at 11:07 PM, Literatecat wrote:

> Hi Sean,
>
> I do already have some of the books that you mention, and I have just
> recently picked up the perl medic to help convert these scripts to the
> latest version of perl.
>
> However, as I already use hidden fields and have started working on
> script generated forms, pattern matching, mysql to mention a few
> things, (although I did see some other things in a book I was flicking
> through last night that will lead to some other questions here, like
> using tk toolkit on the web, perl embed, perl sessions etc) I have
> already altered these script so far that I doubt that copyright exists
> on them anymore.
>
> I just don't understand the concept of what happens when a script
> tries to create multiple pages from one query. This makes it
> difficult to create the code. If you say perl DBI then I already have
> a book on its way, and I will just have to wait for it to come in.

The issue here is that there is no concept of creating multiple pages
for one query. The query is used to generate only ONE page. If you
want to generate another page, you have to generate a new query with
new parameters. Consider a "paged" DBI output. You have a script that
generates a pages that asks the user what to search for. The user
presses submit. The next script (or a subroutine from the same script,
depending on your design) processes the query and returns results, but
only for the first page. On that same page, you need to have all the
information from the original database search page as well as a page
number. On a submit from this page, you submit back to the second
script (the same script), but a parameter in this query tells the
script that it is now to generate page two. Other information remains
the same. Confusing, yes, but that is the nature of the web. It
sounds like you might need to take a step back and make a toy example
that does something plain and simple like acccepting user input and
then generating output, perhaps using DBI. If this is going to be a
large undertaking, I encourage you to look seriously at web development
modules like CGI::Application, CGI::Builder, Maypole, and the like.

Sean

Jay

2005-01-05, 3:55 pm

On Wed, 5 Jan 2005 05:59:47 -0500, Sean Davis <sdavis2@mail.nih.gov> wrote:
>
> On Jan 4, 2005, at 11:07 PM, Literatecat wrote:
>
>
> The issue here is that there is no concept of creating multiple pages
> for one query. The query is used to generate only ONE page. If you
> want to generate another page, you have to generate a new query with
> new parameters. Consider a "paged" DBI output. You have a script that
> generates a pages that asks the user what to search for. The user
> presses submit. The next script (or a subroutine from the same script,
> depending on your design) processes the query and returns results, but
> only for the first page. On that same page, you need to have all the
> information from the original database search page as well as a page
> number. On a submit from this page, you submit back to the second
> script (the same script), but a parameter in this query tells the
> script that it is now to generate page two. Other information remains
> the same. Confusing, yes, but that is the nature of the web. It
> sounds like you might need to take a step back and make a toy example
> that does something plain and simple like acccepting user input and
> then generating output, perhaps using DBI. If this is going to be a
> large undertaking, I encourage you to look seriously at web development
> modules like CGI::Application, CGI::Builder, Maypole, and the like.
>
> Sean
>

Cat,

The issue here is that a cgi script executes anew every time it is
called. Unless you do something to save state--cgi for making the
script remember something--it will simply repeat what it's done
before. Simple data can be passed as hidden values, more complex data
structures may require client-side cookies. In your cgi books, you'll
want to look at saving state, and session management.

How exactly you go about that will depend on what is important to you
with regard to your data, and how you want your user to interact with
it. The simplest way to appraoch this is by modifying a combination
of perl and SQL. Put in a hidden field with either a "page" number or
the last row viewed, and then modify the query subroutine to add a
limit clause to the query. On the first page, you'll use "limit 25",
on the second "limit 25,25" on the third "limit 25,50", etc. Look up
limit in a mysql reference. Note, though, that this appraoch will
re-run the query each time. If a row is added before the current
page, the first item on the page will be a repeat of something the
user has already seen. Probably, this won't be an issue for you.

If you need to make sure the user sees the info as it was at the time
they made the request, you'll need to save the results somewhere and
iterate through them. You could pass them as a hidden field if
they're small, but you'll probably want a temporary file somewhere.
Chek out File::Temp

HTH,

--jay

Scott R. Godin

2005-01-08, 8:55 pm

Sean Davis wrote:
>
> On Jan 4, 2005, at 11:07 PM, Literatecat wrote:
>
>
>
> The issue here is that there is no concept of creating multiple pages
> for one query. The query is used to generate only ONE page. If you
> want to generate another page, you have to generate a new query with new
> parameters. Consider a "paged" DBI output. You have a script that
> generates a pages that asks the user what to search for. The user
> presses submit. The next script (or a subroutine from the same script,
> depending on your design) processes the query and returns results, but
> only for the first page. On that same page, you need to have all the
> information from the original database search page as well as a page
> number. On a submit from this page, you submit back to the second
> script (the same script), but a parameter in this query tells the script
> that it is now to generate page two. Other information remains the
> same. Confusing, yes, but that is the nature of the web. It sounds
> like you might need to take a step back and make a toy example that does
> something plain and simple like acccepting user input and then
> generating output, perhaps using DBI. If this is going to be a large
> undertaking, I encourage you to look seriously at web development
> modules like CGI::Application, CGI::Builder, Maypole, and the like.
>
> Sean
>

this is usually done by passing a count and possibly a limit via the url
of the 2nd request, so that your db request includes LIMIT y, x or
somesuch, to limit the request to x number of values starting at y (if I
correctly remember how that works).

Generally one also need to initially pass an additional request to the
db in advance of displaying the page (resulting in 2 requests per page
display) to get the max # of results returned, so that you can test for
when you need to limit.

--
Scott R. Godin
Laughing Dragon Services
www.webdragon.net

Sponsored Links