From 694b5b8ee6a40b57c91be3c5448bc8f5540d32d8 Mon Sep 17 00:00:00 2001 From: Derek Allard Date: Tue, 18 Dec 2007 15:58:03 +0000 Subject: Added count_all_results() function to Active Record. --- system/database/DB_active_rec.php | 41 +++++++++++++++++++++- system/database/drivers/mssql/mssql_driver.php | 9 ++++- system/database/drivers/mysql/mysql_driver.php | 32 ++++++++++++++--- system/database/drivers/mysqli/mysqli_driver.php | 35 +++++++++++++++--- system/database/drivers/mysqli/mysqli_utility.php | 23 ++++++++++-- system/database/drivers/oci8/oci8_driver.php | 9 ++++- system/database/drivers/odbc/odbc_driver.php | 9 ++++- system/database/drivers/postgre/postgre_driver.php | 17 ++++++--- system/database/drivers/sqlite/sqlite_driver.php | 9 ++++- user_guide/changelog.html | 1 + user_guide/database/active_record.html | 13 ++++++- 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 @@ -565,6 +566,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 * @@ -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 @@ -30,6 +30,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 * @@ -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 @@ -30,6 +30,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, @@ -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 @@ -30,6 +30,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, @@ -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 @@ -30,6 +30,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 * @@ -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 @@ -30,6 +30,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 * @@ -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 @@ -32,6 +32,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 * @@ -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
  • Added reduce_multiples() function to string helper.
  • Added quotes_to_entities() function to string helper.
  • Added form_reset() function to form helper.
    • +
  • Added count_all_results() function to Active Record.
  • Added a language entry for valid_ip validation error.
  • Added a third parameter to Active Record's like() clause to control where the wildcard goes.
  • Moved the safe mode and auth checks for the Email library into the constructor.
    • 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);
    // Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax) +

    $this->db->count_all_results();

    + +

    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:

    +echo $this->db->count_all_results('my_table');
    + +// Produces an integer, like 25
    +
    +$this->db->like('title', 'match');
    +$this->db->from('my_table');
    +echo $this->db->count_all_results();
    +// Produces an integer, like 17     +

    $this->db->count_all();

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

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

    Inserting Data

    -- cgit v1.2.3-24-g4f1b