Zebra_Database Zebra_Database

Class: Zebra_Database

source file: /Zebra_Database.php

A compact (one-file only), lightweight yet feature-rich PHP MySQLi database wrapper providing methods for interacting with MySQL databases that are more secure, powerful and intuitive than PHP's default ones.

Read more here

Author(s):

Version:

  • 2.10.1 (last revision: January 07, 2021)
    See CHANGELOG

License:

Copyright:

  • (c) 2006 - 2021 Stefan Gabos

Class properties

integer $affected_rows

The number of rows affected after running an INSERT, UPDATE, REPLACE or DELETE query.

See the returned_rows property for getting the number of rows returned by SELECT queries.

  1. // update some columns in a table
  2. $db->update('table'array(
  3.     'column_1'  =>  'value 1',
  4.     'column_2'  =>  'value 2',
  5. )'id = ?'array($id));
  6.  
  7. // print the number of affected rows
  8. echo $db->affected_rows;
top

boolean $auto_quote_replacements = true

Should escaped variable be also enclosed in single quotes?

Default is TRUE

Tags
since:   2.9.13
top

string $cache_path

Path where to store cached queries results.

The path must be relative to your working path and not the path of this library!

top

string $caching_method = 'disk'

The method to be used for caching query results.

Can be either:

  • disk - query results are cached as files on the disk at the path specified by cache_path
  • session - query results are cached in the session (use this only for smaller data sets)
    When using this method, the library expects an active session and will trigger a fatal error otherwise!
  • memcache - query results are cached using a memcache server
    When using this method make sure to also set the appropriate values for memcache_host, memcache_port and optionally memcache_compressed prior to calling the connect() method! Failing to do so will disable caching.
    When using this method, PHP must be compiled with the memcache extension and, if memcache_compressed property is set to TRUE, needs to be configured with --with-zlib[=DIR]
  • redis - query results are cached using a redis server
    When using this method make sure to also set the appropriate values for redis_host, redis_port and optionally redis_compressed prior to calling the connect() method! Failing to do so will disable caching.
    When using this method, PHP must be compiled with the redis extension and, if redis_compressed property is set to TRUE, needs to be configured with --with-zlib[=DIR]
  1. // the host where memcache is listening for connections
  2. $db->memcache_host 'localhost';
  3.  
  4. // the port on which memcache is listening for connections
  5. $db->memcache_port 11211;
  6.  
  7. // for this to work, PHP needs to be configured with --with-zlib[=dir] !
  8. // set it to FALSE otherwise
  9. $db->memcache_compressed true;
  10.  
  11. // cache queries using the memcache server
  12. $db->caching_method 'memcache';
  13.  
  14. // only now it is the time to connect
  15. $db->connect(...)

Caching is done on a per-query basis by setting the cache argument when calling some of the library's methods like query(), select(), dcount(), dlookup(), dmax() and dsum()!

Default is disk

Tags
since:   2.7
top

boolean $debug = true

Turns debugging on or off.

The property can take one of the following values:

  • boolean TRUE
    Setting this property to a boolean TRUE will instruct the library to generate debugging information for each query it executes and show the information on the screen when script execution ends.
  • a string
    Setting this property to a string will instruct the library to turn debugging on when the given string is present as argument in the query string (in the URL) and has the value of 1 (i.e ?show_debug=1)

    Useful for turning debugging on on the fly. If you decide to use this in production, make sure to not use an easily guessable value!

    When debugging is turned on this way, a session cookie (a cookie that expires when the browser is closed) will also be set so that the query string argument doesn't need to be present for subsequent requests. Debugging can also be turned off by setting said query string argument to 0 (i.e ?show_debug=0). The cookie's name can be set via the debug_cookie_name property.
  • an array([bool]daily, [bool]hourly, [bool]backtrace)
    Setting this property to an array like above will instruct the library to generate debugging information for each query it executes and write the information to a log file when script execution ends.
  • the value of the first entry (daily) indicates whether the log files should be grouped by days or not; if set to TRUE, log files will have their name in the form of log_ymd.txt, where y, m and d represent the two digit values of year, month and day, respectively.
  • the value of the second entry (hourly) indicates whether the log files should be grouped by hours or not; if set to TRUE, log files will have their name in the form of log_ymd_h.txt, where y, m and d represent the two digit values of year, month and day, respectively, while h represents the two digit value of the hour.
    Note that if this argument is set to TRUE, the first argument will be automatically considered as TRUE
  • the value of the third entry (backtrace) indicates whether backtrace information (where the query was called from) should also be written to the log file.

    The default values for all the entries is FALSE and all are optional, therefore setting the value of this property to an empty array is equivalent of setting it to array(false, false, false)
  • boolean FALSE
    Setting this property to FALSE will instruct the library to not generate debugging information for any of the queries it executes. Even so, if an error occurs the library will try to log the error to PHP's error log file, if your environment is configured to do so !

It is highly recommended to set the value of this property to FALSE on the production environment. Generating the debugging information may consume a lot of resources and is meant to be used only in the development process!

  1. // log debug information instead of showing it on screen
  2. // log everything in one single file (not by day/hour) and also show backtrace information
  3. $db->`debug = array(false,` falsetrue)
  4.  
  5. // disable the generation of debugging information
  6. $db->debug false;
  7.  
  8. // turn debugging on when "debug_db" is found in the query string and has the value "1"
  9. // (meaning that you have to have ?debug_db=1 in your URL)
  10. $db->debug 'debug_db';

Default is TRUE

Tags
since:   turning debugging on/off via a query string is available since 2.10.0
top

array $debugger_ip = array()

An array of IP addresses for which to show the debugging console / write to log file, if the debug property is not set to FALSE.

  1. // show the debugging console only to specific IPs
  2. $db->debugger_ip array('xxx.xxx.xxx.xxx''yyy.yyy.yyy.yyy');

Default is an empty array

Tags
since:   1.0.6
top

If debugging is enabled on the fly via the presence of a query string argument (see the debug property), a cookie is set so that the query string is not required to be present in subsequent requests.

This property sets that cookie's name.

Default value is zebra_db

Tags
since:   2.10.0
top

boolean $debug_show_backtrace = true

Indicates whether backtrace information should be shown in the debugging console.

Default is TRUE

Tags
since:   2.5.9
top

boolean $debug_show_explain = true

Indicates whether queries should be EXPLAINed in the debugging console.

Default is TRUE

Tags
since:   2.5.9
top

mixed $debug_show_globals = true

Indicates which of $_REQUEST, $_POST, $_GET, $_SESSION, $_COOKIE, $_FILES and $_SERVER superglobals should be available in the debugging console, under the globals section.

