Posted by M. Sokolewicz on 01/08/05 18:24

and now a few small comments to your code ;)


Bruno B B Magalhães wrote:
> Thanks for your help, in fact helped a lot... Now my function only
> performs 2 queries, here it is:
>
> =======================================
> function fetch_paginated($query='',$page=1,$itens=20)
> {
> $this->query($query);
> $total_rows = $this->num_rows();
>
> if($total_rows > $itens)
> {
> $this->total_pages = ceil($total_rows/$itens);
> $this->pages_before = ceil($page - 1); // No need to ceil this!
// $page is an integer, meaning a full numer (like 1, 10, 125, -15,
etc.) ceiling the result is not useful, since the result is an integer
aswell.
> $this->pages_after = ceil($this->total_pages - $page); // same thing here
// no need to ceil it.
>
> $this->query($query.' LIMIT
> '.(($page*$itens)-$itens).','.$itens);
>
> while($this->fetch_array())
> {
> $results[] = $this->row;
> }
> }
> elseif($total_rows > 0)
> {
> while($this->fetch_array())
> {
> $results[] = $this->row;
> }
>
> $this->total_pages = '1';
> $this->pages_before = '0';
> $this->pages_after = '0';
// here, I suggest not making those values STRINGS, but instead leaving
them be integers. So instead do:
/*
$this->total_pages = 1;
$this->pages_before = 0;
$this->pages_after = 0;
*/
> }
> else
> {
> return null;
> }
>
> return $results;
> }
> =======================================
>
>
> Regards,
> Bruno B B Magalhães
>
> On Jan 7, 2005, at 10:09 PM, M. Sokolewicz wrote:
>
>> first of all, you're running 4 queries here. 4 queries is a lot!
>> Especially when you don't need more than 2 ;)
>> the problem here is that your queries are pretty "unknown" to this
>> function. Although it does a nice result for that unknowing, there's a
>> few minor things that make it faster.
>>
>> First of all, would be using less queries.
>> What I usually do is issue a query like this:
>> "SELECT count(some_unique_col) WHERE
>> (that_where_clause_youre_using_in_the_select_query)"
>> then, we do some math.
>>
>> $pages_before = $page-1;
>> $rows_before = $pages_before*$itens;
>>
>> $rows_after = $total_number_of_rows-($page*$itens);
>> $pages_after = ceil($rows_after/20);
>>
>> Then do the actual selecting of the rows using the limit.
>>
>> The thing that makes it slow in your example is the fact that 4 times
>> you're selecting ALL data from the relevant rows, and buffer it. You
>> buffer it, but don't use any of it, except for the number of rows.
>> Mysql does a far quicker job at this than PHP would, so use mysql. :)
>> Then, you're using 3 queries to determine the rows around the page;
>> even though, with a bit of simple math, you can calculate it. And
>> trust me on this, simple math is faster ;)
>>
>> anyway, hope that helped.
>
>

[Back to original message]