summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDerek Allard <derek.allard@ellislab.com>2007-12-18 16:58:03 +0100
committerDerek Allard <derek.allard@ellislab.com>2007-12-18 16:58:03 +0100
commit694b5b8ee6a40b57c91be3c5448bc8f5540d32d8 (patch)
treee79f794d18e31113ef086d76772c49c81b6b3cf1
parent04036f33f6af540ce371b1ebd1878018f0816ed8 (diff)
Added count_all_results() function to Active Record.
-rw-r--r--system/database/DB_active_rec.php41
-rw-r--r--system/database/drivers/mssql/mssql_driver.php9
-rw-r--r--system/database/drivers/mysql/mysql_driver.php32
-rw-r--r--system/database/drivers/mysqli/mysqli_driver.php35
-rw-r--r--system/database/drivers/mysqli/mysqli_utility.php23
-rw-r--r--system/database/drivers/oci8/oci8_driver.php9
-rw-r--r--system/database/drivers/odbc/odbc_driver.php9
-rw-r--r--system/database/drivers/postgre/postgre_driver.php17
-rw-r--r--system/database/drivers/sqlite/sqlite_driver.php9
-rw-r--r--user_guide/changelog.html1
-rw-r--r--user_guide/database/active_record.html13
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>