Pagination help

[misterfade]

I'm trying to query the vbulletin database on a custom table, and paginate the results. From the code I have, for some reason it only returns 40 total results. I'm having trouble with the $total_results part, can anyone help me out?

Thanks.

PHP Code:

<?php
// If current page number, use it, if not, set one.
    if(!isset($_GET['page'])){
    $page = 1;
    } else {
    $page = $_GET['page'];
    }

// Define the number of results per page.
$max_results = 10;

$from = (($page * $max_results) - $max_results);

    $query = $vbulletin->db->query_read("SELECT test1.test_name,
         test1.test_ID, test2.test_name, test2.test_ID 
         WHERE test1.test_ID =
         test2.test_ID ORDER BY test1.test_name ASC LIMIT 
         $from, $max_results");
    
// Figure out the total number of results in DB.
$total_results = $vbulletin->db->query_read("SELECT COUNT(*) 
FROM test1,
test2"); 

    if ($total_results !=0) {

    while($row_test = $vbulletin->db->fetch_array($test1))
    {
      echo $row_test['test1_name'];
    }
    
// Figure out the total number of pages.
$total_pages = ceil($total_results/$max_results);

// Page of page.
    echo "<div align=right>";
    echo "Page $page of $total_pages";

// Build Previous Link.
        $first = 1;
    if ($page != 1) {
        $prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<
</a> ";
echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$first\">
First</a> ] ";
        }

for ($i = 1; $i <= $total_pages; $i++){
if ($page == $i){
echo "<strong>$i</strong> ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
}
}

// Build Next Link.
    if($page < $total_pages){
        $next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">>>
</a> ";
echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$total_pages\">
Last
</a> ] ";
    echo "</div>";
      } else { 
    echo 'Sorry.';
    }
    }
?>

[misterfade]

No one??

[Princeton]

this should get you in the right direction: NOTE: change the QUERY as I changed it to do a test

PHP Code:

$vbulletin->input->clean_gpc('r', 'page', TYPE_UINT);
$page = (empty($vbulletin->GPC['page'])) ? 1 : $vbulletin->GPC['page'];

// Define the number of results per page.
$max_results = 10;

// Figure out the total number of results in DB.
$total_results = $vbulletin->db->query_first("SELECT COUNT(*) AS threads FROM " . TABLE_PREFIX . "thread"); 
echo 'Total Rows To Display:  '. $total_results['threads']. '<br /><br />';

// *********************************************************************************
// initialise some stuff...
$from = ($page - 1) * $max_results;

$query = $vbulletin->db->query_read("
                SELECT threadid, title, dateline
                FROM " . TABLE_PREFIX . "thread AS thread
                ORDER BY dateline ASC 
                LIMIT $from, $max_results");


if ($total_results['threads'] !=0) 
{            
    $count=1;
    while($row = $vbulletin->db->fetch_array($query))
    {
        echo '<div>'. $count .')__ '. $row['title'] . '  (ID: '. $row['threadid'] .')</div>';
    $count++;
    }
    
    // Figure out the total number of pages.
    $total_pages = ceil($total_results['threads']/$max_results);    
    echo "<div align=\"right\">Page $page of $total_pages:  ";  
                  
    // Build Previous Link.
    $first = 1;
    if ($page != 1) 
    {
       $prev = ($page - 1);
       echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$first\">First</a> ] ";
       echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\" style=\"font-size:10px\"><<</a> ";
    }
    
    for ($i = 1; $i <= $total_pages; $i++)
    {
        if ($page == $i)
        {
           echo "<strong>$i</strong> ";
        } 
        else 
        {
           echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
        }
    }
    
    // Build Next Link.
    if ($page < $total_pages)
    {
            $next = ($page + 1);
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\" style=\"font-size:10px\">>></a> ";
            echo "[ <a href=\"".$_SERVER['PHP_SELF']."?page=$total_results[threads]\">Last</a> ]</div>";
    } 
} 


[misterfade]

Thanks for the response. I tried your code and it looks like it's working fine.

Can you explain what this part is doing?

PHP Code:

$vbulletin->input->clean_gpc('r', 'page', TYPE_UINT);
$page = (empty($vbulletin->GPC['page'])) ? 1 : $vbulletin->GPC['page']; 


I don't understand those lines, is that what I have to use with vbulletin? Or is this just for testing purposes?

Thanks again!

[Princeton]

PHP Code:

$vbulletin->input->clean_gpc('r', 'page', TYPE_UINT); 


you should get into the habit of "cleaning" any $_GET, $_REQUEST, and $_POST data .... the above is a vb function that cleans/converts data that will be used

if you study the vb files or any product/plugin found here (vb.org) you will find the above line more frequently

This:

PHP Code:

$page = (empty($vbulletin->GPC['page'])) ? 1 : $vbulletin->GPC['page']; 


does the same thing as

PHP Code:

   if(!isset($_GET['page'])){
    $page = 1;
    } else {
    $page = $_GET['page'];
    } 


The ?: operator is used as a shorthand replacement for the if-then-else conditional statement; the general form is condition ? op1 : op2. If condition is true, the statement evaluates as op1; otherwise, it evaluates as op2.

[misterfade]

Ok thanks for the tip!

I was just trying the code again, and for some reason it's only showing a certain amount of rows. The table that I'm querying should be showing 65 results (rows), but it always stops at 40. I have it set for 10 results per page, and it keeps showing only 4 pages, so 40 rows. I ran my query in PHPMyAdmin and it works fine, so it's got to be the pagination with vbulletin. Any ideas as to why that would be happening?

Even the query from your code, it seems to work, but it shows 5 results per page, 6 pages total. That doesn't seem right.

[Princeton]

the code was meant to be a starting point for you

[misterfade]

Yeah I know but that's the main problem I was having originally, only showing a certain amount of results. I was just wondering if you had an idea as to why it would be doing that.

Thanks though.

[Princeton]

I edited the above code.

try
$from = ($vbulletin->GPC['page'] - 1) * $max_results;

[misterfade]

The new code you posted works great except for one thing. This works:
$from = (($page * $max_results) - $max_results);

Not this one:
$from = ($vbulletin->GPC['page'] - 1) * $max_results;

Thanks for all the help! So glad I got this working.