XPDO, Rowboat & getPages for pagination.

85 Posts

mflammia Reply #1, 11 years, 3 months ago

Hi,

Have been doing a fair amount of searching for how to program pagination, which I have found some very limited examples. One that has been helpful is here http://codingpad.maryspad.com/2011/07/07/modx-revolution-for-complete-beginners-part-11-using-getpage-to-add-pagination/.

I am just basically going to be showing the raw results for search queries on a DB, where results can be thousands of rows and therefore some pagination is required.

I have used the programming below but beleive I might be able to make things simpliar for myself using rowboat http://rtfm.modx.com/display/ADDON/Rowboat.Rowboat and getPages http://rtfm.modx.com/display/ADDON/getPage but I am struggling in how to integrate this together. I'm not sure for example how to call a query using rowboat on my external DB. At the bottom of the rowboat page gives an example of using with getPage also but can't convert it to my use?

Am currently connecting to an external database and adding the package with the following code:

if (!isset($modx->myDB)) {
  
$host = 'IPAdress';
$username = 'Username';
$password = 'Password';
$dbname = 'DBname';
$port = 3306;
$charset = 'utf-8';
     
$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$modx->myDB = new xPDO($dsn, $username, $password);
     
echo $o = ($modx->myDB->connect()) ? 'Connected </br>' : 'Not Connected';
 
  $path = MODX_CORE_PATH . 'components/nhs/';
    if (!$modx->myDB->addPackage('packageName', $path . 'model/','')) {
      print 'There was a problem adding your package.';
    };
 
}

and using the following Snippet to return results on a search query:

if (isset($_POST['search'])) {

    $search = $_POST['search'];
    //$modx->getService('myclass','MyClass', MODX_CORE_PATH . '/components/myclass/model/myclass.class.php');
    //$results = $modx->myclass->myDB->getCollection('LogData',array('diallednumber' => $search));
    if ($results = $modx->myDB->getCollection('LogData', array('diallednumber' => $search))) {
        foreach ($results as $result) {
            $fields = $result->toArray();
            $rows[] = $modx->getChunk('ShowData', $fields);
        }

		//$count['totalcount']= '<p>Total: ' . count($results) . '</p>'; 
		//$modx->getChunk('searchCount',$count);
		echo $modx->myDB->getCount('LogData',$results);
		echo '<p>Total: ' . count($results) . '</p>'; 
		
        $ph['rows'] = implode('',$rows);
        $output = $modx->getChunk('ShowDataOuter',$ph);
 
    } else {
        $output = $noresults;
    }
}
return $output;

The ShowData chunk is as follows:

                <tr>
                  <td>[[+callid]]</td>
                  <td>[[+diallednumber]]</td>
                  <td>[[+calldate]]</td>
		  <td><button type="button" class="btn btn-danger">Delete</button></td>
                </tr>

and the ShowDatOuter is as follows:

<table class="table table-striped">
 <thead>
  <tr>
   <th>Call Id:</th>
   <th>Dialled Number:</th>
   <th>Call Date</th>
   <th>Delete</th>
  </tr>
 </thead>
<tbody>
 [[+rows]]   
</tbody>
</table>

and a SearchFrom chunk:

<form method="post" action="[[~[[*id]]]]">
                <input type="text" name='search' id='search' value="[[+search]]" />
                <input type='submit' name='submit' id='submit' value='submit'/>
</form>

and I pull this altogether in my document template as follows.

[

]
[[!dbconnect]]
[[!dbform? &noresults='No items found.']]
[[$SearchForm]]
[

]

This question has been answered by Bruno17. See the first response.

discuss.answer

5,888 Posts

Bruno17 Reply #2, 11 years, 3 months ago

I think all what you need to get it work together with getPage should be there:

<?php

$limit = $modx->getOption('limit', $scriptProperties, '0');
$offset = $modx->getOption('offset', $scriptProperties, 0);
$totalVar = $modx->getOption('totalVar', $scriptProperties, 'total');

if (isset($_POST['search'])) {

    $search = $_POST['search'];
    //$modx->getService('myclass','MyClass', MODX_CORE_PATH . '/components/myclass/model/myclass.class.php');
    //$results = $modx->myclass->myDB->getCollection('LogData',array('diallednumber' => $search));


    //$count['totalcount']= '<p>Total: ' . count($results) . '</p>';
    //$modx->getChunk('searchCount',$count);

    $c = $modx->myDB->newQuery('LogData');
    $c->where(array('diallednumber' => $search));

    $total = $modx->myDB->getCount('LogData', $c);
    echo '<p>Total: ' . $total . '</p>';

    //set "total" placeholder for getPage
    $modx->setPlaceholder($totalVar, $total);

    //&limit, &offset
    if (!empty($limit)) {
        $c->limit($limit, $offset);
    }

    if ($results = $modx->myDB->getCollection('LogData', $c)) {
        foreach ($results as $result) {
            $fields = $result->toArray();
            $rows[] = $modx->getChunk('ShowData', $fields);
        }


        $ph['rows'] = implode('', $rows);
        $output = $modx->getChunk('ShowDataOuter', $ph);

    } else {
        $output = $noresults;
    }
}
return $output;

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

