|
|
There are two components to this solution... (1) A seldom discussed feature of MySQL "Calc Found Rows" which calculates the total number of rows in the table even when you query with the LIMIT clause! For example, if we were to query to get rows 40 thru 60 we would do... $sql="SELECT SQL_CALC_FOUND_ROWS lname,... LIMIT 40,20"; The total rows would be available from MySQL query cache with the following mysql-type php query: $queryCount = mysql_query("SELECT FOUND_ROWS() as rows"); For our example - using ezSQL with TBS - we do the following: $found = $db->get_row("SELECT FOUND_ROWS() as RecCnt"); Giving total Record Count - even with the LIMIT clause: $RecCnt = [var.RecCnt] which we need for the TBS navigation bar plugin (2) Of course you can roll your own... but if you're not using the TBS template engine (TinyButStrong) you really should check it out.. there's nothing comparable for fast development of database driven web applications. |
|
So, for the complete solution the PHP looks like... $TBS = new clsTinyButStrong ; $TBS->LoadTemplate(basename($_SERVER['SCRIPT_NAME'], ".php").".html") ; // check if $PageNum param passed and set default $PageNum = isset($_GET['PageNum'])?(int)$_GET['PageNum']:1; $PageSize = 12; // set the desired rows per page // set the LIMIT offset for mySQL query based on which page we're on now $StartIndex = $PageSize*($PageNum-1); // the MySQL query stmt for the variable LIMIT clause: $sql = "SELECT SQL_CALC_FOUND_ROWS id,lname,fname,email,ph_home FROM mycontacts ORDER BY lname LIMIT $StartIndex,$PageSize"; $query = $db->get_results($sql); // do the ezSQL query using SQL_CALC_FOUND_ROWS trick // from the db query result set the remaing vars for the NAVBAR plugin $found = $db->get_row("SELECT FOUND_ROWS() as rows"); //// Thanks to klepy - he informs me that he adapted this to ms-sql //// He wrote: "Tom, i am using ms-sql and changed your script as follows" //// $found = $db->get_row("SELECT @@rowcount as rows"); //// "which seems to be the equivalent for FOUND_ROWS() in MySql." $RecCnt = $found->rows; $TBS->MergeBlock('blk', $db); $TBS->PlugIn(TBS_NAVBAR,'nv','',$PageNum,$RecCnt,$PageSize) ; $TBS->Show() ; |
|
Here's the completed sample application... |