Can be set to either boolean TRUE or FALSE as a global setting, or as an associative array where each option's visibility can be individually be set, like in the example below:

  1. $db->debug_show_globals(array(
  2.     'request'   =>  true,
  3.     'post'      =>  true,
  4.     'get'       =>  true,
  5.     'session'   =>  true,
  6.     'cookie'    =>  true,
  7.     'files'     =>  true,
  8.     'server'    =>  true,
  9. ));

Default is TRUE

Tags
since:   2.9.14
top

integer $debug_show_records = 20

Sets the number of records returned by SELECT queries to be shown in the debugging console.

Setting this to 0 or FALSE will disable this feature.

  1. // show 50 records

Be aware that having this property set to a high number (hundreds) and having queries that di return that many rows can cause your script to crash due to memory limitations. In this case you should either lower the value of this property or try and set PHP's memory limit higher using:

  1. // set PHP's memory limit to 20 MB
  2. ini_set('memory_limit','20M');

Default is 20

Tags
since:   1.0.9
top

boolean $disable_warnings = false

By default, if set_charset() method is not called, a warning message will be displayed in the debugging console.

The ensure that data is both properly saved and retrieved to and from the database, this method should be called first thing after connecting to a database.

If you don't want to call this method nor do you want to see the warning, set this property to TRUE

Default is FALSE

top

integer $found_rows

After running a SELECT query through select(), query() or query_unbuffered() methods and having the calc_rows argument set to TRUE, this property will contain the number of records that would have been returned if there was no LIMIT applied to the query.

If calc_rows is FALSE, or it is TRUE but there is no LIMIT applied to the query, this property's value will be the same as the value of the returned_rows property.

For unbuffered queries the value of this property will be available only after iterating over all the records with either fetch_assoc() or fetch_obj() methods. Until then, the value will be 0!

  1. // the last argument of the method tells the library to get the total number of records in the table
  2. $db->query('
  3.     SELECT
  4.         *
  5.     FROM
  6.         table
  7.     WHERE
  8.         something = ?
  9.     LIMIT
  10.         10
  11. 'array($somevalue)falsetrue);
  12.  
  13. // prints "10"
  14. // as this is the number of records
  15. // returned by the query
  16. echo $db->returned_rows;
  17.  
  18. // prints "100"
  19. // because we set the "calc_rows" argument of the
  20. // "query" method to TRUE
  21. echo $db->found_rows;
top

boolean $halt_on_errors = true

When the value of this property is set to TRUE, the execution of the script will be halted after the first unsuccessful query and the debugging console will be shown (or debug information will be written to the log file if configured so), if the value of the debug property is not FALSE and the viewer's IP address is in the debugger_ip array (or debugger_ip is an empty array).

  1. // don't stop execution for unsuccessful queries (if possible)
  2. $db->halt_on_errors false;

Default is TRUE

Tags
since:   1.0.5
top

string $log_path = ''

Path where to store the log files when the debug property is set to an array OR a callback function to pass the log information to instead of being written to a file.

The path is relative to your working directory.

Use . (dot) for the current directory instead of an empty string or the log file will be written to the server's root.

If a full path is specified (including an extension) the log file's name will be used from there. Otherwise, the log file's name will be log.txt

At the given path the library will attempt to create a file named "log.txt" (or variations as described here) so the appropriate rights will need to be granted to the script!

IF YOU'RE LOGGING, MAKE SURE YOU HAVE A CRON JOB THAT DELETES THE LOG FILES FROM TIME TO TIME!

Default is "" (an empty string) - log files are created in the root of your server.

If you are using a callback function, the function receives the following arguments:

  • the debug information, as a string, just like it would go into the log file
  • the backtrace information, as a string, just like it would go into the log file - if debug_show_backtrace is set to FALSE, this will be an empty string
top

integer $max_query_time = 10

Time (in seconds) after which a query will be considered as running for too long.

If a query's execution time exceeds this number a notification email will be automatically sent to the address defined by notification_address, having notifier_domain in subject.

  1. // consider queries running for more than 5 seconds as slow and send email
  2. $db->max_query_time 5;

Default is 10

top

boolean $memcache_compressed = false

Setting this property to TRUE will instruct to library to compress the cached results (using zlib).

For this to work, PHP needs to be configured with --with-zlib[=DIR]!

Set this property only if you are using memcache as caching_method.

Default is FALSE

Tags
since:   2.7
top

mixed $memcache_host = false

The host where the memcache server is listening for connections.

Set this property only if you are using memcache as caching_method.

Default is FALSE

Tags
since:   2.7
top

string $memcache_key_prefix = ''

The prefix for the keys used to identify cached queries in memcache. This allows separate caching of the same queries by multiple instances of the libraries, or the same instance handling multiple domains on the same memcache server.

Set this property only if you are using memcache as caching_method.

Default is "" (an empty string)

Tags
since:   2.8.4
top

mixed $memcache_port = false

The port on which the memcache server is listening for connections.

Set this property only if you are using memcache as caching_method.

Default is FALSE

Tags
since:   2.7
top

boolean $minimize_console = true

By setting this property to TRUE a minimized version of the debugging console will be shown by default instead of the full-sized one.

Clicking on it will show the full debugging console.

For quick and easy debugging, setting the highlight argument of a method that has it will result in the debugging console being shown at full size and with the respective query visible for inspecting.

Default is TRUE

Tags
since:   1.0.4
top

string $notification_address = ''

Email address to which notification emails to be sent when a query's execution time exceeds the number of seconds set by max_query_time. The notification email will be automatically sent to the address defined by notification_address and having notifier_domain in subject.

Mails are sent using PHP's mail function.

  1. // the email address where to send an email when there are slow queries
  2. $db->notification_address 'youremail@yourdomain.com';
top

string $notifier_domain = ''

Domain name to be used in the subject of notification emails sent when a query's execution time exceeds the number of seconds set by max_query_time.

If a query's execution time exceeds the number of seconds set by max_query_time, a notification email will be automatically sent to the address defined by notification_address and having notifier_domain in subject.

Mails are sent using PHP's mail function.

  1. // set a domain name so that you'll know where the email comes from
  2. $db->notifier_domain 'yourdomain.com';
top

boolean $redis_compressed = false

Setting this property to TRUE will instruct to library to compress the cached results (using zlib).

For this to work, PHP needs to be configured with --with-zlib[=DIR]!

Set this property only if you are using redis as caching_method.

Default is FALSE

Tags
since:   2.10.0
top

mixed $redis_host = false

The host where the redis server is listening for connections.

Set this property only if you are using redis as caching_method.

Default is FALSE

Tags
since:   2.10.0
top

string $redis_key_prefix = ''

The prefix for the keys used to identify cached queries in redis. This allows separate caching of the same queries by multiple instances of the libraries, or the same instance handling multiple domains on the same redis server.

Set this property only if you are using redis as caching_method.

Default is "" (an empty string)

Tags
since:   2.10.0
top

mixed $redis_port = false

The port on which the redis server is listening for connections.

Set this property only if you are using redis as caching_method.

Default is FALSE

Tags
since:   2.10.0
top

string $resource_path

Path to parent of public folder containing the css and javascript folders.

The path must be relative to your $_SERVER['DOCUMENT_ROOT']

top

integer $returned_rows

After running a SELECT query through select(), query() or query_unbuffered() methods, this property will contain the number of returned rows.

For unbuffered queries the value of this property will be available only after iterating over all the records with either fetch_assoc() or fetch_obj() methods. Until then, the value will be 0!

See found_rows also.

  1. $db->query('
  2.     SELECT
  3.         *
  4.     FROM
  5.         table
  6.     WHERE
  7.         something = ?
  8.     LIMIT
  9.         10
  10. 'array($somevalue));
  11.  
  12. // prints "10"
  13. // as this is the number of records
  14. // returned by the query
  15. echo $db->returned_rows;
Tags
since:   1.0.4
top

Class methods

constructor __construct()

void __construct ()

Constructor of the class

top

destructor __destruct()

void __destruct ()

Frees the memory associated with the last result

Tags
since:   2.8
top

method close()

boolean close ( [ boolean $reset_options = false ] )

Closes the MySQL connection and optionally unsets the connection options previously set with the option() method

Arguments
boolean $reset_options

If set to TRUE the library will also unset the connection options previously set with the option() method.

Default is FALSE

This option was added in 2.9.5

Tags
return:   Returns TRUE on success or FALSE on failure
since:   1.1.0
top

method connect()

void connect ( string $host , string $user , string $password , [ string $database = '' ] , [ string $port = '' ] , [ string $socket = '' ] , [ boolean $connect = false ] )

Opens a connection to a MySQL server and optionally selects a database

Since the library is using lazy connection (it is not actually connecting to the database until the first query is executed), the object representing the connection to the MySQL server is not available at this time. In case you need it before running any queries, use the get_link() method.

If you want the connection to the database to be made right away, set the connect argument to TRUE

  1. // create the database object
  2. $db new Zebra_Database();
  3.  
  4. // notice that we're not doing any error checking. errors will be shown in the debugging console
  5. $db->connect('host''username''password''database');
Arguments
string $host

The address of the MySQL server to connect to (i.e. localhost)

Prepending p: to the host opens a persistent connection

string $user The user name used for authentication when connecting to the MySQL server
string $password The password used for authentication when connecting to the MySQL server
string $database

(Optional) The database to be selected after the connection is established

This can also be set later with the select_database() metho

Default is "" (empty string)

string $port

(Optional) The port number to use when connecting to the MySQL server

Default is "" (empty string) which defaults to the value returned by ini_get('mysqli.default_port')

string $socket

(Optional) The socket or named pipe that should be used.

Specifying the socket parameter will not explicitly determine the type of connection to be used when connecting to the MySQL server. How the connection is made to the MySQL database is determined by the host argument.

Default is "" (empty string) which defaults to the value returned by ini_get('mysqli.default_socket')

boolean $connect

(Optional) Setting this argument to TRUE will force the library to connect to the database right away instead of using a lazy connection and not connecting the database until the first query is run.

Default is FALSE

top

method dcount()

mixed dcount ( string $column , string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ mixed $cache = false ] , [ boolean $highlight = false ] )

Counts the values in a column of a table

  1. // count male users
  2. $male $db->dcount('id''users''gender = "M"');
  3.  
  4. // when working with variables you should use the following syntax
  5. // this way you will stay clear of SQL injections
  6. $users $db->dcount('id''users''gender = ?'array($gender));
Arguments
string $column Name of the column in which to do the counting
string $table

Name of the table containing the column

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

Default is FALSE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:  

Returns the number of counted records or FALSE if no records matching the given criteria (if any) were found. It also returns FALSE if there are no records in the table, or if there was an error.

This method may return boolean FALSE but may also return a non-boolean value which evaluates to FALSE, such as 0. Use the === operator for testing the return value of this method.

top

method delete()

boolean delete ( string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ boolean $highlight = false ] )