85 Posts

mflammia Reply #3, 11 years, 3 months ago

After getting so much help I feel a little embaressed asking for more, but I hope once I get to grip of some these fundamentals things will be a bit easier

I can see now how cleverly the snippet is written and now provides all the nesseary elements for pagination like totals, limits and offsets which I really appreciate the assistance with, thanks Bruno17.

Where I am struggling with it now is using it with getPage, which i understand is a wrapper snippet and so has to be used in conjuntion with another snippet like getResources. So my new snippet above which I have called dbform contains a lot of elements including capturing the $post, running the query, looping through the results and using a tpl chunk to display the results.

When looking at examples of getPage / getResource like the one below:

[[!getPage?
  &elementClass=`modSnippet`
  &element=`getResources`
  &showHidden=`1`
  &tpl=`newsTpl`
  &limit=`3`
  &includeContent=`1`
  &includeTVs=`1`
  &processTVs=`1`
  &parents=`[[*id]]`
  &hideContainers=`1`
  &pageLimit=`5`
  &pageNavVar=`page.nav`
]]
[[!+page.nav]]

I can't understand how to break that up to fit my example. Was thinking that &elementClass could equal my dbform snippet, but what about the &tpl as the tpl chunk is already written into the snippet. Maybe I need to break the dbform snippet up, possibly have it run in someway, and use the getPage snippet just to run through the loop. I'm not sure how and where with getPage I make use of the $limit, $offset etc variables. Have tried playing around with some of these ideas but not got anywhere.

It maybe that my understanding is too limited and the advise would simply just to go back to my MODX manual, if so I shall and post back with any answers, otherwise any tips advise would be very grateful.

Many thanks.

5,888 Posts

Bruno17 Reply #4, 11 years, 3 months ago

you should just need this:

[[!getPage?
  &element=`dbform`
  &limit=`5`
  &noresults=`No items found.`
]]
[[!+page.nav]]

getPage will call your snippet (&element=`dbform`) with all the properties you put into the getPage-snippet-tag
Additional getPage will add the calculated offset to the property-list.

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

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

85 Posts

mflammia Reply #5, 11 years, 3 months ago

Can't believe it was that simple. I definitely overcomplicated that. The more I read the harder it seemed.

Thanks Bruno17!

85 Posts

mflammia Reply #6, 11 years, 3 months ago

For anyone who maybe using this code.....

When using this code and clicking on the pagination / nav I would simply get a page with no results.

I realised the reason for this was that I had been rendering my database query table based on a $_POST that no longer existed, so have changed the code as per below to store the $_POST results in a $_SESSION and have the snippet use either / or to render the results.

The snippet:

$limit = $modx->getOption('limit', $scriptProperties,0);
$offset = $modx->getOption('offset', $scriptProperties,0);
$totalVar = $modx->getOption('totalVar', $scriptProperties, 'total');

$output ='';

if ((isset($_POST['search'])) || (isset($_SESSION['searchResult']))) {

    $search = $_POST['search'];
    if (isset($search)) {$_SESSION['searchResult'] = $search;};
    echo '<p>Session search = ' . $_SESSION['searchResult'] . '</p>';

    $c = $modx->myDB->newQuery('LogData');
    $c->where(array('diallednumber' => $_SESSION['searchResult']));
 
    $total = $modx->myDB->getCount('LogData', $c);
    echo '<p>Total records found: ' . $total . '</p>';

    //set "total" placeholder for getPage
    $modx->setPlaceholder($totalVar, $total);
 
    //&limit, &offset
    if (!empty($limit)) {
        $c->limit($limit, $offset);
    }

    //run query and loop through the results
    if ($results = $modx->myDB->getCollection('LogData', $c)) {
        foreach ($results as $result) {
            $fields = $result->toArray();
            $rows[] = $modx->getChunk('ShowData', $fields);
        }
 
        $ph['rows'] = implode('', $rows);
        $output = $modx->getChunk('ShowDataOuter', $ph);
 
    } else {
        $output = $noresults;
    }
}
return $output;

Answered XPDO, Rowboat & getPages for pagination.