Use Joomla Pagination?

[Anthony Fuentes]

I am running on Joomla 2.5 and I am trying to figure out how to use Pagination when I am using a MySQL query / loop that I created myself. Can anyone point me in the right direction?

[subtextproductions]

In order to use the JPagination object, you need to know three things: the total number of records in the set, the limit start and the list limit. The last two of these items are the easiest to retrieve.

$app = JFactory::getApplication();

$limit = $app->getUserStateFromRequest('mycomponent.limit', 'limit', $app->getCfg('list_limit'), 'int');

$limitstart = $app->getUserStateFromRequest('mycomponent.limitstart', 'limitstart', 0, 'int');

In the first line of code we use getUserStateFromRequest to check and see if a new value was set by the user. If not, fall back to the list_limit set in the global configuration file. In the second line of code we use the same method to get the limit start, and if none is set, we always start at zero.

To get the total number of records in the set, the Joomla framework usually uses two database queries to get this information. First, send a database query with no limits that is optimized just to get the number of records in the set. Then, execute the query again, but this time with limits. It looks something like this:

$db->setQuery("SELECT COUNT(*) FROM jos_mytable WHERE published = 1");

$total = $db->loadResult();

$db->setQuery("SELECT * FROM jos_mytable WHERE published = 1 LIMIT " . $limitstart . ", " . $limit);

$rows = $db->loadObjectList();

Now that you have a set of records and a total, as well as the limit and limitstart, you're ready to use the JPagination object. All you have to do is create a new object and pass it the appropriate information. Here's some more sample code:

$page = new JPagination($total, $limitstart, $limit);

And, then on your layout template you only need to do this:

echo $page->getListFooter();

I hope you find this helpful. Good luck.

[Douglas Bezerra Possas]

See about getListQuery in your model. This method is responsible for bringing the list of objects from the database. In your view you need to assign a list of objects $ this-> items and soon after assign to $ this-> pagination value of $ this-> getPagination ().

In your view:
function display ($tpl=null){

...

            $this->items        =   $this->get('Items');

            

            $this->state        =   $this->get('State');

            $this->pagination   =   $this->get('Pagination');

...

}

In your model

protected function populateState($ordering = null, $direction = null) {

            

            // Initialise variables.

            $app = JFactory::getApplication();

            // List state information

            $limit = $app->getUserStateFromRequest('global.list.limit', 'limit', $app->getCfg('list_limit'));

            $this->setState('list.limit', $limit);

            $limitstart = JRequest::getVar('limitstart', 0, '', 'int');

            $this->setState('list.start', $limitstart);

            // List state information.

            parent::populateState();

        }

        

        /**

         * Build an SQL query to load the list data.

         *

         * @return    JDatabaseQuery

         * @since    1.6

         */

        protected function getListQuery() {

            // Create a new query object.

            $db = $this->getDbo();

            $query = $db->getQuery(true);

            // Select the required fields from the table.

            $query->select(

                    $this->getState(

                            'list.select', 'a.*'

                    )

            );

            $query->from('`#__jamboroo_product` AS a');

            

            $query->where('a.category_id = \''.JRequest::getVar('category_id',17,'get').'\'');

            

            

                // Filter by published state

                //$published = $this->getState('filter.state');

                //if (is_numeric($published)) {

                //    $query->where('a.is_deleted = '.(int) $published);

                //} else if ($published === '') {

                //    $query->where('(a.is_deleted IN (0, 1))');

                //}

                

            $query->order('a.name');

            return $query;

        }

Sorry for my bad english...

2012/11/1 Anthony Fuentes <antfue...@gmail.com>

I am running on Joomla 2.5 and I am trying to figure out how to use Pagination when I am using a MySQL query / loop that I created myself. Can anyone point me in the right direction?

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/uWKF1xGkoswJ.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

--
Douglas Bezerra Possas - WebModerna

http://www.webmoderna.com.br
Programador PHP
Nilópolis  – RJ – Brasil
Cel.: (21) 8388-6384

Curriculum: http://cvu.vg/dpossas
MSN: con...@webmoderna.com.br
Skype: douglas.possas
Twitter: dpossas

PORTFOLIO
http://www.rezendedarosa.adv.br
http://www.4iprcg.com.br
http://www.bao-bing.com
http://www.shopfrete.com.br

[Anthony Fuentes]

Below is the query I want to add Joomla Pagination too, can someone give me a example using the code below?

<?php

$mysqli = new mysqli($this->host,$this->user,$this->password,$this->db);

$q = 'SELECT * FROM content';

