From 6ddb5a17ae1a0a75ca75f846dbb7d3a98f1902a3 Mon Sep 17 00:00:00 2001 From: Derek Allard Date: Tue, 18 Dec 2007 17:22:50 +0000 Subject: Added 'random' as an order_by() option in Active Record. --- system/database/DB_active_rec.php | 11 +++++++++-- system/database/drivers/mssql/mssql_driver.php | 4 ++-- system/database/drivers/mysql/mysql_driver.php | 17 +++++++++-------- system/database/drivers/mysqli/mysqli_driver.php | 7 ++++--- system/database/drivers/oci8/oci8_driver.php | 4 ++-- system/database/drivers/odbc/odbc_driver.php | 5 +++-- system/database/drivers/postgre/postgre_driver.php | 7 ++++--- system/database/drivers/sqlite/sqlite_driver.php | 5 +++-- user_guide/changelog.html | 13 +++++++------ user_guide/database/active_record.html | 3 ++- 10 files changed, 45 insertions(+), 31 deletions(-) diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php index cb134ea6a..e8059ab76 100644 --- a/system/database/DB_active_rec.php +++ b/system/database/DB_active_rec.php @@ -450,7 +450,12 @@ class CI_DB_active_record extends CI_DB_driver { */ function order_by($orderby, $direction = '') { - if (trim($direction) != '') + if (strtolower($direction) == 'random') + { + $orderby = ''; // Random results want or don't need a field name + $direction = $this->_random_keyword; + } + elseif (trim($direction) != '') { $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC'; } @@ -458,6 +463,7 @@ class CI_DB_active_record extends CI_DB_driver { $this->ar_orderby[] = $orderby.$direction; return $this; } + // -------------------------------------------------------------------- /** @@ -469,6 +475,7 @@ class CI_DB_active_record extends CI_DB_driver { { return $this->order_by($orderby, $direction); } + // -------------------------------------------------------------------- /** @@ -583,7 +590,7 @@ class CI_DB_active_record extends CI_DB_driver { $this->from($table); } - $sql = $this->_compile_select($this->count_string); + $sql = $this->_compile_select($this->_count_string); $query = $this->query($sql); $this->_reset_select(); diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php index 051dd5bfe..89610455b 100644 --- a/system/database/drivers/mssql/mssql_driver.php +++ b/system/database/drivers/mssql/mssql_driver.php @@ -35,7 +35,7 @@ class CI_DB_mssql_driver extends CI_DB { * 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 "; + var $_count_string = "SELECT COUNT(*) AS numrows "; /** * Non-persistent database connection @@ -280,7 +280,7 @@ class CI_DB_mssql_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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 69a238d94..2d91c5927 100644 --- a/system/database/drivers/mysql/mysql_driver.php +++ b/system/database/drivers/mysql/mysql_driver.php @@ -30,19 +30,20 @@ */ 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. */ var $delete_hack = TRUE; + + /** + * 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 "; + var $_random_keyword = ' RAND()'; // database specific random keyword /** * Non-persistent database connection @@ -296,7 +297,7 @@ class CI_DB_mysql_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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/mysqli/mysqli_driver.php b/system/database/drivers/mysqli/mysqli_driver.php index 30a256e05..099117097 100644 --- a/system/database/drivers/mysqli/mysqli_driver.php +++ b/system/database/drivers/mysqli/mysqli_driver.php @@ -35,8 +35,9 @@ class CI_DB_mysqli_driver extends CI_DB { * 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 "; - + var $_count_string = "SELECT COUNT(*) AS numrows "; + var $_random_keyword = ' RAND()'; // database specific random keyword + /** * Whether to use the MySQL "delete hack" which allows the number * of affected rows to be shown. Uses a preg_replace when enabled, @@ -289,7 +290,7 @@ class CI_DB_mysqli_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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/oci8/oci8_driver.php b/system/database/drivers/oci8/oci8_driver.php index 0cd04cc58..e6bcefef1 100644 --- a/system/database/drivers/oci8/oci8_driver.php +++ b/system/database/drivers/oci8/oci8_driver.php @@ -48,7 +48,7 @@ class CI_DB_oci8_driver extends CI_DB { * 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 "; + var $_count_string = "SELECT COUNT(1) AS numrows "; // Set "auto commit" by default var $_commit = OCI_COMMIT_ON_SUCCESS; @@ -396,7 +396,7 @@ class CI_DB_oci8_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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 ef26ea1c6..5e1676244 100644 --- a/system/database/drivers/odbc/odbc_driver.php +++ b/system/database/drivers/odbc/odbc_driver.php @@ -35,7 +35,8 @@ class CI_DB_odbc_driver extends CI_DB { * 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 "; + var $_count_string = "SELECT COUNT(*) AS numrows "; + var $_random_keyword = ' RND('.time().')'; // database specific random keyword /** * Non-persistent database connection @@ -258,7 +259,7 @@ class CI_DB_odbc_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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 beaa7931c..e54f9cceb 100644 --- a/system/database/drivers/postgre/postgre_driver.php +++ b/system/database/drivers/postgre/postgre_driver.php @@ -35,7 +35,8 @@ class CI_DB_postgre_driver extends CI_DB { * 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 "; + var $_count_string = "SELECT COUNT(*) AS numrows "; + var $_random_keyword = ' RANDOM()'; // database specific random keyword /** * Non-persistent database connection @@ -285,8 +286,8 @@ class CI_DB_postgre_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string .'FROM "'.$this->dbprefix.$table.'"'); -// original query before count_string was used. Kept for reference + $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) diff --git a/system/database/drivers/sqlite/sqlite_driver.php b/system/database/drivers/sqlite/sqlite_driver.php index 5f86b8ec3..aa6738c03 100644 --- a/system/database/drivers/sqlite/sqlite_driver.php +++ b/system/database/drivers/sqlite/sqlite_driver.php @@ -37,7 +37,8 @@ class CI_DB_sqlite_driver extends CI_DB { * 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 "; + var $_count_string = "SELECT COUNT(*) AS numrows "; + var $_random_keyword = ' Random()'; // database specific random keyword /** * Non-persistent database connection @@ -281,7 +282,7 @@ class CI_DB_sqlite_driver extends CI_DB { if ($table == '') return '0'; - $query = $this->query($this->count_string . "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 5028032b2..612c4db62 100644 --- a/user_guide/changelog.html +++ b/user_guide/changelog.html @@ -66,10 +66,11 @@ Change Log
  • Added $assign_to_controller variable in the main index.php file. Anything that this variable contains will be passed automatically to a controller constructor when initialized.
  • Reorganized the URI and Routes classes for better clarity.
  • Javascript Calendar plugin now uses the months and days from the calendar language file, instead of hard-coded values, internationalizing it.
    • -
  • Removed "rand()" as a listed option from orderby in the Active Record, as it was MySQL only.
    • +
  • Removed "rand()" as a listed option from orderby in the Active Record, as it was MySQL only.
    • +
  • Added 'random' as an order_by() option in Active Record.
  • Added titles to all user manual pages.
  • Added a check for NULL fields in the MySQL database backup utility.
    • -
  • Documented the timezones() function in the Date Helper.
    • +
  • Documented the timezones() function in the Date Helper.
  • Documented unset_userdata in the Session class.
  • Changed the behaviour of custom callbacks so that they no longer trigger the "required" rule.
  • Added a few additional mime type variations for CSV.
    • @@ -79,16 +80,16 @@ Change Log
  • 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.
    • +
  • 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.
  • Fixed a bug in highlight_pharse() that caused an error with slashes.
  • Fixed a bug in word_wrap() of the Text Helper that incorrectly referenced an object.
  • Fixed a bug in the Email library where some timezones were calculated incorrectly.
    • -
  • Fixed a bug in Validation where valid_ip() wasn't called properly.
    • +
  • Fixed a bug in Validation where valid_ip() wasn't called properly.
  • Fixed a bug in the Session library where user agent matching would fail on user agents ending with a space.
  • Fixed a bug in database driver where num_rows property wasn't getting updated.
  • Fixed a bug in captcha calling an invalid PHP function.
    • -
  • Fixed a bug in _html_entity_decode_callback() when 'global_xss_filtering' is enabled.
    • +
  • Fixed a bug in _html_entity_decode_callback() when 'global_xss_filtering' is enabled.
  • Fixed a bug in the cookie helper "set_cookie" function. It was not honoring the config settings.
  • Fixed a bug that was making validation callbacks required even when not set as such.
  • Fixed an example of comma-separated emails in the email library documentation.
    • @@ -103,7 +104,7 @@ Change Log
  • Fixed a typo in the DocBlock comment for unset_userdata() in Session.
  • Fixed a typo in the table library docs.
  • Moved part of the userguide menu javascript to an external file.
    • -
  • Deprecated from Active Record; getwhere() for get_where(); groupby() for group_by(); orderby() for order_by; orwhere() for or_where(); and orlike() for or_like().
    • +
  • Deprecated from Active Record; getwhere() for get_where(); groupby() for group_by(); orderby() for order_by; orwhere() for or_where(); and orlike() for or_like().
    • diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html index d608459e4..739d56a15 100644 --- a/user_guide/database/active_record.html +++ b/user_guide/database/active_record.html @@ -353,7 +353,7 @@ $this->db->or_like('body', $match);

    $this->db->order_by();

    Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. -The second parameter lets you set the direction of the result. Options are asc or desc

    +The second parameter lets you set the direction of the result. Options are asc or desc, or random.

    $this->db->order_by("title", "desc");
    @@ -375,6 +375,7 @@ The second parameter lets you set the direction of the result. Options are

    Note: order_by() was formerly known as orderby(), which has been deprecated.

    +

    Note: random ordering is not currently supported in Orcacle or MSSQL drivers.

    $this->db->limit();

    Lets you limit the number of rows you would like returned by the query:

    -- cgit v1.2.3-24-g4f1b