Deletes rows from a table

  1. // delete male users
  2. $db->delete('users''gender = "M"');
  3.  
  4. // when working with variables you should use the following syntax
  5. // this way you will stay clear of SQL injections
  6. $db->delete('users''gender = ?'array($gender));
Arguments
string $table

Table from which to delete

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success or FALSE on error
since:   1.0.9
top

method dlookup()

mixed dlookup ( string $column , string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ mixed $cache = false ] , [ boolean $highlight = false ] )

Returns one or more columns from ONE row of a table

  1. // get name, surname and age of all male users
  2. $result $db->dlookup('name, surname, age''users''gender = "M"');
  3.  
  4. // when working with variables you should use the following syntax
  5. // this way you will stay clear of SQL injections
  6. $result $db->dlookup('name, surname, age''users''gender = ?'array($gender));
Arguments
string $column

One or more columns to return data from

If only one column is specified, the returned result will be the specified column's value. If more columns are specified the returned result will be an associative array!

You may use * (asterisk) to return all the columns from the row.

string $table

Name of the table in which to search

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

Default is FALSE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns the found value/values or FALSE if no records matching the given criteria (if any) were found. It also returns FALSE if there are no records in the table, or if there was an error.
top

method dmax()

mixed dmax ( string $column , string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ mixed $cache = false ] , [ boolean $highlight = false ] )

Looks up the maximum value in a column of a table

  1. // get the maximum age of male users
  2. $result $db->dmax('age''users''gender = "M"');
  3.  
  4. // when working with variables you should use the following syntax
  5. // this way you will stay clear of SQL injections
  6. $result $db->dmax('age''users''gender = ?'array($gender));
Arguments
string $column Name of the column in which to search
string $table

Name of table in which to search

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

Default is FALSE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:  

Returns the maximum value in the column or FALSE if no records matching the given criteria (if any) were found. It also returns FALSE if there are no records in the table, or if there was an error.

This method may return boolean FALSE but may also return a non-boolean value which evaluates to FALSE, such as 0. Use the === operator for testing the return value of this method.

top

method dsum()

mixed dsum ( string $column , string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ mixed $cache = false ] , [ boolean $highlight = false ] )

Sums the values in a column of a table

  1. // get the total logins of all male users
  2. $result $db->dsum('login_count''users''gender = "M"');
  3.  
  4. // when working with variables you should use the following syntax
  5. // this way you will stay clear of SQL injections
  6. $result $db->dsum('login_count''users''gender = ?'array($gender));