if($result = $mysqli->query($q)){

while($row = $result->fetch_assoc()){

echo $row['id'];

}

}

mysqli_close($mysqli);

?>

[subtextproductions]

First, you can use the Joomla database object. You don't need to call mysql directly. You will learn it is so much easier to do it this way. Instead of this:

$mysqli = new mysqli($this->host,$this->user,$this->password,$this->db);

Use this instead;

$mysqli = JFactory::getDbo();

As I said previously, you need to use two queries, the first one counts, the second one is paginated. Get the $limit and $limitstart variables as shown previously:

$q1 = 'SELECT COUNT(*) FROM content';

$q2 = 'SELECT * FROM content LIMIT ' . $limitstart . ', ' . $limit;

$mysqli->setQuery($q1);

$total = $mysqli->loadResult();

$page = new JPagination($total, $limitstart, $limit);

$mysqli->setQuery($q2);

if($result = $mysqli->loadAssocList()){

foreach($result as $row){

     echo $row['id'];

}

echo $page->getListFooter();

}

[Anthony Fuentes]

How do I set  $limitstart and $limit? I thought the limit came from the first query and $imitstart I could just set to one?

[subtextproductions]

Read carefully. Total comes from the first query. Here are limit and limitstart:

$app = JFactory::getApplication();

$limit  = $app->getUserStateFromRequest('mycomponent.limit', 'limit', $app->getCfg('list_limit'), 'int');

[Anthony Fuentes]

The code below displays the Pagination with correct amount of pages and the first set of IDs, but when I try clicking next, it does nothing...

$limit  = 5;

$limitstart = 1;

$mysqli = JFactory::getDbo();

$q1 = 'SELECT COUNT(*) FROM fwr4e_content';

$q2 = 'SELECT * FROM fwr4e_content LIMIT ' . $limitstart . ', ' . $limit;

[subtextproductions]

$app = JFactory::getApplication();

$limit  = $app->getUserStateFromRequest('mycomponent.limit', 'limit', $app->getCfg('list_limit'), 'int');

$limitstart = $app->getUserStateFromRequest('mycomponent.limitstart', 'limitstart', 0, 'int');

[Anthony Fuentes]

I am not making my own component... 

[subtextproductions]

I never thought you were. Did you take the time to try the code I suggested?

[Anthony Fuentes]

Yes... I tried using the exact code below, and it shows the pagination but when I try to go the first page, if I am on lets say the third page, it does not let me. I tried changing the limitstart to 1 or 0, that did not work..

<?php

$mysqli = JFactory::getDbo();

$app = JFactory::getApplication();

$limit = $app->getUserStateFromRequest('mycomponent.limit', 'limit', $app->getCfg('list_limit'), 'int');

$limitstart = $app->getUserStateFromRequest('mycomponent.limitstart', 'limitstart', 0, 'int');

$q1 = 'SELECT COUNT(*) FROM fwr4e_content';

$q2 = 'SELECT * FROM fwr4e_content LIMIT ' . $limitstart . ', ' . $limit;

$mysqli->setQuery($q1);

$total = $mysqli->loadResult();

$page = new JPagination($total, $limitstart, $limit);

$mysqli->setQuery($q2);

if($result = $mysqli->loadAssocList()){

foreach($result as $row){

    echo $row['id'];

}

echo $page->getListFooter();

}

?>

[subtextproductions]

Geoff,

Take a look at Douglas's post below. He goes into a lot more detail in using the proper MVC architecture to build a custom component. Most of this code belong in your model, but you want to actually get the Pagination object in your view. I use a slightly modified version of the MVC because I don't like to have different views for list and edit. Instead I have one view and than a list layout and an edit layout. You can view the custom component skeleton that I use at http://github.com/subtext/com_subtext.

I hope that's helpful!

[Ganesh Kumar]

May be this might help.

Joomla 3.0 Pagination (Works front-end and back-end)

--------------  Modal Changes  ------------------------- Start
class FooModelFoo extends JModelLegacy {
   
      var $_total = null;
      var $_pagination = null;

  function __construct()
  {
        parent::__construct();
 
        global $option;
        $mainframe = JFactory::getApplication();
        $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');

        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');

        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
  }


    /**
     * Loads the pagination
     */
  function getPagination()
  {
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
  }

    /**
     * Gets the total number of products
     */
  function getTotal()
  {
        // Load the content if it doesn't already exist
        if (empty($this->_total)) {
            $query = $this->_buildQuery();
            $this->_total = $this->_getListCount($query);   
        }
        return $this->_total;
  }
  function getData()
  {
        // if data hasn't already been obtained, load it
        if (empty($this->_data)) {
            $query = $this->_buildQuery();
            $this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));
        }
        return $this->_data;
  }

 
      function _buildQuery()
      {
          $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        $query->select('*')->from('#__content ')->order('id DESC');
        return $query;
      }

}

