Posted by Matthew Weier O'Phinney on 04/12/05 00:03

* D. Wokan <wokan@cox.net>:
> Matthew Weier O'Phinney wrote:
> > * Reynier Perez Mira <rperezm@estudiantes.uci.cu>:
> >
> > > I'm using PEAR DB and Smarty. The case is that I have to pass a value =
> > > from a query result to a Smarty var. I try to paginate results. See the =
> > > code below:
> > >
> > > /////////////////////////////////////////////////////////////////////////=
> > >
> > > SmartyPaginate::connect();
> > > SmartyPaginate::setLimit(5);
> > > $gbresult =3D $database->query("SELECT * FROM libro_de_visitas LIMIT " . =
> > > SmartyPaginate::getCurrentIndex() . "," . SmartyPaginate::getLimit());
> > > $gbtotal =3D $database->query("SELECT COUNT(*) AS total FROM =
> > > libro_de_visitas");
> > > $gbtotal->fetchRow();
> > > SmartyPaginate::setTotal($gbtotal['total']);
> > >
> > > /////////////////////////////////////////////////////////////////////////=
> > >
> > > As you can see I need the COUNT value but I not know how get it from =
> > > query using PEAR. Some body can help me?
> >
> > You'll need to do two different queries, one that counts the number of
> > total results for the criteria, and one that pulls the set with a LIMIT
> > statement.
> >
> That is definitely not a Good Thing. I did that with an employer's site
> and database performance suffered having to always count and recount the
> records in question.

Typically, I store this count in the session or in a cache to prevent
such performance issues; if the data is constantly changing, you can
also have a script that performs the count for you and stores it in a
common retrieval area. However, database performance for a count
operation can also be tuned quite easily with the use of indexes, and
shouldn't be an expensive operation unless you have multiple hundreds of
thousands of records.

--
Matthew Weier O'Phinney | WEBSITES:
Webmaster and IT Specialist | http://www.garden.org
National Gardening Association | http://www.kidsgardening.com
802-863-5251 x156 | http://nationalgardenmonth.org
mailto:matthew@garden.org | http://vermontbotanical.org

[Back to original message]