Arguments
string $column Name of the column in which to sum values
string $table

Name of the table in which to search

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

Default is FALSE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:  

Returns the summed values or FALSE if no records matching the given criteria (if any) were found. It also returns FALSE if there are no records in the table, or if there was an error.

This method may return boolean FALSE but may also return a non-boolean value which evaluates to FALSE, such as 0. Use the === operator for testing the return value of this method.

top

method error()

void error ( [ boolean $return_error_number = false ] )

Returns the description of the last error, or an empty string if no error occurred

In most cases you should not need this method as any errors are shown in the debugging console as long as the debugging is on, or available in PHP's error log file (if your environment is configured to do so) when set to FALSE.

If, for some reasons, none of the above is available, you can use this method to see errors.

  1. $db->query('
  2.     SELECT
  3.         *
  4.     FROM
  5.         users
  6.     WHERE
  7.         invalid_column = ?
  8. 'array($value)) or die($db->error());
Arguments
boolean $return_error_number

Indicates whether the error number should also be returned.

If set to TRUE, the method returns an array in the form of

  1. Array(
  2.     'number'    =>  '1234',
  3.     'message'   =>  'The message',
  4. )

...or an empty string if no error occurred.

Default is FALSE

Tags
since:   2.9.1
top

method escape()

string escape ( string $string )

Escapes special characters in a string making it safe to be used in an MySQL statement, in order to prevent SQL injections

This method also encloses given string in single quotes!

Works even if magic_quotes is ON.

  1. // use the method in a query
  2. // THIS IS NOT THE RECOMMENDED METHOD!
  3. $db->query('
  4.     SELECT
  5.         *
  6.     FROM
  7.         users
  8.     WHERE
  9.         gender = "' $db->escape($gender'"
  10. ');
  11.  
  12. // the recommended method
  13. // (variable are automatically escaped this way)
  14. $db->query('
  15.     SELECT
  16.         *
  17.     FROM
  18.         users
  19.     WHERE
  20.         gender = ?
  21. 'array($gender));
Arguments
string $string String to be quoted and escaped
Tags
return:   Returns the original string enclosed in single quotes and with special characters escaped in order to prevent SQL injections. .
top

method fetch_assoc()

mixed fetch_assoc ( [ resource $resource = '' ] )

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. The data is taken from the resource created by the previous query or from the resource given as argument.

  1. // run a query
  2. $db->query('SELECT * FROM table WHERE criteria = ?'array($criteria));
  3.  
  4. // iterate through the found records
  5. while ($row $db->fetch_assoc()) {
  6.  
  7.     // code goes here
  8.  
  9. }
Arguments
resource $resource

(Optional) Resource to fetch

If not specified, the resource returned by the last run query is used

Tags
return:   Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead, or FALSE if there are no more rows.
top

method fetch_assoc_all()

mixed fetch_assoc_all ( [ string $index = '' ] , [ resource $resource = '' ] )

Returns an associative array containing all the rows from the resource created by the previous query or from the resource given as argument and moves the internal pointer to the end

  1. // run a query
  2. $db->query('SELECT * FROM table WHERE criteria = ?'array($criteria));
  3.  
  4. // fetch all the rows as an associative array
  5. $records $db->fetch_assoc_all();
Arguments
string $index

(Optional) Name of a column containing unique values

If specified, the returned associative array's keys will be the values from this column.

If not specified, returned array will have numerical indexes, starting from 0.

resource $resource

(Optional) Resource to fetch

If not specified, the resource returned by the last run query is used

Tags
return:   Returns an associative array containing all the rows from the resource created by the previous query or from the resource given as argument and moves the internal pointer to the end. Returns FALSE on error.
since:   1.1.2
top

method fetch_obj()

mixed fetch_obj ( [ resource $resource = '' ] )

Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead.

The data is taken from the resource created by the previous query or from the resource given as argument.

  1. // run a query
  2. $db->query('SELECT * FROM table WHERE criteria = ?'array($criteria));
  3.  
  4. // iterate through the found records
  5. while ($row $db->fetch_obj()) {
  6.  
  7.     // code goes here
  8.  
  9. }
Arguments
resource $resource

(Optional) Resource to fetch

If not specified, the resource returned by the last run query is used

Tags
return:   Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead, or FALSE if there are no more rows.
since:   1.0.8
top

method fetch_obj_all()

mixed fetch_obj_all ( [ string $index = '' ] , [ resource $resource = '' ] )

Returns an associative array containing all the rows (as objects) from the resource created by the previous query or from the resource given as argument and moves the internal pointer to the end

  1. // run a query
  2. $db->query('SELECT * FROM table WHERE criteria = ?'array($criteria));
  3.  
  4. // fetch all the rows as an associative array
  5. $records $db->fetch_obj_all();
Arguments
string $index

(Optional) A column name from the records, containing unique values

If specified, the returned associative array's keys will be the values from this column.

If not specified, returned array will have numerical indexes, starting from 0.

resource $resource

(Optional) Resource to fetch

If not specified, the resource returned by the last run query is used

Tags
return:   Returns an associative array containing all the rows (as objects) from the resource created by the previous query or from the resource given as argument and moves the internal pointer to the end. Returns FALSE on error.
since:   1.1.2
top

method free_result()

void free_result ( [ resource $resource = '' ] )

Frees the memory associated with a result

You should always free your result with free_result(), when your result object is not needed anymore

Arguments
resource $resource

(Optional) A valid resource

If not specified, the resource returned by the last run query is used

Tags
since:   2.9.1
top

method get_columns()

mixed get_columns ( [ resource $resource = '' ] )

Returns an array of associative arrays with information about the columns in the MySQL result associated with the specified result identifier

Each entry will have the column's name as key and, associated, an array with the following keys:

  • name
  • table
  • def
  • max_length
  • not_null
  • primary_key
  • multiple_key
  • unique_key
  • numeric
  • blob
  • type
  • unsigned
  • zerofill
  1. // run a query
  2. $db->query('SELECT * FROM table');
  3.  
  4. // print information about the columns
  5. print_r('<pre>');
Arguments
resource $resource

(Optional) Resource to fetch columns information from.

If not specified, the resource returned by the last run query is used

Tags
return:   Returns an associative array with information about the columns in the MySQL result associated with the specified result identifier, or FALSE on error.
since:   2.0
top

identifier get_link ()

Returns the MySQL link identifier associated with the current connection to the MySQL server

Why a separate method? Because the library uses lazy connection (it is not actually connecting to the database until the first query is executed) there's no link identifier available when calling the connect() method.

  1. // create the database object
  2. $db new Zebra_Database();
  3.  
  4. // nothing is returned by this method!
  5. $db->connect('host''username''password''database');
  6.  
  7. // get the link identifier
  8. $link $db->get_link();
Tags
return:   Returns the MySQL link identifier associated with the current connection to the MySQL server.
since:   2.5
top

method get_selected_database()

mixed get_selected_database ()

Returns the name of the currently selected database

Tags
return:   Returns the name of the currently selected database, or FALSE if there's no active connection.
since:   2.8.7
top

method get_tables()

array get_tables ( [ string $database = '' ] )

Returns an array with all the tables in a database

  1. // get all tables from the currently selected database
  2. $tables get_tables();
Arguments
string $database

(Optional) The name of the database from which to return the names of existing tables

If not specified, the tables from the currently selected database will be returned.

This option was added in 2.9.5

Tags
return:   Returns an array with all the tables in the specified database
since:   1.1.2
top

method get_table_columns()

array get_table_columns ( string $table )

Returns information about the columns of a given table as an associative array

  1. // get column information for a table named "table_name"
  2. $db->get_table_columns('table_name');
Arguments
string $table

Name of table to return column information for

May also be given like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

Tags
return:   Returns information about the columns of a given table as an associative array.
since:   2.6
top

method get_table_status()

array get_table_status ( [ string $table = '' ] )

Returns an associative array with a lot of useful information on all or specific tables only

  1. // return status information on tables in the currently
  2. // selected database having their name starting with "users"
  3. $tables get_table_status('users%');
Arguments
string $table

(Optional) Table for which to return information for

May also be given like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

% may be used as a wildcard in table's name to get information about all the tables matching the pattern.

If not specified, information will be returned for all the tables in the currently selected database.

Tags
return:   Returns an associative array with a lot of useful information on all or specific tables only
since:   1.1.2
top

method implode()

string implode ( array $items )

Works similarly to PHP's implode() function with the difference that the glue is always the comma and that all the values are automatically escaped

This was useful for escaping an array's values to be used in an SQL statements having the IN keyword, before adding arrays directly in the replacement array became possible in version 2.8.6

  1. $array array(1,2,3,4);
  2.  
  3. //  this would work as the WHERE clause in the SQL statement would become
  4. //  WHERE column IN ('1','2','3','4')
  5. $db->query('
  6.     SELECT
  7.         column
  8.     FROM
  9.         table
  10.     WHERE
  11.         column IN (' $db->implode($array')
  12. ');
  13.  
  14. //  THE RECOMMENDED WAY OF DOING WHERE-IN CONDITIONS SINCE VERSION 2.8.6
  15.  
  16. $db->query('
  17.     SELECT
  18.         column
  19.     FROM
  20.         table
  21.     WHERE
  22.         column IN (?)
  23. 'array($array));
Arguments
array $items An array with items to be glued together
Tags
return:   Returns the string representation of all the array elements in the same order, escaped and with commas between each element.
since:   2.0
top

method insert()

boolean insert ( string $table , array $columns , [ mixed $update = true ] , [ boolean $highlight = false ] )

Shorthand for INSERT queries with additional IGNORE / ON DUPLICATE KEY support

This method inserts a single row of data. For inserting multiple rows of data see the insert_bulk() method

When using this method column names will be automatically escaped in order to prevent SQL injections and will be enclosed in ` (grave accents), allowing the usage of reserved words.

  1. // simple insert
  2. $db->insert(
  3.     'table',
  4.     array(
  5.         'a' => 1,
  6.         'b' => 2,
  7.         'c' => 3,
  8.     )
  9. );
  10.  
  11. //  would result in
  12. //  INSERT INTO
  13. //      table (`a`, `b`, `c`)
  14. //  VALUES
  15. //      ("1", "2", "3")
  16.  
  17. // ==================================================
  18.  
  19. // ignore inserts if it would create duplicate keys
  20. $db->insert(
  21.     'table',
  22.     array(
  23.         'a' => 1,
  24.         'b' => 2,
  25.         'c' => 3,
  26.     ),
  27.     false
  28. );
  29.  
  30. //  would result in
  31. //  INSERT IGNORE INTO
  32. //      table (`a`, `b`, `c`)
  33. //  VALUES
  34. //      ("1", "2", "3")
  35.  
  36. // ==================================================
  37.  
  38. // update values on duplicate keys
  39. // (let's assume `a` is the key)
  40. $db->insert(
  41.     'table',
  42.     array(
  43.         'a' => 1,
  44.         'b' => 2,
  45.         'c' => 3,
  46.     ),
  47.     array('b''c')
  48. );
  49.  
  50. //  would result in
  51. //  INSERT INTO
  52. //      table (`a`, `b`, `c`)
  53. //  VALUES
  54. //      ("1", "2", "3"),
  55. //  ON DUPLICATE KEY UPDATE
  56. //      `b` = VALUES(`b`),
  57. //      `c` = VALUES(`c`)
  58.  
  59. // ==================================================
  60.  
  61. // when using MySQL functions, the value will be used as it is without being escaped!
  62. // while this is ok when using a function without any arguments like NOW(), this may
  63. // pose a security concern if the argument(s) come from user input.
  64. // in this case we have to escape the value ourselves
  65. $db->insert(
  66.     'table',
  67.     array(
  68.         'column1'       =>  'value1',
  69.         'column2'       =>  'TRIM(UCASE("' $db->escape($value'"))',
  70.         'date_updated'  =>  'NOW()',
  71. ));
Arguments
string $table

Table in which to insert

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

array $columns

An associative array where the array's keys represent the columns names and the array's values represent the values to be inserted in each respective column.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

mixed $update

(Optional) By default, calling this method with this argument set to boolean TRUE or to an empty array will result in a simple insert which will fail in case of duplicate keys.

Setting this argument to boolean FALSE will create an INSERT IGNORE query where when trying to insert a record that would cause a duplicate entry for a key, the row would be skipped instead.

Setting this argument to an array of column names will create a query where, on duplicate key, these given columns will be updated with their respective values from the $values argument.

Alternatively, this argument can also be an associative array where the array's keys represent column names and the array's values represent the values to update the columns' values to if the inserted row would cause a duplicate key.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

For more information see MySQL's INSERT ... ON DUPLICATE KEY syntax.

Default is TRUE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success of FALSE on error.
since:   1.0.9
top

method insert_bulk()

boolean insert_bulk ( string $table , array $columns , array $values , [ mixed $update = true ] , [ boolean $highlight = false ] )

Shorthand for inserting multiple rows in a single query with additional IGNORE / ON DUPLICATE KEY support

When using this method column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

  1. // simple, multi-row insert
  2.     'table',
  3.     array('a''b''c'),
  4.     array(
  5.         array(123),
  6.         array(456),
  7.         array(789),
  8.     )
  9. );
  10.  
  11. //  would result in
  12. //  INSERT INTO
  13. //      table (`a`, `b`, `c`)
  14. //  VALUES
  15. //      ("1", "2", "3"),
  16. //      ("4", "5", "6"),
  17. //      ("7", "8", "9")
  18.  
  19. // ==================================================
  20.  
  21. // ignore inserts if it would create duplicate keys
  22.     'table',
  23.     array('a''b''c'),
  24.     array(
  25.         array(123),
  26.         array(456),
  27.         array(789),
  28.     ),
  29.     false
  30. );
  31.  
  32. //  would result in
  33. //  INSERT IGNORE INTO
  34. //      table (`a`, `b`, `c`)
  35. //  VALUES
  36. //      ("1", "2", "3"),
  37. //      ("4", "5", "6"),
  38. //      ("7", "8", "9")
  39.  
  40. // ==================================================
  41.  
  42. // update values on duplicate keys
  43. // (let's assume `a` is the key)
  44.     'table',
  45.     array('a''b''c'),
  46.     array(
  47.         array(123),
  48.         array(456),
  49.         array(789),
  50.     ),
  51.     array('b''c')
  52. );
  53.  
  54. //  would result in
  55. //  INSERT INTO
  56. //      table (`a`, `b`, `c`)
  57. //  VALUES
  58. //      ("1", "2", "3"),
  59. //      ("4", "5", "6"),
  60. //      ("7", "8", "9")
  61. //  ON DUPLICATE KEY UPDATE
  62. //      `b` = VALUES(`b`),
  63. //      `c` = VALUES(`c`)
  64.  
  65. // ==================================================
  66.  
  67. // update values on duplicate keys, but this time use static values
  68. // (let's assume `a` is the key)
  69.     'table',
  70.     array('a''b''c'),
  71.     array(
  72.         array(123),
  73.         array(456),
  74.         array(789),
  75.     ),
  76.     array('b' => 'foo''c' => 'bar')
  77. );
  78.  
  79. //  would result in
  80. //  INSERT INTO
  81. //      table (`a`, `b`, `c`)
  82. //  VALUES
  83. //      ("1", "2", "3"),
  84. //      ("4", "5", "6"),
  85. //      ("7", "8", "9")
  86. //  ON DUPLICATE KEY UPDATE
  87. //      `b` = "foo",
  88. //      `c` = "bar"
  89.  
  90. // ==================================================
  91.  
  92. // when using MySQL functions, the value will be used as it is without being escaped!
  93. // while this is ok when using a function without any arguments like NOW(), this may
  94. // pose a security concern if the argument(s) come from user input.
  95. // in this case we have to escape the value ourselves
  96.     'table',
  97.     array('a''b''c'),
  98.     array(
  99.         array('1''TRIM(UCASE("' $db->escape($foo'"))''NOW()'),
  100.         array('2''TRIM(UCASE("' $db->escape($bar'"))''NOW()'),
  101.     )
  102. );
Arguments
string $table

Table in which to insert

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

array $columns

An array with columns to insert values into

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

array $values

An array of an unlimited number of arrays with values to be inserted. The arrays must have the same number of items as you in the $columns argument.

Values will be automatically escape()d in order to prevent SQL injections.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

mixed $update

(Optional) By default, calling this method with this argument set to boolean TRUE or to an empty array will result in a simple multi-row insert which will fail in case of duplicate keys.

Setting this argument to boolean FALSE will create an INSERT IGNORE query where when trying to insert a record that would cause a duplicate entry for a key, the row would be skipped instead.

Setting this argument to an array of column names will create a query where, on duplicate key, these given columns will be updated with their respective values from the $values argument.

Alternatively, this argument can also be an associative array where the array's keys represent column names and the array's values represent the values to update the columns' values to if the inserted row would cause a duplicate key.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

For more information see MySQL's INSERT ... ON DUPLICATE KEY syntax.

Default is TRUE

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success of FALSE on error
since:   2.1
top

method insert_id()

mixed insert_id ()

Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query

Tags
return:   Returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query did not generate an AUTO_INCREMENT value, or FALSE if there was no MySQL connection.
since:   1.0.4
top

method insert_update()

boolean insert_update ( string $table , array $columns , [ array $update = array() ] , [ boolean $highlight = false ] )

This method is deprecated since 2.9.12 and will be removed in 3.0. Please use the insert_bulk() method

When using this method, if an inserted row would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the existing row is performed.

Read more here.

When using this method column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

  1. // presuming article_id is a UNIQUE index or PRIMARY KEY, the statement below will
  2. // insert a new row for given $article_id and set the "votes" to 0. But, if $article_id
  3. // is already in the database, increment the votes' numbers.
  4. // also notice that we're using a MySQL function as a value
  5.     'table',
  6.     array(
  7.         'article_id'    =>  $article_id,
  8.         'votes'         =>  0,
  9.         'date_updated'  =>  'NOW()',
  10.     ),
  11.     array(
  12.         'votes'         =>  'INC(1)',
  13.     )
  14. );
  15.  
  16. // when using MySQL functions, the value will be used as it is without being escaped!
  17. // while this is ok when using a function without any arguments like NOW(), this may
  18. // pose a security concern if the argument(s) come from user input.
  19. // in this case we have to escape the value ourselves
  20.     'table',
  21.     array(
  22.         'article_id'    =>  'CEIL("' $db->escape($article_id'")',
  23.         'votes'         =>  0,
  24.         'date_updated'  =>  'NOW()',
  25.     ),
  26.     array(
  27.         'votes'         =>  'INC(1)',
  28.     )
  29. );
Arguments
string $table

Table in which to insert/update

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

array $columns

An associative array where the array's keys represent the columns names and the array's values represent the values to be inserted in each respective column.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

array $update

(Optional) An associative array where the array's keys represent the columns names and the array's values represent the values to update the columns' values to.

This array represents the columns/values to be updated if the inserted row would cause a duplicate value in a UNIQUE index or PRIMARY KEY.

If an empty array is given, the values in $columns will be used.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

A special value may also be used for when a column's value needs to be incremented or decremented. In this case, use INC(value) where value is the value to increase the column's value with. Use INC(-value) to decrease the column's value. See update() for an example.

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

Default is an empty array

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success of FALSE on error
deprecated:   Deprecated starting with 2.9.12, will be removed in 3.0
since:   2.1
top

method language()

void language ( string $language )

Sets the language to be used for the messages in the debugging console and in the log files

  1. // show messages in German
  2. $db->language('german');
Arguments
string $language

The name of the language file from the languages subdirectory

Must be specified without the extension! (i.e. "german" for the german language not "german.php")

Default is "english"

Tags
since:   1.0.6
top

method optimize()

void optimize ( [ string $table = '' ] )

Optimizes all tables that have overhead (unused, lost space) in a database

  1. // optimize all tables in the currently selected database
  2. $db->optimize();
Arguments
string $table

(Optional) Table to optimize

May also be given like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

% may be used as a wildcard in table's name to optimize only the tables matching the pattern.

If not specified, all the tables in the currently selected database will be optimized.

This option was added in 2.9.5

Tags
since:   1.1.2
top

method option()

void option ( mixed $option , [ mixed $value = '' ] )

Sets one or more options that affect the behavior of a connection

See the options that can be set.

Usually, this method must to be called before connecting to a MySQL server because the library uses "lazy connection" and it will not actually connect to the MySQL server until the first query is run, unless the connect() method is called with the connect argument set to TRUE. As such, you may also call this method after the connect() method but only if no queries were run before calling this method.

This method may be called multiple times to set several options.

Arguments
mixed $option One of the valid values described here, or an array of key/value pairs where the keys are valid values described in the previous link.
mixed $value (Optional) When setting a single option this is the value to be associated with that option. When setting an array of options this argument is ignored.
Tags
since:   2.9.5
top

method parse_file()

boolean parse_file ( string $path )

Parses a MySQL dump file (like an export from phpMyAdmin)

If you must parse a very large file and your script crashed due to timeout or because of memory limitations, try the following:

  1. // prevent script timeout
  2.  
  3. // allow for more memory to be used by the script
  4. ini_set('memory_limit','128M');
Arguments
string $path Path to the file to be parsed
Tags
return:   Returns TRUE on success or FALSE on failure
top

method query()

mixed query ( string $sql , [ array $replacements = '' ] , [ mixed $cache = false ] , [ boolean $calc_rows = false ] , [ boolean $highlight = false ] )

Runs a MySQL query

After a SELECT query you can get the number of returned rows by reading the returned_rows property.

After an UPDATE, INSERT or DELETE query you can get the number of affected rows by reading the affected_rows property.

Note that you don't necessarily need to store the result of this method in a variable for using it with a fetch method like fetch_assoc() or fetch_obj(), as all these methods, when called without the resource argument, work on the LAST returned result resource!

  1. // run a query
  2. $db->query('
  3.     SELECT
  4.         *
  5.     FROM
  6.         users
  7.     WHERE
  8.         gender = ?
  9. 'array($gender));
  10.  
  11. // array as replacement, for use with WHERE-IN conditions
  12. $db->query('
  13.     SELECT
  14.         *
  15.     FROM
  16.         users
  17.     WHERE
  18.         gender = ? AND
  19.         id IN (?)
  20. 'array('f'array(123)));
Arguments
string $sql MySQL statement to execute
array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

For unbuffered queries this argument is always FALSE!

Default is FALSE

boolean $calc_rows

(Optional) If query is a SELECT query, this argument is set to TRUE, and there is a LIMIT applied to the query, the value of the found_rows property (after the query was run) will represent the number of records that would have been returned if there was no LIMIT applied to the query.

This is very useful for creating pagination or computing averages. Also, note that this information will be available without running an extra query. Here's how

For unbuffered queries the value of this property will be available only after iterating over all the records with either fetch_assoc() or fetch_obj() methods. Until then, the value will be 0!

Default is FALSE

This argument is deprecated since 2.10.0 and will be removed in 3.0 because the technique used for it was deprecated in MySQL 8.0.17 and is pending removal in a future version. Please use the solution provided in the MySQL documentation instead.

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:  

Returns a resource or an array (if results are taken from the cache) upon success, or FALSE on error.

If query results are taken from cache, the returned result will be a pointer to the actual results of the query!

top

method query_unbuffered()

void query_unbuffered ()

Runs a MySQL unbuffered query.

The method's arguments are the same as for the query() method.

For unbuffered queries the values returned by returned_rows and found_rows properties will be available only after iterating over all the records with either fetch_assoc() or fetch_obj() methods, and will be 0 until then!

Until you iterate over the entire resultset, all subsequent queries will return a Commands out of sync error unless the free_result() method is called

Tags
since:   2.9.4
top

method seek()

boolean seek ( integer $row , [ resource $resource = '' ] )

Moves the internal row pointer of the MySQL result associated with the specified result identifier to the specified row number

The next call to a fetch method like fetch_assoc() or fetch_obj() would return that row.

Arguments
integer $row

The row you want to move the pointer to

$row starts at 0.

$row should be a value in the range from 0 to returned_rows

resource $resource

(Optional) Resource to fetch

If not specified, the resource returned by the last run query is used

Tags
return:   Returns TRUE on success or FALSE on failure
since:   1.1.0
top

method select()

mixed select ( mixed $columns , string $table , [ string $where = '' ] , [ array $replacements = '' ] , [ string $order = '' ] , [ mixed $limit = '' ] , [ mixed $cache = false ] , [ boolean $calc_rows = false ] , [ boolean $highlight = false ] )

Shorthand for simple SELECT queries

For complex queries (using UNION, JOIN, etc) use the query() method.

When using this method column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

  1. $db->select(
  2.     'column1, column2',
  3.     'table',
  4.     'criteria = ?',
  5.     array($criteria)
  6. );
  7.  
  8. // or
  9.  
  10. $db->select(
  11.     array('column1''column2'),
  12.     'table',
  13.     'criteria = ?',
  14.     array($criteria)
  15. );
  16.  
  17. // or
  18.  
  19. $db->select(
  20.     '*',
  21.     'table',
  22.     'criteria = ?',
  23.     array($criteria)
  24. );
Arguments
mixed $columns

A string with comma separated values or an array representing valid column names as used in a SELECT statement.

If given as a string it will be enclosed in grave accents, so make sure you are only using column names and not things like "tablename.*" or MySQL functions!
Use this argument as an array if you want values (like MySQL functions) to be skipped from this process.

You may also use * (asterisk) to select all the columns of a table.

string $table

Table in which to search

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

Note that table name (and database name, if provided) will be enclosed in grave accents ` and thus only one table name must be used!

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

string $order

(Optional) A MySQL ORDER BY clause (without the ORDER BY keyword)

Default is "" (an empty string)

mixed $limit

(Optional) A MySQL LIMIT clause (without the LIMIT keyword)

Default is "" (an empty string)

mixed $cache

(Optional) Instructs the library on whether it should cache the query results or not. Can be either FALSE - no caching - or an integer representing the number of seconds after which the cache will be considered as expired and the query will be executed again.

The caching method is specified through the caching_method property.

Default is FALSE

boolean $calc_rows

(Optional) If this argument is set to TRUE, and there is a LIMIT applied to the query, the value of the found_rows property (after the query was run) will represent the number of records that would have been returned if there was no LIMIT applied to the query.

This is very useful for creating pagination or computing averages. Also, note that this information will be available without running an extra query. Here's how

Default is FALSE

This argument is deprecated since 2.10.0 and will be removed in 3.0 because the technique used for it was deprecated in MySQL 8.0.17 and is pending removal in a future version. Please use the solution provided in the MySQL documentation instead.

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:  

Returns a resource or an array (if results are taken from the cache) upon success, or FALSE on error.

If query results are taken from cache, the returned result will be a pointer to the actual results of the query!

since:   2.0
top

method select_database()

boolean select_database ( string $database )

Selects the default database for queries

  1. // set the default database for queries
  2. $db->select_database('database_name');
Arguments
string $database Name of the database to select as the default database for queries
Tags
return:   Returns TRUE on success or FALSE on failure
since:   2.9.4
top

method set_charset()

void set_charset ( [ string $charset = 'utf8' ] , [ string $collation = 'utf8_general_ci' ] )

Sets MySQL character set and collation

The ensure that data is both properly saved and retrieved from the database you should call this method first thing after connecting to the database.

If this method is not called a warning message will be displayed in the debugging console.

Warnings can be disabled by setting the disable_warnings property.

Arguments
string $charset

(Optional) The character set to be used by the database

Default is utf8

See the list of possible values

string $collation

(Optional) The collation to be used by the database

Default is utf8_general_ci

See the list of possible values

Tags
since:   2.0
top

method table_exists()

boolean table_exists ( string $table )

Checks whether a table exists in a database

  1. // checks whether table "users" exists
  2. table_exists('users');
Arguments
string $table

The name of the table to check if it exists in the database.

May also be given like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

Tags
return:   Returns TRUE if table given as argument exists in the database, or FALSE if it does not
since:   2.3
top

method transaction_complete()

boolean transaction_complete ()

Ends a transaction

This means that if all the queries since transaction_start() are valid, it updates the data in the database, but if any of the queries failed, it ignores all queries and treats them as if they never happened.

  1. // start transactions
  2.  
  3. // run queries
  4.  
  5. // if all the queries since "transaction_start" are valid, write data to the database;
  6. // if any of the queries had an error, ignore all queries and treat them as if they never happened
Tags
return:   Returns TRUE on success or FALSE on error
since:   2.1
top

method transaction_start()

boolean transaction_start ( [ boolean $test_only = false ] )

Starts the transaction system.

Transactions work only with databases that support transaction-safe table types. In MySQL, these are InnoDB or BDB table types. Working with MyISAM tables will not raise any errors but statements will be executed automatically as soon as they are called (just like if there was no transaction).

If you are not familiar with transactions, have a look here and try to find a good online resource for more specific information.

  1. // start transactions
  2.  
  3. // run queries
  4.  
  5. // if all the queries since "transaction_start" are valid, write data to database;
  6. // if any of the queries had an error, ignore all queries and treat them as if they never happened
Arguments
boolean $test_only

(Optional) Starts the transaction system in "test mode" causing the queries to be rolled back (when transaction_complete() is called) - even if all queries are valid

Default is FALSE

Tags
return:   Returns TRUE on success or FALSE on error
since:   2.1
top

method truncate()

boolean truncate ( string $table , [ boolean $highlight = false ] )

Shorthand for TRUNCATE queries

Truncating a table is quicker then deleting all rows, as stated in the MySQL documentation. Truncating a table also resets the value of the AUTO INCREMENT column.

  1. $db->truncate('table');
Arguments
string $table

Table to truncate

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success of FALSE on error
since:   1.0.9
top

method update()

boolean update ( string $table , array $columns , [ string $where = '' ] , [ array $replacements = '' ] , [ boolean $highlight = false ] )

Shorthand for UPDATE queries

When using this method column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

After an update check affected_rows to find out how many rows were affected.

  1. // notice that we're using a MySQL function as a value
  2. $db->update(
  3.     'table',
  4.     array(
  5.         'column1'       =>  'value1',
  6.         'column2'       =>  'value2',
  7.         'date_updated'  =>  'NOW()',
  8.     ),
  9.     'criteria = ?',
  10.     array($criteria)
  11. );
  12.  
  13. // when using MySQL functions, the value will be used as it is without being escaped!
  14. // while this is ok when using a function without any arguments like NOW(), this may
  15. // pose a security concern if the argument(s) come from user input.
  16. // in this case we have to escape the value ourselves
  17. $db->update(
  18.     'table',
  19.     array(
  20.         'column1'       =>  'TRIM(UCASE("' $db->escape($value1'"))',
  21.         'column2'       =>  'value2',
  22.         'date_updated'  =>  'NOW()',
  23.     ),
  24.     'criteria = ?',
  25.     array($criteria)
  26. );
Arguments
string $table

Table in which to update

May also be specified like databasename.tablename if a database was not explicitly selected with the connect() or select_database() methods prior to calling this method.

array $columns

An associative array where the array's keys represent the columns names and the array's values represent the values to be inserted in each respective column.

Column names will be enclosed in grave accents ` (thus allowing the usage of reserved words) and will be automatically escaped in order to prevent SQL injections.

A special value may also be used for when a column's value needs to be incremented or decremented. In this case, use INC(value) where value is the value to increase the column's value with. Use INC(-value) to decrease the column's value:

  1. $db->update(
  2.     'table',
  3.     array(
  4.         'column'    =>  'INC(?)',
  5.     ),
  6.     'criteria = ?',
  7.     array(
  8.         $value,
  9.         $criteria
  10.     )
  11. );

...is equivalent to

  1. $db->query('
  2.     UPDATE
  3.         table
  4.     SET
  5.         column = column + ?
  6.     WHERE
  7.         criteria = ?
  8. 'array($value$criteria));

You may also use any of MySQL's functions as values.

Be aware that when using MySQL functions, the value will be used as it is, without being escaped! While this is ok when using a function without any arguments like NOW(), this may pose a security concern if the argument(s) come from user input. In this case make sure you escape the values yourself!

string $where

(Optional) A MySQL WHERE clause (without the WHERE keyword)

Default is "" (an empty string)

array $replacements

(Optional) An array with as many items as the total parameter markers (?, question marks) in $where. Each item will be automatically escaped and will replace the corresponding ?.
An item may also be an array, case in which each value from the array will be automatically escaped and concatenated with the other elements from the array - useful when using WHERE column IN (?) conditions. See second example here.

Default is "" (an empty string)

boolean $highlight

(Optional) If set to TRUE the debugging console will be opened automatically and the query will be shown - useful for quickly debugging specific queries.

Default is FALSE

Tags
return:   Returns TRUE on success of FALSE on error
since:   1.0.9
top