Right way to count pages

Archives Yii 1.0.x Discussions
1

In this case, how the right way to count pages?



<?php





$keywords=explode(" ",$_GET['q']);


        foreach($keywords as $keyword)


        {


            $sql="SELECT * FROM `content` WHERE `fulltext` LIKE '%$keyword%'";


            $searchList=content::model()->findAllBySql($sql);


            


            $sqt="SELECT count(id) FROM `content` WHERE `fulltext` LIKE '%$keyword%'";


            $total=content::model()->countBySql($sqt);


            


            /*$criteria=new CDbCriteria;


            $criteria->condition='fulltext LIKE %:keyword%';


            $criteria->offset=0;


            $criteria->limit=10;


            $criteria->params=array(':keyword'=>$keyword);*/


            


            $pages=new CPagination(content::model()->count());


            $pages->pageSize=10;


            $pages->applyLimit($sqt); //approach 1





            $pages->applyLimit($criteria); //approach 2          


        }

Approach 1 making error

Quote

PHP Error

Description

Attempt to assign property of non-object

Source File

C:\www\yii104\framework\web\CPagination.php(158)

00146:        else

00147:            unset($params[$this->pageVar]);

00148:        return $controller->createUrl($this->route,$params);

00149:    }

00150:

00151:    /**

00152:      * Applies LIMIT and OFFSET to the specified query criteria.

00153:      * @param CDbCriteria the query criteria that should be applied with the limit

00154:      * @since 1.0.1

00155:      */

00156:    public function applyLimit($criteria)

00157:    {

[glow=red,2,300]00158: $criteria->limit=$this->pageSize;[/glow]

00159:        $criteria->offset=$this->currentPage*$this->pageSize;

00160:    }

00161: }

In approach 2 no error, but give the wrong result, too much number of pages.

2

Approach 2 is more correct but you have to write it like so:

            $pages=new CPagination(content::model()->countBySql($sqt));

Your original code returns the wrong number of pages because it counts total number of rows in table 'content' instead of total results in the fulltext search.

3

I try like this:



<?php





            $pages=new CPagination(content::model()->countBySql($sqt));


            $pages->pageSize=10;


            $pages->applyLimit(content::model()->countBySql($sqt));

But still show error like approach 1

4

Quote

I try like this: 


<?php





            $pages=new CPagination(content::model()->countBySql($sqt));


            $pages->pageSize=10;


            $pages->applyLimit(content::model()->countBySql($sqt));

But still show error like approach 1

I guess the first line should be ($sql not $sqt)

$pages=new CPagination(content::model()->countBySql($sql));
5

It doesn't works too… still error like approach 1

6

Try to use sth like this :



$criteria=new CDbCriteria;


		$criteria->condition='fulltext LIKE %:keyword%';


                $criteria->params=array(':keyword'=>$keyword);


		$count=content::model()->count($criteria);





		$pages=new CPagination($count);


		$pages->pageSize=10;


		$pages->applyLimit($criteria);
7

Thanks, but still error…

Quote

CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext LIKE %'keyword'%' at line 1

Source File

C:\www\yii104\framework\db\CDbCommand.php(285)

00273:                $this->_statement->execute();

00274:            else

00275:                $this->_statement=$this->getConnection()->getPdoInstance()->query($this->getText());

00276:            if($method==='')

00277:                return new CDbDataReader($this);

00278:            $result=$this->_statement->{$method}($mode);

00279:            $this->_statement->closeCursor();

00280:            return $result;

00281:        }

00282:        catch(Exception $e)

00283:        {

00284:            Yii::log('Error in executing SQL: '.$this->getText(),CLogger::LEVEL_ERROR,'system.db.CDbCommand');

[glow=red,2,300]00285: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',[/glow]

00286:                array('{error}'=>$e->getMessage())));

00287:        }

00288:    }

00289: }

8

Your approach 1 is using $sqt. Shouldn't it be $sql?

9

Yeah… I mean is like this…



<?php





            $pages=new CPagination(content::model()->countBySql($sql));


            $pages->pageSize=10;


            $pages->applyLimit(content::model()->countBySql($sqt));

Still error too…

Quote

PHP Error

Description

Attempt to assign property of non-object

Source File

C:\www\yii104\framework\web\CPagination.php(158)

00146:        else

00147:            unset($params[$this->pageVar]);

00148:        return $controller->createUrl($this->route,$params);

00149:    }

00150:

00151:    /**

00152:      * Applies LIMIT and OFFSET to the specified query criteria.

00153:      * @param CDbCriteria the query criteria that should be applied with the limit

00154:      * @since 1.0.1

00155:      */

00156:    public function applyLimit($criteria)

00157:    {

[glow=red,2,300]00158: $criteria->limit=$this->pageSize;[/glow]

00159:        $criteria->offset=$this->currentPage*$this->pageSize;

00160:    }

00161: }

Stack Trace

#0 C:\www\webapp\protected\controllers\SiteController.php(463): CPagination->applyLimit()

#1 C:\www\yii104\framework\web\actions\CInlineAction.php(32): SiteController->actionSearch()

#2 C:\www\yii104\framework\web\CController.php(279): CInlineAction->run()

#3 C:\www\yii104\framework\web\filters\CFilterChain.php(129): SiteController->runAction()

#4 C:\www\yii104\framework\web\filters\CFilter.php(41): CFilterChain->run()

#5 C:\www\yii104\framework\web\CController.php(917): CAccessControlFilter->filter()

#6 C:\www\yii104\framework\web\filters\CInlineFilter.php(59): SiteController->filterAccessControl()

#7 C:\www\yii104\framework\web\filters\CFilterChain.php(126): CInlineFilter->filter()

#8 C:\www\yii104\framework\web\CController.php(262): CFilterChain->run()

#9 C:\www\yii104\framework\web\CController.php(236): SiteController->runActionWithFilters()

#10 C:\www\yii104\framework\web\CWebApplication.php(332): SiteController->run()

#11 C:\www\yii104\framework\web\CWebApplication.php(120): CWebApplication->runController()

#12 C:\www\yii104\framework\base\CApplication.php(133): CWebApplication->processRequest()

#13 C:\www\webapp\index.php(11): CWebApplication->run()

2009-04-10 23:17:30 Apache/2.2.11 (Win32) PHP/5.2.9 Yii Framework/1.0.4

10

The parameter to applyLimit should be a CDbCriteria.

11

Do you mean like this?

Still error like Reply #6…

12

You sql should be "… LIKE :keyword", and then bind ":keyword" with "%".$keyword."%".

13

Do you mean like this?

It doesn't works… CDbException error…

14

Yes, that was what I meant. What is the error now? Could you print out the SQL statement being executed? (turn on logging to find the SQLs).

15

Quote

2009/04/11 19:40:30 [error] [system.db.CDbCommand] Error in executing SQL: SELECT COUNT(*) FROM `content` WHERE fulltext LIKE :keyword LIMIT 10

In web pages showing error like reply #6

16

Do not apply LIMIT when you do counting.

17

Ups, it just for experiment… I removed it… and still got the same error

18

After you remove LIMIT, what is the error message? I don't think there is any syntax error now. Please try to execute the same SQL using some MySQL client (replacing the :keyword placeholder with actual value).

19

Well, the line of trouble maker is…

after I removed it the search result is working again…

But, I try to use keyword "country", and there is have 3 result…

So, I try to set $pages->pageSize=2; to see if limiting list result is working. But it doesn't work… there is still show 3 list result… However the number of pages is correct (Go to page: < Previous 1 2 Next >).

Last condition codes