After an INSERT, UPDATE or DELETE query, read this property to get the number of affected rows.

This is a read-only property!

1. // after an "action" query...
2. echo $db->affected_rows;

Path (with trailing slash) where to store cached queries results.

The path must be relative to your working path and not the class' path!

The method to use for caching query results.

Can be either

• disk - query results are cached as files on the disk, at the path specified by cache_path.
• 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.
  For using memcache as caching method, PHP version must be 4.3.3+, must be compiled with the memcache extension and, if memcache_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 where 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 after we've set up everything it is 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:

Sets how many of the records returned by a SELECT query should be shown in the debug console.

1. // show more records
2. $db->console_show_records(50);

Be aware that having this property set to a high number (thousands or more) and having a query that returns 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:

Setting this property to TRUE, will instruct the class to generate debug information for each query it executes.

Debug information can later be reviewed by calling the show_debug_console() method.

Don't forget to set this to FALSE when going live. Generating the debug information consumes a lot of resources and is meant to be used in the development process only!.

Note that not calling show_debug_console() when debug is set to TRUE, will not disable debug information: debug information will still be generated only it will not be shown!

The propper solution is to always use show_debug_console() at the end of your scripts and simply change the state of $debug as show_debug_console() will not display anything if $debug is set to FALSE.

1. $db->debug = false;

Default is TRUE.

An array of IP addresses for which to show the debug console (when calling show_debug_console() and debug is TRUE).

Leaving this an empty array, will display the debug console for everybody.

1. // show the debug console only to specific IPs
2. $db->debugger_ip = array('192.168.0.12', '192.168.0.13');

Default is an empty array.

Tags:

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

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 you don't want to call the method and don't want to see the warning either, set this property to FALSE.

Default is TRUE.

After a SELECT query done through either select() or query() methods, and having set the $calc_rows argument to TRUE, this property would 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 is TRUE but there is no LIMIT applied to the query, this property's value will be equal to returned_rows.

This is a read-only property!

By setting this property to TRUE, the execution of the script will be halted upon an unsuccessful query and the debug console will be displayed, if debug is TRUE and the viewer's IP address is in the debugger_ip array (or $debugger_ip is an empty array).

1. // don't stop execution on critical errors (if possible)
2. $db->halt_on_errors = false;

Default is TRUE.

Tags:

Path (with trailing slash) where to store the log file.

The path is relative to your working directory.

Data is written to the log file when calling the write_log() method.

At the given path the script will attempt to create a file named "log.txt". Remember to grant the appropriate rights to the script!

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

Remember that the library will try to write errors to the system log (if PHP is configured so) only when the debug property is set to FALSE (as when the debug property is set to TRUE the error messages are reported in the debug console);

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 compressed (using zlib) the cached results.

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 memcache is listening for connections.

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

Default is FALSE.

Tags:

The port where memcache 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 debug console will be shown by default.

Clicking on it, will show the full debug console.

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.

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, having notifier_domain in subject.

1. // the email address where to send an email when there are slow queries
2. $db->notifier_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, having notifier_domain in subject.

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

After a SELECT query, read this property to get the number of returned rows.

This is a read-only property!

See found_rows also.

1. // after a select query...
2. echo $db->returned_rows;

Tags:

Zebra_Database __construct ( )

Constructor of the class

Initializes the class' properties

boolean close ( )

Closes the MySQL connection.

Tags:

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

Opens a connection to a MySQL Server and selects a database.

Since the library is using "lazy connection" (it is not actually connecting to the database until the first query is executed) there's no link identifier available when calling this method!

If you need the link identifier use the get_link() method!

If you need 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 debug console
5. $db->connect('host', 'username', 'password', 'database');
6.  
7. //  code goes here
8.  
9. // show the debug console (if enabled)
10. $db->show_debug_console();

Parameters:

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 variables will be mysqli_real_escape_string-ed first
6. $users = $db->dcount('id', 'users', 'gender = ?', array($gender));

Tags:

Parameters:

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 variables will be mysqli_real_escape_string-ed first
6. $db->delete('users', 'gender = ?', array($gender));

Tags:

Parameters:

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 variables will be mysqli_real_escape_string-ed first
6. $result = $db->dlookup('name, surname, age', 'users', 'gender = ?', array($gender));

Tags:

Parameters:

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 variables will be mysqli_real_escape_string-ed first
6. $result = $db->dmax('age', 'users', 'gender = ?', array($gender));

Tags:

Parameters:

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.

Example:

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 variables will be mysqli_real_escape_string-ed first
6. $result = $db->dsum('login_count', 'users', 'gender = ?', array($gender));

Tags:

Parameters:

string escape ( string $string )

Escapes special characters in a string for use in an SQL statement.

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

Tags:

Parameters:

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. // create the database object
2. // run a query
3. $db->query('SELECT * FROM table WHERE criteria = ?', array($criteria));
4.  
5. // iterate through the found records
6. while ($row = $db->fetch_assoc()) {
7.  
8.     // code goes here
9.  
10. }

Tags:

Parameters:

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

Tags:

Parameters:

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

Tags:

Parameters:

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

Tags:

Parameters:

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 index 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());

Tags:

Parameters:

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:

array get_tables ( )

Returns an array with all the tables in the current database.

1. // get all tables from database
2. $tables = get_tables();

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_columns('table_name');

Tags:

Parameters:

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

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

1. // return status information on tables having their name starting with "users"
2. $tables = get_table_status('users%');

Tags:

Parameters:

void halt ( )

Stops the execution of the script at the line where this method is called and, if debug is set to TRUE and the viewer's IP address is in the debugger_ip array (or debugger_ip is an empty array), shows the debug console.

