Added count_all_results() function to Active Record.

11 files changed, 176 insertions, 22 deletions

diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index 6a991a2ed..cb134ea6a 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -540,6 +540,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * and runs the query

 	 *

 	 * @access	public

+	 * @param	string	the table

 	 * @param	string	the limit clause

 	 * @param	string	the offset clause

 	 * @return	object

@@ -566,6 +567,39 @@ class CI_DB_active_record extends CI_DB_driver {
 	// --------------------------------------------------------------------

 

 	/**

+	 * "Count All Results" query

+	 *

+	 * Generates a platform-specific query string that counts all records 

+	 * returned by an Active Record query.

+	 *

+	 * @access	public

+	 * @param	string

+	 * @return	string

+	 */

+	function count_all_results($table = '')

+	{

+		if ($table != '')

+		{

+			$this->from($table);

+		}

+		

+		$sql = $this->_compile_select($this->count_string);

+

+		$query = $this->query($sql);

+		$this->_reset_select();

+	

+		if ($query->num_rows() == 0)

+		{

+			return '0';

+		}

+

+		$row = $query->row();

+		return $row->numrows;

+	}

+

+	// --------------------------------------------------------------------

+

+	/**

 	 * Get_Where

 	 *

 	 * Allows the where clause, limit and offset to be added directly

@@ -806,12 +840,17 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * @access	private

 	 * @return	string

 	 */

-	function _compile_select()

+	function _compile_select($select_override = FALSE)

 	{

 		$sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';

 	

 		$sql .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->ar_select);

 

+		if ($select_override !== FALSE)

+		{

+			$sql = $select_override;

+		}

+

 		if (count($this->ar_from) > 0)

 		{

 			$sql .= "\nFROM ";

diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index 5140dd859..051dd5bfe 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -31,6 +31,13 @@
 class CI_DB_mssql_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+	

+	/**

 	 * Non-persistent database connection

 	 *

 	 * @access	private called by the base class

@@ -273,7 +280,7 @@ class CI_DB_mssql_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 	

-		$query = $this->query("SELECT COUNT(*) AS numrows FROM ".$this->dbprefix.$table);

+		$query = $this->query($this->count_string . "FROM ".$this->dbprefix.$table);

 		

 		if ($query->num_rows() == 0)

 			return '0';

diff --git a/system/database/drivers/mysql/mysql_driver.php b/system/database/drivers/mysql/mysql_driver.php
index 2e1f30f21..69a238d94 100644
--- a/system/database/drivers/mysql/mysql_driver.php
+++ b/system/database/drivers/mysql/mysql_driver.php
@@ -31,6 +31,13 @@
 class CI_DB_mysql_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+

+	/**

 	 * Whether to use the MySQL "delete hack" which allows the number

 	 * of affected rows to be shown. Uses a preg_replace when enabled,

 	 * adding a bit more processing to all queries.

@@ -222,7 +229,17 @@ class CI_DB_mysql_driver extends CI_DB {
 	 */

 	function escape_str($str)	

 	{	

-		if (function_exists('mysql_real_escape_string'))

+		if (is_array($str))

+    	{

+    		foreach($str as $key => $val)

+    		{

+    			$str[$key] = $this->escape_str($val);

+    		}

+    		

+    		return $str;

+    	}

+	

+		if (function_exists('mysql_real_escape_string') AND is_resource($this->conn_id))

 		{

 			return mysql_real_escape_string($str, $this->conn_id);

 		}

@@ -279,7 +296,7 @@ class CI_DB_mysql_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 	

-		$query = $this->query("SELECT COUNT(*) AS numrows FROM `".$this->dbprefix.$table."`");

+		$query = $this->query($this->count_string . "FROM `".$this->dbprefix.$table."`");

 		

 		if ($query->num_rows() == 0)

 			return '0';

@@ -298,9 +315,16 @@ class CI_DB_mysql_driver extends CI_DB {
 	 * @access	private

 	 * @return	string

 	 */

-	function _list_tables()

+	function _list_tables($prefix_limit = FALSE)

 	{

-		return "SHOW TABLES FROM `".$this->database."`";		

+		$sql = "SHOW TABLES FROM `".$this->database."`";	

+		

+		if ($prefix_limit !== FALSE AND $this->_stdprefix != '')

+		{

+			$sql .= " LIKE '".$this->_stdprefix."%'";

+		}

+		

+		return $sql;

 	}

 	

 	// --------------------------------------------------------------------

diff --git a/system/database/drivers/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php
index 4643cb2ac..30a256e05 100644
--- a/system/database/drivers/mysqli/mysqli_driver.php
+++ b/system/database/drivers/mysqli/mysqli_driver.php
@@ -31,6 +31,13 @@
 class CI_DB_mysqli_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+	

+	/**

 	 * Whether to use the MySQL "delete hack" which allows the number

 	 * of affected rows to be shown. Uses a preg_replace when enabled,

 	 * adding a bit more processing to all queries.

@@ -224,8 +231,19 @@ class CI_DB_mysqli_driver extends CI_DB {
 	 * @return	string

 	 */

 	function escape_str($str)	

-	{	

-		return mysqli_real_escape_string($this->conn_id, $str);

+	{

+		if (function_exists('mysqli_real_escape_string') AND is_resource($this->conn_id))

+		{

+			return mysqli_real_escape_string($this->conn_id, $str);

+		}

+		elseif (function_exists('mysql_escape_string'))

+		{

+			return mysql_escape_string($str);

+		}

+		else

+		{

+			return addslashes($str);

+		}

 	}

 		

 	// --------------------------------------------------------------------

@@ -271,7 +289,7 @@ class CI_DB_mysqli_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 	

-		$query = $this->query("SELECT COUNT(*) AS numrows FROM `".$this->dbprefix.$table."`");

+		$query = $this->query($this->count_string . "FROM `".$this->dbprefix.$table."`");

 		

 		if ($query->num_rows() == 0)

 			return '0';

@@ -290,9 +308,16 @@ class CI_DB_mysqli_driver extends CI_DB {
 	 * @access	private

 	 * @return	string

 	 */

-	function _list_tables()

+	function _list_tables($prefix_limit = FALSE)

 	{

-		return "SHOW TABLES FROM `".$this->database."`";		

+		$sql = "SHOW TABLES FROM `".$this->database."`";	

+		

+		if ($prefix_limit !== FALSE AND $this->_stdprefix != '')

+		{

+			$sql .= " LIKE '".$this->_stdprefix."%'";

+		}

+		

+		return $sql;

 	}

 

 	// --------------------------------------------------------------------

diff --git a/system/database/drivers/mysqli/mysqli_utility.php b/system/database/drivers/mysqli/mysqli_utility.php
index 55ffcb738..c904e92d7 100644
--- a/system/database/drivers/mysqli/mysqli_utility.php
+++ b/system/database/drivers/mysqli/mysqli_utility.php
@@ -219,13 +219,30 @@ class CI_DB_mysqli_utility extends CI_DB_utility {
 					$v = str_replace('\\\r', '\r',	$v);

 					$v = str_replace('\\\t', '\t',	$v);

 				

-					// Escape the data if it's not an integer type

-					$val_str .= ($is_int[$i] == FALSE) ? $this->db->escape($v) : $v;

-					$val_str .= ', ';

+					// Is the value NULL?

+					if ($v == NULL)

+					{

+						$val_str .= 'NULL';

+					}

+					else

+					{

+						// Escape the data if it's not an integer

+						if ($is_int[$i] == FALSE)

+						{

+							$val_str .= $this->db->escape($v);

+						}

+						else

+						{

+							$val_str .= $v;

+						}					

+					}					

 					

+					// Append a comma

+					$val_str .= ', ';

 					$i++;

 				}

 				

+				// Remove the comma at the end of the string

 				$val_str = preg_replace( "/, $/" , "" , $val_str);

 								

 				// Build the INSERT string

diff --git a/system/database/drivers/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php
index 99b5ded13..0cd04cc58 100644
--- a/system/database/drivers/oci8/oci8_driver.php
+++ b/system/database/drivers/oci8/oci8_driver.php
@@ -43,6 +43,13 @@
 

 class CI_DB_oci8_driver extends CI_DB {

 

+	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(1) AS numrows ";

+

 	// Set "auto commit" by default

 	var $_commit = OCI_COMMIT_ON_SUCCESS;

 

@@ -389,7 +396,7 @@ class CI_DB_oci8_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 

-		$query = $this->query("SELECT COUNT(1) AS numrows FROM ".$table);

+		$query = $this->query($this->count_string . "FROM ".$table);

 

 		if ($query == FALSE)

 			{

diff --git a/system/database/drivers/odbc/odbc_driver.php b/system/database/drivers/odbc/odbc_driver.php
index 3f42f7c35..ef26ea1c6 100644
--- a/system/database/drivers/odbc/odbc_driver.php
+++ b/system/database/drivers/odbc/odbc_driver.php
@@ -31,6 +31,13 @@
 class CI_DB_odbc_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+

+	/**

 	 * Non-persistent database connection

 	 *

 	 * @access	private called by the base class

@@ -251,7 +258,7 @@ class CI_DB_odbc_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 	

-		$query = $this->query("SELECT COUNT(*) AS numrows FROM `".$this->dbprefix.$table."`");

+		$query = $this->query($this->count_string . "FROM `".$this->dbprefix.$table."`");

 		

 		if ($query->num_rows() == 0)

 			return '0';

diff --git a/system/database/drivers/postgre/postgre_driver.php b/system/database/drivers/postgre/postgre_driver.php
index a8a6ca169..beaa7931c 100644
--- a/system/database/drivers/postgre/postgre_driver.php
+++ b/system/database/drivers/postgre/postgre_driver.php
@@ -31,6 +31,13 @@
 class CI_DB_postgre_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+

+	/**

 	 * Non-persistent database connection

 	 *

 	 * @access	private called by the base class

@@ -277,9 +284,11 @@ class CI_DB_postgre_driver extends CI_DB {
 	{

 		if ($table == '')

 			return '0';

-	

-		$query = $this->query('SELECT COUNT(*) AS numrows FROM "'.$this->dbprefix.$table.'"');

-		

+

+		$query = $this->query($this->count_string .'FROM "'.$this->dbprefix.$table.'"');

+//		original query before count_string was used.  Kept for reference

+//		$query = $this->query('SELECT COUNT(*) AS numrows FROM "'.$this->dbprefix.$table.'"');

+				

 		if ($query->num_rows() == 0)

 			return '0';

 

@@ -315,7 +324,7 @@ class CI_DB_postgre_driver extends CI_DB {
 	 */

 	function _list_columns($table = '')

 	{

-		return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$this->_escape_table($table)."'"; 	

+		return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$this->_escape_table($table)."'";

 	}

 

 	// --------------------------------------------------------------------

diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php
index f8a573b35..5f86b8ec3 100644
--- a/system/database/drivers/sqlite/sqlite_driver.php
+++ b/system/database/drivers/sqlite/sqlite_driver.php
@@ -33,6 +33,13 @@
 class CI_DB_sqlite_driver extends CI_DB {

 

 	/**

+	 * The syntax to count rows is slightly different across different

+	 * database engines, so this string appears in each driver and is

+	 * used for the count_all() and count_all_results() functions.

+	 */

+	var $count_string = "SELECT COUNT(*) AS numrows ";

+

+	/**

 	 * Non-persistent database connection

 	 *

 	 * @access	private called by the base class

@@ -274,7 +281,7 @@ class CI_DB_sqlite_driver extends CI_DB {
 		if ($table == '')

 			return '0';

 	

-		$query = $this->query("SELECT COUNT(*) AS numrows FROM `".$this->dbprefix.$table."`");

+		$query = $this->query($this->count_string . "FROM `".$this->dbprefix.$table."`");

 		

 		if ($query->num_rows() == 0)

 			return '0';

diff --git a/user_guide/changelog.html b/user_guide/changelog.html
index a47d3a019..5028032b2 100644
--- a/user_guide/changelog.html
+++ b/user_guide/changelog.html
@@ -77,6 +77,7 @@ Change Log
     <li>Added <kbd>reduce_multiples()</kbd> function to <a href="./helpers/string_helper.html">string helper</a>.</li>

     <li>Added <kbd>quotes_to_entities()</kbd> function to <a href="./helpers/string_helper.html">string helper</a>.</li>

     <li>Added <kbd>form_reset()</kbd> function to <a href="./helpers/form_helper.html">form helper</a>.</li>

+    <li>Added <kbd>count_all_results()</kbd> function to <a href="./database/active_record.html">Active Record</a>.</li>

     <li>Added a language entry for valid_ip validation error.</li>

     <li>Added a third parameter to Active Record's like() clause to control where the wildcard goes. </li>

     <li>Moved the safe mode and auth checks for the Email library into the constructor. </li>

diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html
index 1c4783ab9..d608459e4 100644
--- a/user_guide/database/active_record.html
+++ b/user_guide/database/active_record.html
@@ -392,6 +392,18 @@ $this->db->limit(10, 20);<br />
 // Produces: LIMIT 20, 10 (in MySQL.  Other databases have slightly different syntax)</code>

 

 

+<h2>$this->db->count_all_results();</h2>

+

+<p>Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(),  or_where(), like(), or_like(), etc. Example:</p>

+<code>echo $this->db->count_all_results('<var>my_table</var>');<br />

+

+// Produces an integer, like 25<br />

+<br />

+$this-&gt;db-&gt;like('title', 'match');<br />

+$this-&gt;db-&gt;from('<var>my_table</var>');<br />

+echo $this-&gt;db-&gt;count_all_results();<br />

+// Produces an integer, like 17 </code>

+

 <h2>$this->db->count_all();</h2>

 

 <p>Permits you to determine the number of rows in a particular table.  Submit the table name in the first parameter. Example:</p>

@@ -401,7 +413,6 @@ $this->db->limit(10, 20);<br />
 // Produces an integer, like 25</code>

 

 

-

 	

 <a name="insert">&nbsp;</a>

 <h1>Inserting Data</h1>