SOLVED Return pagination in a ajax return (data)


So I have a successful ajax call and I’m using with the data the ajax return calls.


However I need to figure out how to print the pagination.

// display pagination

<?php $this->widget(‘CLinkPager’, array(

'pages' =&gt; &#036;pages,

)) ?>

My pagination is just not showing at all. I don’t know whats wrong. I’m returning the data (the view) as the return from the ajax call. Its doing it correctly, but no pagination is being shown.

In my controller:

$pages = new CPagination(Marker::model()->countBySql($sql, $params));

            $pages->pageSize = 20;


            $sql .= " LIMIT $criteria->limit OFFSET $criteria->offset";

            $venues = Marker::model()->findAllBySql($sql, $params);

            if ($venues == NULL) {

                echo 'No Result';



            } else {

                // Iterate through the rows, adding XML nodes for each

                foreach ($venues as $location) {

                    $node = $dom->createElement("marker");

                    $newnode = $parnode->appendChild($node);

                    $newnode->setAttribute("name", $location->name);

                    $newnode->setAttribute("address", $location->address);

                    $newnode->setAttribute("lat", $location->lat);

                    $newnode->setAttribute("lng", $location->lng);

                    $newnode->setAttribute("distance", $location->distance);



                $data = $dom->saveXML();

                echo $data;

                $this->renderPartial('locate', array('venues'=>$venues, 'pages'=>$pages));

My View:


$index = 0;

foreach ($venues as $bus) {


                    <div class="snglLst">

                        <div class="floatLeft"><div class="floatBackground"><img src="<?php echo Yii::app()->request->baseUrl; ?>/images/no-preview.gif" alt="<?php echo $bus->name; ?>"></div><div><strong><?php echo round($bus->distance, 1); ?> miles away</strong></div></div>

                        <div class="fL">

                            <div style="float:left; width:200px;">  

                                <div class="mainHead"><a href="<?php echo $bus->bizurl; ?>"><?php echo $bus->name; ?></a></div>

                                <div><?php echo $bus->address0; ?> ( <a id="mapit" href="#" onClick="mapIt('<?php echo $index; ?>');">Map it</a> )</div>

                                <div><?php echo $bus->address1; ?></div>

                                <div><?php echo $bus->address2; ?></div>

                                <div><?php echo $bus->address3; ?></div>

                                <div><strong>Phone  :</strong> <?php echo $bus->display_phone; ?></div>

                                <div><strong>RSVP   : </strong> <span class="green">YES</span> ( <a href="#">Change</a> )</div>


                            <div style="float:left">

                                <div class="mainHead"><a href="#"> </a></div>

                                <div>Reviews: <?php

                    $reviews = (empty($bus->num_reviews)) ? '(' . $bus->num_reviews . ') <a class="statlink" href="#">Be the first to review</a>' : '(' . $bus->num_reviews . ') Write a Review';

                    echo $reviews;


                                <div>Rating: <?php

                    $rating = (empty($bus->rating)) ? 'Has not been rated yet' : $bus->rating;

                    echo $rating;


                                <?php echo CHtml::link('Create Event', array("events/create/$bus->bizurl"), array('class'=>'statlink')); ?>        



                        <div class="fR">

                            <div class="top">Events<br>

                                <span class="green"><?php echo $bus->num_events; ?></span></div>

                            <div class="btm"><a href="#"><img src="<?php echo Yii::app()->request->baseUrl; ?>/images/facebook.png" alt="" width="16" height="16" border="0"></a><a href="#"><img src="<?php echo Yii::app()->request->baseUrl; ?>/images/twitter.png" alt="" width="16" height="16" border="0"></a></div>


                        <div class="clear"></div>






<?php $this->widget('CLinkPager', array('pages' => $pages));?>


I think this has to do with [font="Courier New"]Marker::model()->countBySql($sql, $params)[/font]. As far as I understand from the remaining code, the sql statement in [font="Courier New"]$sql[/font] is used for selecting the data to display. The [font="Courier New"]countBySql()[/font] does not really count the number of rows for a given sql statement, but returns the value of the first column in the first row of the query result (using [font="Courier New"]CDbCommand::queryScalar[/font]). I must say here the function name is misleading.

The function call is probably returning a non numerical value which is interpreted as 0 by the [font="Courier New"]CPagination[/font] constructor. To fix your issue, you should pass a correct sql statement for counting the rows.

here is the actual $sql statement:

$sql = "SELECT $criteria->select FROM tbl_markers"

                    . &quot; WHERE &#036;criteria-&gt;condition &quot;

                    . &quot; HAVING &#036;criteria-&gt;having &quot;

                    . &quot; ORDER by &#036;criteria-&gt;order&quot;;

I thought countBySQL returns the number of rows satisfying the statement:

count() Finds the number of rows satisfying the specified query condition. CActiveRecord

countByAttributes() Finds the number of rows that have the specified attribute values. CActiveRecord

countBySql() Finds the number of rows using the given SQL statement. CActiveRecord

It should work with:

$sql = "SELECT count(*) FROM tbl_markers"

                        . " WHERE $criteria->condition "

                        . " HAVING $criteria->having";

Hi, Haykel

Thank you for your help. I just removed the SQL statement, and did everything with criteria and params. Then I just used count, you were right that countBySQL was not working as it mentioned. Then I extended the class and switched from CHtml::link to CHtml::ajaxlink