How to add pagination in joomla custom php code?

Question

Please help me to add pagination (getLimitBox) to my table result. I have for example 100 rows in my table and I want to show 10 page.

<?php
$formid=23;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
    ->select(array('SubmissionId', 'Username', 'FormId','UserId'))
    ->from('#__rsform_submissions')
    ->where ('FormId='.$formid)
    ->order('Username ASC');
//$query->setLimit('10');
$db->setQuery($query);
$results = $db->loadObjectList();
echo '<table>';
    ?>
    <thead>
        <tr> 
            <th>SubmissionId</th> 
            <th>Username</th> 
            <th>UserId</th> 
            <th>FormId</th> 
        </tr>
    </thead> 
    <?php
        foreach ($results as $row)
        {
            echo '<tr>';
                echo '<td>' . $row->SubmissionId . ' </td>';
                echo '<td>' . $row->Username . ' </td>';
                echo '<td>' . $row->UserId . ' </td>';
                echo '<td>' . $row->FormId . ' </td>';
            echo '</tr>';
        }
echo '</table>';
?>

Update I changed my code to:

<?php
$db = JFactory::getDbo();

$lim    = $mainframe->getUserStateFromRequest("$option.limit", 'limit', 10, 'int'); //I guess getUserStateFromRequest is for session or different reasons
$lim0   = JRequest::getVar('limitstart', 0, '', 'int');

$db->setQuery('SELECT SQL_CALC_FOUND_ROWS SubmissionId, Username, FormId,UserId FROM #__rsform_submissions WHERE FormId=23',$lim0, $lim);
$results = $db->loadObjectList();

$db->setQuery('SELECT FOUND_ROWS();');  //no reloading the query! Just asking for total without limit

$pageNav = new JPagination( $db->loadResult(), $lim0, $lim );

echo '<table>';
    ?>
    <thead>
        <tr> 
            <th>SubmissionId</th> 
            <th>Username</th> 
            <th>UserId</th> 
            <th>FormId</th> 
        </tr>
    </thead> 
    <?php
        foreach ($results as $row)
        {
            echo '<tr>';
                echo '<td>' . $row->SubmissionId . ' </td>';
                echo '<td>' . $row->Username . ' </td>';
                echo '<td>' . $row->UserId . ' </td>';
                echo '<td>' . $row->FormId . ' </td>';
            echo '</tr>';
        }
echo '</table>';
echo $pageNav->getListFooter(  ); //Displays a nice footer

?>

It works in custom module next is okay.

However, all not show in front page.

Article only first list start show when click next it is not show anything.

In other article next page displays a 404 error.

Answer 1

You have the instruction in your code, but commented. The query setLimit() function requires two parameters:

• $limit: the number of records to return, and
• $offset: the first record you want to return, zero-based.

This is the signature of the function:

public function setLimit($limit = 0, $offset = 0)

Please take notice that the parameter order is opposite that of mysql:

LIMIT [offset,] row_count;

Then you will need to draw some pagination buttons

echo "<a href=".JRoute::_("index.php?page=1& ...

and so on, and figure out the $offset parameter from that. JRoute omitted for clarity.

I should add that you could also code your model extending ListModel, and then you'd get pagination for free, courtesy of Joomla. To try and sell this better, consider that all of the above would run down to a call like:

echo $this->pagination->getListFooter();

You can find some info on Joomla MVC Docs and sample code on Joomla component tutorial, but ultimately it would be faster to just read how it's done in the core Joomla components, it's quicker, and you always learn something reading Joomla code.