--------------  Modal Changes  ------------------------- End


--------------  View Changes  ------------------------- Start
        $model = & JModelLegacy::getInstance('Foo', "FooModel");
        $this->pages=$model->getData();
        $this->pagination = $model->getPagination();
--------------  View Changes  ------------------------- End


--------------  Template Changes  ------------------------- Start

1. JHtml::_('behavior.tooltip');  // Top of the page

2. <form method="post" class="form-validate"

3.     Check all box
<th><input type="checkbox" name="toggle" value="" onclick="Joomla.checkAll(this)" /> </th>   
4. Checkboxes and Publish button    (optional)
    foreach($this->pages as $key => $page)
    {
        $checked = JHTML::_('grid.id', $i , $page->id);
        $published = JHTML::_('jgrid.published', $page->status, $i );
    }

5.  Close Form
<input type="hidden" name="boxchecked" value="0" />
<?php echo JHtml::_('form.token'); ?>
</form>

--------------  Template Changes  ------------------------- End

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-general/-/pURoFEsB-HcJ.

To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

--
Ganesh Kumar
Mail: gane...@gmail.com
Mobile: +4915788771699

[Viper]

At the ond of the query add " LIMIT ".$this->getState('list.start').", ".$this->getState('list.limit')

And you question isn't clear.

On Monday, May 20, 2013 2:55:42 PM UTC+3, jamel werfelli wrote:

Urgent !!
joomla how to use pagination on this request ???!!

function searchExpiredPublishedDeal($keyword=null, $categoryId=null, $locationId=null, $sortBy=null)
        {
                $db = JFactory::getDBO();
                                        
            // generate the query
                $query = "        SELECT
                                                d.*
                                        FROM
                                                #__enmasse_deal d
                                        WHERE
                                                d.status NOT LIKE 'Pending' AND
                                          d.published = '1' AND
                                                d.end_at <= '".DatetimeWrapper::getDatetimeOfNow()."' ";
}

[jamel werfelli]

function searchExpiredPublishedDeal($keyword=null, $categoryId=null, $locationId=null, $sortBy=null)

{

$db = JFactory::getDBO();

   // generate the query

$query = " SELECT 

d.* 

FROM 

#__enmasse_deal d

WHERE

d.status NOT LIKE 'Pending' AND

          d.published = '1' AND

d.end_at <= '".DatetimeWrapper::getDatetimeOfNow()."' ";

if($keyword != null)

{

$keyword = EnmasseHelper::escapeSqlLikeSpecialChar($db->getEscaped($keyword));

$query .= " AND d.name like '%$keyword%'";

}

if($categoryId)

$query .=" AND d.id IN (SELECT cat.deal_id FROM #__enmasse_deal_category cat WHERE cat.category_id = $categoryId ) ";

if($locationId)

$query .=" AND d.id IN (SELECT loc.deal_id FROM #__enmasse_deal_location loc WHERE loc.location_id = $locationId ) ";

 

$query .= " ORDER BY id DESC";

//if($categoryId == null)&&($locationId == null)&&($sortBy == null)) $expire="ORDER BY id DESC";  else $expire=""; 

$db->setQuery( $query );

$rows = $db->loadObjectList();

if ($db->getErrorNum())

{

JError::raiseError( 500, $db->getErrorMsg() );

return false;

}

return $rows;  

}

2013/5/21 jamel werfelli <werfell...@gmail.com>

I want to create a pagination with joomla on this request

2013/5/20 Viper <goodla...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.

To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com.

To post to this group, send an email to joomla-de...@googlegroups.com.

Visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

----------------------------------------------------------------------------

WERFELLI Jamel
Développeur web

E-mail: werfell...@gmail.com

Skype: werfelli.jamel
GSM:(+216) 21.004.608

--

----------------------------------------------------------------------------

WERFELLI Jamel
Développeur web

E-mail: werfell...@gmail.com

Skype: werfelli.jamel
GSM:(+216) 21.004.608

[M. Asif]

Hello Sir,

I am at the moment trying to use same pagination in Joomla 3. But I am unable to use setState method it gives error like this " Catchable fatal error: Argument 1 passed to JModelBase::setState() must be an instance of JRegistry, string given, called in ..."

Can you please help in this regard?

Thanks,
Regards

[WP4J]

I seem to recall that you can get the count in the same query as the records so you don't need to have two queries to get total as well as the records...