Jump to content

Developer Documentation

Accessing the database

Accessing the database to store and query data is a necessity of nearly all applications and plugins that integrate with the Invision Community software. The \IPS\Db class handles database connections, and extends the default mysqli class in the PHP core library.

Connecting to the database

The default database connection (denoted by the connection details in conf_global.php) can be established automatically by calling \IPS\Db::i(). If a connection has not yet been established, it will be done so immediately on the fly when this method is called. The default connection uses utf8 (or utf8mb4 depending upon your configuration) and all database tables and columns must be configured as utf8. This is generally handled automatically by the Invision Community software, but is an important note to keep in mind.

If you need to establish a connection to a remote database, this can be done by passing parameters to the i() method of \IPS\Db. The first parameter is an arbitrary string connection identifier, and the second parameter is an array with the connection settings. 

$connection = \IPS\Db::i( 'external', array(
    'sql_host'        => 'localhost',
    'sql_user'        => 'username',
    'sql_pass'        => 'password',
    'sql_database'    => 'database_name',
    'sql_port'        => 3306,
    'sql_socket'    => '/var/lib/mysql.sock',
    'sql_utf8mb4'    => true,
) );

You only need to supply the parameters that your connection requires. You can also support read/write separation automatically by passing the same parameters a second time with the prefix "sql_read_" instead of just "sql_", pointing to your read-only MySQL instance.

Selecting data

Selecting data from the database is one of the more common needs when interacting with the database. 

	/**
	 * Build SELECT statement
	 *
	 * @param	array|string		$columns	The columns (as an array) to select or an expression
	 * @param	array|string		$table		The table to select from. Either (string) table_name or (array) ( name, alias ) or \IPS\Db\Select object
	 * @param	array|string|NULL	$where		WHERE clause - see \IPS\Db::compileWhereClause() for details
	 * @param	string|NULL			$order		ORDER BY clause
	 * @param	array|int			$limit		Rows to fetch or array( offset, limit )
	 * @param	string|NULL|array	$group		Column(s) to GROUP BY
	 * @param	array|string|NULL	$having		HAVING clause (same format as WHERE clause)
	 * @param	int					$flags		Bitwise flags
	 *	@li	\IPS\Db::SELECT_DISTINCT				Will use SELECT DISTINCT
	 *	@li	\IPS\Db::SELECT_MULTIDIMENSIONAL_JOINS	Will return the result as a multidimensional array, with each joined table separately
	 *	@li	\IPS\Db::SELECT_FROM_WRITE_SERVER		Will send the query to the write server (if read/write separation is enabled)
	 * @return	\IPS\Db\Select
	 *
	 */
	public function select( $columns=NULL, $table, $where=NULL, $order=NULL, $limit=NULL, $group=NULL, $having=NULL, $flags=0 )

You can call the select() method to perform a SELECT database query.  Doing so returns an \IPS\Db\Select object which allows you to further refine the SELECT query. For instance, there are methods in this class to force a specific index to be used, to join other database tables, and to specify which key/value fields to use for results. 

// Get the select object
$select = \IPS\Db::i()->select( '*', 'some_table', array( 'field=?', 1 ), 'some_column DESC', array( 0, 10 ) );

// Force a specific index to be used for the query
$select = $select->forceIndex( 'some_index' );

// Join another table on
$select = $select->join( 'other_table_name', 'other_table_name.column=some_table.column', 'LEFT' );

// Now, get the number of results returned
$results = count( $select );

// Tell the iterator that keys should be 'column_a' and values should be 'column_b'
$select = $select->setKeyField( 'column_a' )->setValueField( 'column_b' );

// Finally, loop over the results
foreach( $select as $columnA => $columnB )
{
    print $columnA . ': ' . $columnB . '<br>';
}

