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;

Should escaped variables be also enclosed in single quotes?

Default is TRUE

Tags

Path where to store cached queries results.

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

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()!

Warnings related to the presence of memcache and redis can be disabled by setting the disable_warnings property.

Default is disk

Tags

Turns debugging on or off.

See also $debug_ajax.

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.

This is also works when called from CLI!

• 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.

This is also works when called from CLI!

• 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, false, true)
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

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

Enables logging of queries done through AJAX requests.

When set to TRUE all AJAX requests sent by your page will be captured and checked for logs generated by Zebra Database.

Note that when this is enabled HTML code will be appended to the result of the AJAX requests!
Therefore, depending on your use-case, having this enabled could break some of your page's functionality.

Default is FALSE

Tags

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

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

Default is TRUE

Tags

Show a link for editing the query in your favorite database manager like (like phpMyAdmin or Adminer).

It should be an HTML anchor (a link) to your favorite database manager where you build the query string according to the requirements of said database manager using the following placeholders:

• %host%
• %user%
• %password%
• %database%
• %port%
• %socket%
• %query%

...were any of those placeholders will be replaced by the values you have passed on to connect, except for the %query% placeholder which will be replaced by the respective query.

Here's how to set up the value for opening the query in Adminer:

1. <a href="path/to/adminer.php?server=%host%:%port%&db=%database%&sql=%query%" target="adminer">edit in adminer</a>

I don't use phpMyAdmin so if you manage to set it up, please share the result with me so I can add it to the docs. Thanks!

Be VERY CAREFUL when using this feature and make sure you do not expose your credentials in the links you build.

Setting it to false will disable the feature.

Default is a link to the documentation on how to set the link up.

Tags

Indicates whether queries should be EXPLAINed in the debugging console.

Default is TRUE

Tags

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

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
2. $db->debug_show_records = 50;

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

By default, if set_charset() method is not called, caching is used and memcache or redis are available but none of them is used, 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

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), false, true);
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;

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).

If you want to stop on errors no matter what, set the value of this property to always and that will raise an exception regardless of the value of the debug property.

If you ever consider using this with its value set to always, I recommend using the debug property with a string value instead.

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

Default is TRUE

Tags

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

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

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

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

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

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

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

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';

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';

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

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

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

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

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

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

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

void __construct ()

Constructor of the class

boolean close ( [ boolean $reset_options = false ] )

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

Arguments

Tags

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

mixed dcount ( string $column , string $table , [ string $where = '' ] , [ mixed $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

Tags

boolean delete ( string $table , [ string $where = '' ] , [ mixed $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

Tags

mixed dlookup ( string $column , string $table , [ string $where = '' ] , [ mixed $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

Tags

mixed dmax ( string $column , string $table , [ string $where = '' ] , [ mixed $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

Tags

mixed dsum ( string $column , string $table , [ string $where = '' ] , [ mixed $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

Tags

mixed 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

Tags

string|boolean 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. // (variables are automatically escaped this way)
14. $db->query('
15.     SELECT
16.         *
17.     FROM
18.         users
19.     WHERE
20.         gender = ?
21. ', array($gender));

Arguments

Tags

mixed fetch_assoc ( [ resource|string $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

Tags

mixed fetch_assoc_all ( [ int|string $index = '' ] , [ resource|string $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

Tags

mixed fetch_obj ( [ resource|string $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

Tags

mixed fetch_obj_all ( [ int|string $index = '' ] , [ resource|string $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

Tags

false|void free_result ( [ resource|string $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

Tags

mixed get_columns ( [ resource|string $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>');
6. print_r($db->get_columns());

Arguments

Tags

object|boolean 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

mixed get_selected_database ()

Returns the name of the currently selected database

Tags

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

Tags

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

Tags

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

Tags

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

Tags

boolean insert ( string $table , mixed $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

Tags

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

Arguments

Tags

mixed insert_id ()

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

Tags

boolean insert_update ( string $table , mixed $columns , [ mixed $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. $db->insert_update(
6.     'table',
7.     array(
8.         'article_id'    =>  $article_id,
9.         'votes'         =>  0,
10.         'date_updated'  =>  'NOW()',
11.     ),
12.     array(
13.         'votes'         =>  'INC(1)',
14.     )
15. );
16.  
17. // when using MySQL functions, the value will be used as it is without being escaped!
18. // while this is ok when using a function without any arguments like NOW(), this may
19. // pose a security concern if the argument(s) come from user input.
20. // in this case we have to escape the value ourselves
21. $db->insert_update(
22.     'table',
23.     array(
24.         'article_id'    =>  'CEIL("' . $db->escape($article_id) . '")',
25.         'votes'         =>  0,
26.         'date_updated'  =>  'NOW()',
27.     ),
28.     array(
29.         'votes'         =>  'INC(1)',
30.     )
31. );

Arguments

Tags

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

Tags

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

Tags

boolean|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

Tags

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. set_time_limit(0);
3.  
4. // allow for more memory to be used by the script
5. ini_set('memory_limit','128M');

Arguments

Tags

mixed query ( string $sql , [ mixed $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(1, 2, 3)));

Arguments

Tags

mixed 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

boolean seek ( integer $row , [ resource|string $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

Tags

mixed select ( mixed $columns , string $table , [ string $where = '' ] , [ mixed $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

Tags

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

Tags

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.

Calling this method will not create a connection to the database but will be deferred instead until a "real" query is run. This is to ensure that the lazy connection feature of the library is preserved.

Arguments

Tags

boolean table_exists ( string $table )

Checks whether a table exists in a database

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

Arguments

Tags

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. $db->transaction_start();
3.  
4. // run queries
5.  
6. // if all the queries since "transaction_start" are valid, write data to the database;
7. // if any of the queries had an error, ignore all queries and treat them as if they never happened
8. $db->transaction_complete();

Tags

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. $db->transaction_start();
3.  
4. // run queries
5.  
6. // if all the queries since "transaction_start" are valid, write data to database;
7. // if any of the queries had an error, ignore all queries and treat them as if they never happened
8. $db->transaction_complete();

Arguments

Tags

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

Tags

boolean update ( string $table , mixed $columns , [ string $where = '' ] , [ mixed $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

Tags