Home > Archive > PERL CGI Beginners > September 2007 > Perl/DBI
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Shawn Hinchy 2007-06-24, 9:55 pm |
| Hello all,
I am getting to the point where I need to start thinking about doing
pagination for search results.
I did some searches and it looks like there are some pagination
modules, but I am not quite interested in going that route yet. I am
using a handful of columns from the search so I do not think that
retrieving all results and then only showing a subset will be very
efficient. It also looks like you can execute FOUND_ROWS() after
doing your initial query and it will give you the total results. This
is probably the best route, but it is not easily implemented with the
way I have set up my functions.
Are there any other options that I have missed? Suggestions?
Thanks,
Shawn
--------------------
Shawn Michael Hinchy
shawn@hinchy.net
| |
| Tyler Gee 2007-06-24, 9:55 pm |
| On 6/24/07, Shawn Hinchy <shawn@hinchy.net> wrote:
>
> Hello all,
>
> I am getting to the point where I need to start thinking about doing
> pagination for search results.
>
> I did some searches and it looks like there are some pagination
> modules, but I am not quite interested in going that route yet. I am
> using a handful of columns from the search so I do not think that
> retrieving all results and then only showing a subset will be very
> efficient. It also looks like you can execute FOUND_ROWS() after
> doing your initial query and it will give you the total results. This
> is probably the best route, but it is not easily implemented with the
> way I have set up my functions.
>
> Are there any other options that I have missed? Suggestions?
If you are not interested in using the modules, it is not actually that hard
to roll your own. In the past I have done different things for total
counts, either two separate queries, one just a 'SELECT COUNT(*)' and the
other to actually fetch the data. Depending on your data you might be able
to add the count into your data fetching query but probably not.
Why do you not want to use the modules?
Thanks,
>
> Shawn
>
> --------------------
> Shawn Michael Hinchy
> shawn@hinchy.net
>
> --
> To unsubscribe, e-mail: beginners-cgi-unsubscribe@perl.org
> For additional commands, e-mail: beginners-cgi-help@perl.org
> http://learn.perl.org/
>
>
>
--
~Tyler
| |
| Shawn Hinchy 2007-06-24, 9:55 pm |
| Quoting Tyler Gee <g out@gmail.com>:
> On 6/24/07, Shawn Hinchy <shawn@hinchy.net> wrote:
>
>
> If you are not interested in using the modules, it is not actually that ha=
rd
> to roll your own. In the past I have done different things for total
> counts, either two separate queries, one just a 'SELECT COUNT(*)' and the
> other to actually fetch the data. Depending on your data you might be abl=
e
> to add the count into your data fetching query but probably not.
>
> Why do you not want to use the modules?
Thank you Tyler, I hadn't though about just using select COUNT(*).
I was shying away from using the modules because I knew that it wasn't =20
too hard and I was afraid that I wouldn't find one that would do =20
exactly what i wanted. Then there is the concern that my needs will =20
change in the future and I'll have to find and re-implement another =20
module instead of just tweaking my own code. Or maybe I just like to =20
go through the pain on my own. :)
I think two separate queries will work fine for me now, I just didn't =20
want to have to load all the results for fear it would be a memory hog =20
and slow things down since searches will be frequent.
Thanks,
Shawn
--------------------
Shawn Michael Hinchy
shawn@hinchy.net
| |
| Tyler Gee 2007-06-24, 9:55 pm |
| On 6/24/07, Shawn Hinchy <shawn@hinchy.net> wrote:
>
> Quoting Tyler Gee <gout@gmail.com>:
>
> hard
> the
> able
>
> Thank you Tyler, I hadn't though about just using select COUNT(*).
>
> I was shying away from using the modules because I knew that it wasn't
> too hard and I was afraid that I wouldn't find one that would do
> exactly what i wanted. Then there is the concern that my needs will
> change in the future and I'll have to find and re-implement another
> module instead of just tweaking my own code. Or maybe I just like to
> go through the pain on my own. :)
>
> I think two separate queries will work fine for me now, I just didn't
> want to have to load all the results for fear it would be a memory hog
> and slow things down since searches will be frequent.
One thing people sometimes do if they have a lot of rows (a whole lot) is to
actually keep a separate table that stores the count and then you can just
select from that. It is not recommended unless you really have a lot of
rows because it de-normalizes the database. The count() should work fine
however.
Cheers,
Thanks,
>
> Shawn
>
> --------------------
> Shawn Michael Hinchy
> shawn@hinchy.net
>
> --
> To unsubscribe, e-mail: beginners-cgi-unsubscribe@perl.org
> For additional commands, e-mail: beginners-cgi-help@perl.org
> http://learn.perl.org/
>
>
>
--
~Tyler
| |
| Greg Jetter 2007-06-24, 9:55 pm |
| On Sunday June 24 2007 8:31 am, Shawn Hinchy wrote:
> Hello all,
>
> I am getting to the point where I need to start thinking about doing
> pagination for search results.
>
> I did some searches and it looks like there are some pagination
> modules, but I am not quite interested in going that route yet. I am
> using a handful of columns from the search so I do not think that
> retrieving all results and then only showing a subset will be very
> efficient. It also looks like you can execute FOUND_ROWS() after
> doing your initial query and it will give you the total results. This
> is probably the best route, but it is not easily implemented with the
> way I have set up my functions.
>
> Are there any other options that I have missed? Suggestions?
>
> Thanks,
>
> Shawn
>
> --------------------
> Shawn Michael Hinchy
> shawn@hinchy.net
HTML::Pager is what your looking for
it handles the page count for you , you just tell it how many results to
present per page.
it trivial to set up and use.
Greg
| |
| Paul Archer 2007-06-24, 9:55 pm |
| >> > If you are not interested in using the modules, it is not actually that
>
> One thing people sometimes do if they have a lot of rows (a whole lot) is to
> actually keep a separate table that stores the count and then you can just
> select from that. It is not recommended unless you really have a lot of
> rows because it de-normalizes the database. The count() should work fine
> however.
>
Keep in mind that count() is database dependent, even database-engine
dependent. For instance, mySQL's MyISAM engine keeps a running count of the
number of rows in a table, so count() is cheap. Buy the InnoDB engine does
*not* keep that running total, so count() is relatively expensive. If you're
talking a few hundred or thousand rows, it shouldn't be a problem--but if
you're talking large numbers (six million rows of genomic data, anyone),
then it'll probably be an issue.
Paul
| |
|
|
| David Busby 2007-07-24, 9:55 pm |
| Shawn Hinchy wrote:
> Hello all,
>
> I am getting to the point where I need to start thinking about doing
> pagination for search results.
>
> I did some searches and it looks like there are some pagination modules,
> but I am not quite interested in going that route yet. I am using a
> handful of columns from the search so I do not think that retrieving all
> results and then only showing a subset will be very efficient. It also
> looks like you can execute FOUND_ROWS() after doing your initial query
> and it will give you the total results. This is probably the best
> route, but it is not easily implemented with the way I have set up my
> functions.
>
> Are there any other options that I have missed? Suggestions?
>
> Thanks,
>
> Shawn
>
> --------------------
> Shawn Michael Hinchy
> shawn@hinchy.net
Shawn; I can only speak to Oracle but if your looking at counts be sure
that you use it something like this.
Select * from (your real query here) where rownum >=lownum and rownum<=
highnum or something of that nature where the lownum and highnum
represents the beginning and end of rows you want to get. Say you want
to run the query get the first 5K rows. You could write the low as 1
and the high as 5000. Use binds. You could run your query first with
something like select count(*) from (inner query) to return the total
number of rows but you may just be wasting resources in that case.
Let me know if you have any further questions.
| |
| Hasnain 2007-09-17, 12:15 am |
| gay, oral amber movie, oral milf, bukkake, taylor rain movie, oral trailers, oral thumbnail, oral hardcore, video clip blow, facial, blow stories, mpg movie, oral free, oral free, oral preview, free movie, voyeur.
bj |
|
|
|
|