There are some important things to note here.

  • The WHERE clause accepts many different formats you should be aware of. You can pass a string as the WHERE clause
    some_column='some value'
    or you can pass an array with the first element the WHERE clause using ? as placeholders for values and then each placeholder replaced with the subsequent array entries. This uses prepared statements in MySQL to help avoid SQL injection concerns and is recommended.
    array( 'some_column=? OR some_column=?', 'first value', 'second value' )
    or, finally, you can pass an array of clauses which will be AND joined together
    array( array( 'some_column=?', 'test value' ), array( 'other_column=?', 1 ) )
  • You can call setKeyField() without calling setValueField(). Instead of the value being a string in this case, it will simply be an array of all columns selected (or if only one column is selected, the value will be a string with that column's value). Note that you must select the columns you wish to use for setKeyField and/or setValueField.
  • The method definition for join() requires the first parameter to be the name of the table you wish to join, then the join 'on' clause, followed by the type of join to use (defaulting to LEFT). You can also specify a using clause for the join as the fourth parameter, if necessary. To control which columns to select, you will need to adjust the first parameter of the original SELECT clause (if you pass '*' MySQL will return all columns from all tables selected from and/or joined).
  • \IPS\Db\Select implements both Iterator and Countable. This means you can treat it as an array and use a foreach() loop to loop over the results, and you can call count() against the object to get the number of results. Be aware, however, that count() by default only returns the number of results that the query returned. If you have 1000 rows in the table and use a limit clause to only return 100, then count() will show 100. 

Often you are selecting just one row (i.e. when performing a COUNT(*) query) and a bulit in first() method facilitates this. Be aware, however, that if the row does not exist an UnderflowException is thrown, so you should wrap such queries in a try/catch statement. 

try
{
    $row = \IPS\Db::i()->select( '*', 'table', array( 'id=?', 2 ) )->first();
}
catch( \UnderflowException $e )
{
    // There is no row with id=2 in table
}

Inserting, Updating and Deleting rows

You will also want to insert, update and delete rows in MySQL periodically. To do so, you use the aptly named insert(), update() and delete() methods of the database driver. 

// Insert a row
$new_id = \IPS\Db::i()->insert( 'some_table', array( 'column_one' => 'value', 'column_two' => 'value2' ) );

// Update that row
\IPS\Db::i()->update( 'some_table', array( 'column_two' => 'other value' ), array( 'id_column=?', $new_id ) );

\IPS\Db::i()->delete( 'some_table', array( 'id_column=?', $new_id ) );
// Delete the row

Inserting is straight forward. The first parameter to the method is the table name, followed by an associative array of column names => values. The new autoincrement id (if applicable) is returned by the method.

Note that there is also a replace() method which behaves like insert(), except a REPLACE INTO query will be executed instead of an INSERT INTO query (in which case, if a duplicate unique index is encountered the original will be replaced with the new row).

The update() method expects the first parameter to be the table name, the second parameter to be an associative array of column names => values, and the third parameter to be the where clause (if needed). Additionally, you can pass an array of table joins as the fourth parameter if needed, an array to represent the limit clause as the fifth parameter, and flags to modify the query as the last parameter, including:

  • \IPS\Db::LOW_PRIORITY            Will use LOW_PRIORITY
  • \IPS\Db::IGNORE                    Will use IGNORE

The delete() method is typically called with just the first parameter, the table name, to empty out the entire table, or also with a second parameter, the where clause, to delete specific rows. The method additionally accepts a third parameter to control the order of results for the DELETE query, a fourth parameter to limit the number of results deleted, and a fifth column that specifies the statement column if the WHERE clause is a statement.

Affecting database structure

You can create database tables, add, alter and remove columns from existing tables, and add, alter and remove indexes from existing tables through the \IPS\Db library. Additionally, methods exist to help determine if a table, column or index exists before using it.
Please be warned that while it's possible to alter existing database tables, we are NOT accepting any marketplace submissions which alter existing IPS database tables! 

  

    /**
     * Does table exist?
     *
     * @param    string    $name    Table Name
     * @return    bool
     */
    public function checkForTable( $name )

    /**
     * Does column exist?
     *
     * @param    string    $name    Table Name
     * @param    string    $column    Column Name
     * @return    bool
     */
    public function checkForColumn( $name, $column )

    /**
     * Does index exist?
     *
     * @param    string    $name    Table Name
     * @param    string    $index    Index Name
     * @return    bool
     */
    public function checkForIndex( $name, $index )

    /**
     * Create Table
     *
     * @code
         \IPS\Db::createTable( array(
             'name'            => 'table_name',    // Table name
             'columns'        => array( ... ),    // Column data - see \IPS\Db::compileColumnDefinition for details
             'indexes'        => array( ... ),    // (Optional) Index data - see \IPS\Db::compileIndexDefinition for details
             'comment'        => '...',            // (Optional) Table comment
             'engine'        => 'MEMORY',        // (Optional) Engine to use - will default to not specifying one, unless a FULLTEXT index is specified, in which case MyISAM is forced
             'temporary'        => TRUE,            // (Optional) Will sepcify CREATE TEMPORARY TABLE - defaults to FALSE
             'if_not_exists'    => TRUE,            // (Optional) Will sepcify CREATE TABLE name IF NOT EXISTS - defaults to FALSE
         ) );
     * @endcode
     * @param    array    $data    Table Definition (see code sample for details)
     * @throws    \IPS\Db\Exception
     * @return    void|string
     */
    public function createTable( $data )

    /**
     * Create copy of table structure
     *
     * @param    string    $table            The table name
     * @param    string    $newTableName    Name of table to create
     * @throws    \IPS\Db\Exception
     * @return    void|string
     */
    public function duplicateTableStructure( $table, $newTableName )

    /**
     * Rename table
     *
     * @see        <a href='http://dev.mysql.com/doc/refman/5.1/en/rename-table.html'>Rename Table</a>
     * @param    string    $oldName    The current table name
     * @param    string    $newName    The new name
     * @return    void
     * @see        <a href='http://stackoverflow.com/questions/12856783/best-practice-with-mysql-innodb-to-rename-huge-table-when-table-with-same-name-a'>Renaming huge InnoDB tables</a>
     * @see        <a href='http://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/'>Performance problem dropping huge InnoDB tables</a>
     * @note    A race condition can occur sometimes with InnoDB + innodb_file_per_table so we can't drop then rename...see above links
     */
    public function renameTable( $oldName, $newName )

    /**
     * Alter Table
     * Can only update the comment and engine
     * @note This will not examine key lengths and adjust.
     *
     * @param    string            $table        Table name
     * @param    string|null        $comment    Table comment. NULL to not change
     * @param    string|null        $engine        Engine to use. NULL to not change
     * @return    void
     */
    public function alterTable( $table, $comment=NULL, $engine=NULL )

    /**
     * Drop table
     *
     * @see        <a href='http://dev.mysql.com/doc/refman/5.1/en/drop-table.html'>DROP TABLE Syntax</a>
     * @param    string|array    $table        Table Name(s)
     * @param    bool            $ifExists    Adds an "IF EXISTS" clause to the query
     * @param    bool            $temporary    Table is temporary?
     * @return    mixed
     */
    public function dropTable( $table, $ifExists=FALSE, $temporary=FALSE )

    /**
     * Add column to table in database
     *
     * @see        \IPS\Db::compileColumnDefinition
     * @param    string    $table            Table name
     * @param    array    $definition        Column Definition (see \IPS\Db::compileColumnDefinition for details)
     * @return    void
     */
    public function addColumn( $table, $definition )

    /**
     * Modify an existing column
     *
     * @see        \IPS\Db::compileColumnDefinition
     * @param    string    $table            Table name
     * @param    string    $column            Column name
     * @param    array    $definition        New column definition (see \IPS\Db::compileColumnDefinition for details)
     * @return    void
     */
    public function changeColumn( $table, $column, $definition )

    /**
     * Drop a column
     *
     * @param    string            $table            Table name
     * @param    string|array    $column            Column name
     * @return    void
     */
    public function dropColumn( $table, $column )

    /**
     * Add index to table in database
     *
     * @see        \IPS\Db::compileIndexDefinition
     * @param    string    $table                Table name
     * @param    array    $definition            Index Definition (see \IPS\Db::compileIndexDefinition for details)
     * @param    bool    $discardDuplicates    If adding a unique index, should duplicates be discarded? (If FALSE and there are any, an exception will be thrown)
     * @return    void
     */
    public function addIndex( $table, $definition, $discardDuplicates=TRUE )

    /**
     * Modify an existing index
     *
     * @see        \IPS\Db::compileIndexDefinition
     * @param    string    $table            Table name
     * @param    string    $index            Index name
     * @param    array    $definition        New index definition (see \IPS\Db::compileIndexDefinition for details)
     * @return    void
     */
    public function changeIndex( $table, $index, $definition )

    /**
     * Drop an index
     *
     * @param    string            $table            Table name
     * @param    string|array    $index            Column name
     * @return    mixed
     */
    public function dropIndex( $table, $index )

 

Most of these methods are self-explanatory and infrequently used, except when using the developer center to add queries for upgrades.

Miscellaneous

Finally, there are a handful of methods and properties in the class you may find useful or relevant while working with the database driver.

If you need to obtain the database prefix being used for tables (represented by sql_tbl_prefix in conf_global) you can do so by calling \IPS\Db::i()->prefix . If you are building queries to manually run, you will need to prepend this to table names.

If you need to build an SQL statement and then return it instead of running it, you can set \IPS\Db::i()->returnQuery = TRUE before calling the driver to build a query.

To run a MySQL query that has been fully built already represented as a string, you can call the query() method.

\IPS\Db::i()->query( "UPDATE some_table SET field_a='value' WHERE id_field=1" );

You should typically avoid using the query() method directly, as the other built in methods automatically handle things like escaping values, prepending the database table prefix and so on.

If you need to build a UNION statement, there is a method available to facilitate this. 

	/**
	 * Build UNION statement
	 *
	 * @param	array				$selects		Array of \IPS\Db\Select objects
	 * @param	string|NULL			$order			ORDER BY clause
	 * @param	array|int			$limit			Rows to fetch or array( offset, limit )
	 * @param	string|null			$group			Group by clause
	 * @param	bool				$unionAll		TRUE to perform a UNION ALL, FALSE (default) to perform a regular UNION
	 * @param	int					$flags			Bitwise flags
	 * @param	array|string|NULL	$where			WHERE clause (see example)
	 * @param	string				$querySelect	Custom select for the outer query
	 * @return	\IPS\Db|Select
	 */
	public function union( $selects, $order, $limit, $group=NULL, $unionAll=FALSE, $flags=0, $where=NULL, $querySelect='*' )

To build a FIND_IN_SET() clause, which allows the query to search for specific values in a MySQL field that contains comma-separated values, you can use the findInSet() method. 

    /**
     * FIND_IN_SET
     * Generates a WHERE clause to determine if any value from a column containing a comma-delimined list matches any value from an array
     * 
     * @param    string    $column        Column name (which contains a comma-delimited list)
     * @param    array    $values        Acceptable values
     * @param    bool    $reverse    If true, will match cases where NO values from $column match any from $values
     * @return     string    Where clause
     * @see        \IPS\Db::in()        More efficient equivilant for columns that do not contain comma-delimited lists
     */
    public function findInSet( $column, $values, $reverse=FALSE )

Similarly, you can build an IN() clause by using the in()  method. 

    /**
     * IN
     * Generates a WHERE clause to determine if the value of a column matches any value from an array
     *
     * @param    string    $column            Column name
     * @param    array    $values            Acceptable values
     * @param    bool    $reverse        If true, will match cases where $column does NOT match $values
     * @return     string    Where clause
     * @see        \IPS\Db::findInSet()    For columns that contain comma-delimited lists
     */
    public function in( $column, $values, $reverse=FALSE )

If you are performing a query against a bitwise column and need to check a value, you can use the bitwiseWhere method (or simply build the WHERE clause manually).. 

    /**
     * Bitwise WHERE clause
     *
     * @param    array    $definition        Bitwise keys as defined by the class
     * @param    string    $key            The key to check for
     * @param    bool    $value            Value to check for
     * @return    string
     * @throws    \InvalidArgumentException
     */
    public function bitwiseWhere( $definition, $key, $value=TRUE )

 

You will find that most of these miscellaneous methods are not referenced or needed as often as the core insert, update, delete, replace and select methods.

  Report Document


Invision Community © 2024 IPS, Inc.
×
×
  • Create New...