Pagination links -- what's the trick to get them to show up?

Talk about Sorting & Paging problems, issues etc.

Moderator: alexandrleonenko

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Oct 3rd, '09, 01:16

I have 4 grids on different pages. The settings are identical except for the columns (which are extremely similar) and the SQL.

1 of the grids shows the pagination links.

Another showed them only after doing a sort ... and i found that my ORDER BY was messed up until then. So that one works.

But the other 2 won't show those pagination links for anything.

What should I check?

Thanks!

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Oct 3rd, '09, 02:02

adding
$dgrid->AllowPaging(true,false,"N #",false);
changed nothing ...

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: Pagination links -- what's the trick to get them to show up?

Postby administrator » Oct 3rd, '09, 18:53

Take an example of code and check how it works.

The full code you need is:

/// $paging_option = true;
/// $rows_numeration = false;
/// $numeration_sign = "N #";
/// $dropdown_paging = false;
/// $dgrid->AllowPaging($paging_option, $rows_numeration, $numeration_sign, $dropdown_paging);
## *** set paging settings
/// $bottom_paging = array("results"=>true, "results_align"=>"left", "pages"=>true, "pages_align"=>"center", " "=>true, "page_size_align"=>"right");
/// $top_paging = array("results"=>true, "results_align"=>"left", "pages"=>true, "pages_align"=>"center", "page_size"=>true, "page_size_align"=>"right");
/// $pages_array = array("10"=>"10", "25"=>"25", "50"=>"50", "100"=>"100", "250"=>"250", "500"=>"500", "1000"=>"1000");
/// $default_page_size = 10;
/// $paging_arrows = array("first"=>"|<<", "previous"=>"<<", "next"=>">>", "last"=>">>|");
/// $dgrid->SetPagingSettings($bottom_paging, $top_paging, $pages_array, $default_page_size, $paging_arrows);

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Oct 5th, '09, 17:49

thanks ... but ... it didn't help.

Now the grids where pagination shows up at all have it both top and bottom ... though I see clearly how to turn off one or the other. And the grids that lacked pagination still do.

Is there another trick?

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Oct 6th, '09, 00:48

Reviewing the code, it appears that the pagination links are displayed only if pagesTotal >= 1 (PagingSecondPart).

And pagesTotal is set based on rowsTotal (PagingFirstPart).

And rowsTotal is set in SetTotalNumberRows.

It appears that certain queries fail the kind of rowcount manipulation in that function.

I'll see if I can come up with a patch ...

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Oct 6th, '09, 01:15

OK, here's my modification to the function. Basically, if the attempt to get the count you have coded errors out, I catch that and try a different SQL construct. It is working for the SQL statements I'm using and cannot break those your code properly handles as it kicks in ONLY if there's a problem with yours. I do not guarantee this will handle all cases ... but it handles a couple of them.

Code: Select all

//--------------------------------------------------------------------------
    // Set total number of rows in query
    //--------------------------------------------------------------------------
    protected function SetTotalNumberRows($fsort = "", $limit = "", $mode = "", $first_time = false){
        $req_mode = ($mode == "") ? $this->GetVariable('mode') : $mode;
        $temp_sql = $this->SetSqlByDbType($this->sql, "", "");
        $this->rowsTotal = 0;
        $bError = false;
       
        if(($req_mode == "edit") || ($req_mode == "details")){
            // [#0003-1 under check - 15.07.09] - start
            if($first_time){
                // we need this stupid operation to get a total number of rows in our query
                $from_pos              = $this->SubStrOccurence($temp_sql, "from ", true); /* get last occurence */
                $strlen_start_position = (int)(strlen($temp_sql) - $from_pos);
                $strlen_length         = (int)$from_pos;
                $new_sql = "SELECT count(*) as cnt FROM ".substr($temp_sql, $strlen_start_position, $strlen_length);           
                $this->dataSet = & $this->dbHandler->query($new_sql);       
                if(!$this->dbHandler->isError($this->dataSet)){
                    $row = $this->dataSet->fetchRow();
                    $this->rowsTotal = $row[0];
                }  else $bError = true;             
            }else{
            // [#0003-1 under check - 15.07.09] - end
                $this->dataSet = & $this->dbHandler->query($this->SetSqlByDbType($this->sql, $fsort, $limit));
                $this->rowsTotal = $this->NumberRows();               
            }
        }else{
            $group_by_is     = false;
            $union_is        = false;
            $exists_is       = false;
           
            if(eregi("group by", $temp_sql)) $group_by_is = true;
            if(eregi("union", $temp_sql)) $union_is = true;
            if(eregi("exists ", $temp_sql)){
                /* handle EXIST/NOT EXISTS cases */
                $from_pos = $this->SubStrOccurence($temp_sql, "from "); /* get first occurence */
                $strlen_start_position = (int)$from_pos + 4;
                $strlen_length = (int)(strlen($temp_sql) - $from_pos);
            }else{
                $from_pos = $this->SubStrOccurence($temp_sql, "from ", true); /* get last occurence */
                $strlen_start_position = (int)(strlen($temp_sql) - $from_pos);
                $strlen_length = (int)$from_pos;
            }           

            if($union_is){
                $new_sql = str_ireplace("SELECT ", "SELECT count(*) as union_cnt, ", $temp_sql);
                $this->dataSet = & $this->dbHandler->query($new_sql);                                       
                if(!$this->dbHandler->isError($this->dataSet)){
                    while($row = $this->dataSet->fetchRow()){
                        $this->rowsTotal += $row[0];
                    }
                }    else $bError = true;             
            }else{
                $new_sql = "SELECT ".(($group_by_is) ? "*" : "count(*) as cnt")." FROM ".substr($temp_sql, $strlen_start_position, $strlen_length);
               
                $this->dataSet = & $this->dbHandler->query($new_sql);       
                if(!$this->dbHandler->isError($this->dataSet)){
                    $row = $this->dataSet->fetchRow();
                    if($group_by_is){
                        $this->rowsTotal = $this->dataSet->numRows();
                    }else{
                        $this->rowsTotal = $row[0];
                    }               
                }   else $bError = true;             
            }           
        }
        if ($bError){
           //try another way to get the count.
           $new_sql = "SELECT count(*) as cnt FROM ( $temp_sql ) t";
           
            $this->dataSet = & $this->dbHandler->query($new_sql);       
            if(!$this->dbHandler->isError($this->dataSet)){
                $row = $this->dataSet->fetchRow();
                $this->rowsTotal = $row[0];
            }
        }
    }

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: Pagination links -- what's the trick to get them to show up?

Postby administrator » Oct 10th, '09, 16:54

Thank you!
We'll check this.

BlackLocust
Senior
Senior
Posts: 34
Joined: Sep 16th, '09, 23:17

Re: Pagination links -- what's the trick to get them to show up?

Postby BlackLocust » Nov 10th, '09, 03:17

I'm just now applying the 6.0.4 update. You've done a lot! And thanks for including the patch I provided.

1 problem, though.

In the line
$new_sql = "SELECT count(*) as cnt FROM ( $temp_sql ) t";
You omitted the final alias "t". MySQL requires that a derived table have an alias, and MS SQL Server doesn't complain about it. It needs that alias!

thanks again.

administrator
Site Admin
Site Admin
Posts: 5997
Joined: Jan 7th, '09, 23:18
Contact:

Re: Pagination links -- what's the trick to get them to show up?

Postby administrator » Nov 11th, '09, 11:48

Thank you for your help.
We'll re-check it again and make all needed changes. :)


Return to “Settings: Sorting & Paging”