Tags:

string implode ( array $pieces )

Works similarly to PHP's implode() function, with the difference that the "glue" is always the comma and that this method escape()'s arguments.

Useful for escaping an array's values used in SQL statements with the "IN" keyword.

1. $array = array(1,2,3,4);
2.  
3. //  INCORRECT
4.  
5. //  this would not work as the WHERE clause in the SQL statement would become
6. //  WHERE column IN ('1,2,3,4')
7. $db->query('
8.     SELECT
9.         column
10.     FROM
11.         table
12.     WHERE
13.         column IN (?)
14. ', array($array));
15.  
16. //  CORRECT
17.  
18. //  this would work as the WHERE clause in the SQL statement would become
19. //  WHERE column IN ('1','2','3','4') which is what we actually need
20. $db->query('
21.     SELECT
22.         column
23.     FROM
24.         table
25.     WHERE
26.         column IN (' . $db->implode($array) . ')
27. ');

Tags:

Parameters:

boolean insert ( string $table , array $columns , [ boolean $ignore = false] , [ boolean $highlight = false] )

Shorthand for INSERT queries.

When using this method, column names will be enclosed in grave accents " ` " (thus, allowing seamless usage of reserved words as column names) and values will be automatically escaped.

1. $db->insert(
2.     'table',
3.     array(
4.         'column1'   =>  'value1',
5.         'column2'   =>  'value2',
6. ));

Tags:

Parameters:

boolean insert_bulk ( string $table , array $columns , arrays $data , [ boolean $ignore = false] )

Shorthand inserting multiple rows in a single query.

When using this method, column names will be enclosed in grave accents " ` " (thus, allowing seamless usage of reserved words as column names) and values will be automatically escaped.

1. $db->insert_bulk(
2.     'table',
3.     array('column1', 'column2'),
4.     array(
5.         array('value1', 'value2'),
6.         array('value3', 'value4'),
7.         array('value5', 'value6'),
8.         array('value7', 'value8'),
9.         array('value9', 'value10')
10.     )
11. ));

Tags:

Parameters:

mixed insert_id ( )

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

Tags:

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

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

Read more at http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html.

When using this method, column names will be enclosed in grave accents " ` " (thus, allowing seamless usage of reserved words as column names) and values will be automatically escaped.

1. // presuming article_id is a UNIQUE index or PRIMARY KEY, the statement below will insert a new row for given
2. // $article_id and set the "votes" to 0. But, if $article_id is already in the database, increment the votes'
3. // numbers.
4. $db->insert_update(
5.     'table',
6.     array(
7.         'article_id'    =>  $article_id,
8.         'votes'         =>  0,
9.     ),
10.     array(
11.         'votes'         =>  'INC(1)',
12.     )
13. );

Tags:

Parameters:

void language ( string $language )

Sets the language to be used for messages in the debug console.

1. // show messages in the debug console in German
2. $db->language('german');

Tags:

Parameters:

void optimize ( )

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

1. // optimize all tables in the database
2. $db->optimize();

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

Tags:

Parameters:

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 need to return the result of this method in a variable for using it later with a fetch method, like fetch_assoc() or fetch_obj() as all these methods, if called without the resource arguments, 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));

Tags:

Parameters:

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.

Tags:

Parameters:

mixed select ( string $columns , string $table , [ string $where = ''] , [ array $replacements = ''] , [ string $order = ''] , [ mixed $limit = ''] , [ mixed $cache = false] , [ boolean $highlight = false] , [ boolean $calc_rows = 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 seamless usage of reserved words as column names) and values will be automatically escaped.

1. $db->select(
2.     'column1, column2',
3.     'table',
4.     'criteria = ?',
5.     array($criteria)
6. );

Tags:

Parameters:

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 debug console.

Warnings can be disabled by setting the disable_warnings property.

Tags:

Parameters:

void show_debug_console ( [ boolean $return = false] )

Shows the debug console, if debug is TRUE and the viewer's IP address is in the debugger_ip array (or $debugger_ip is an empty array).

This method must be called after all the queries in a script, preferably before </body>!

You should ALWAYS have this method called at the end of your scripts and control whether the debug console will show or not with the debug property.

Parameters:

boolean table_exists ( string $table )

Checks whether a table exists in the current database.

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

Tags:

Parameters:

boolean transaction_complete ( )

Ends a transaction which means that if all the queries since transaction_start() are valid, it writes the data to the database, but if any of the queries had an error, ignore all queries and treat 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 at http://dev.mysql.com/doc/refman/5.0/en/commit.html 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();

Tags:

Parameters:

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

Shorthand for truncating tables.

Truncating a table is quicker then deleting all rows, as stated in the MySQL documentation at http://dev.mysql.com/doc/refman/4.1/en/truncate-table.html. Truncating a table also resets the value of the AUTO INCREMENT column.

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

Tags:

Parameters:

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 seamless usage of reserved words as column names) and values will be automatically escaped.

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

1. $db->update(
2.     'table',
3.     array(
4.         'column1'   =>  'value1',
5.         'column2'   =>  'value2',
6.     ),
7.     'criteria = ?',
8.     array($criteria)
9. );

Tags:

Parameters:

void write_log ( )

Writes debug information to a log.txt log file at log_path if debug is TRUE and the viewer's IP address is in the debugger_ip array (or $debugger_ip is an empty array).

This method must be called after all the queries in a script!

Make sure you're calling it BEFORE show_debug_console() so that you can see in the debug console if writing to the log file was successful or not.

Tags: