follower

Question Builder

The Yii Question Builder supplies an object-oriented manner of writing SQL statements. It permits builders to make use of class strategies and properties to specify particular person elements of a SQL assertion. It then assembles completely different elements into a sound SQL assertion that may be additional executed by calling the DAO strategies as described in Knowledge Entry Objects. The next reveals a typical utilization of the Question Builder to construct a SELECT SQL assertion:

$person = Yii::app()->db->createCommand() ->choose('id, username, profile') ->from('tbl_user u') ->be part of('tbl_profile p', 'u.id=p.user_id') ->the place('id=:id', array(':id'=>$id)) ->queryRow();

The Question Builder is finest used when you could assemble a SQL assertion procedurally, or based mostly on some conditional logic in your software. The primary advantages of utilizing the Question Builder embrace:

  • It permits constructing complicated SQL statements programmatically.

  • It routinely quotes desk names and column names to stop battle with SQL reserved phrases and particular characters.

  • It additionally quotes parameter values and makes use of parameter binding when attainable, which helps scale back danger of SQL injection assaults.

  • It affords sure diploma of DB abstraction, which simplifies migration to completely different DB platforms.

It isn’t necessary to make use of the Question Builder. Actually, in case your queries are easy, it’s simpler and quicker to instantly write SQL statements.

Notice: Question builder can’t be used to switch an current question specified as a
SQL assertion. For instance, the next code is not going to work:

$command = Yii::app()->db->createCommand('SELECT * FROM tbl_user'); // the next line will NOT append WHERE clause to the above SQL $command->the place('id=:id', array(':id'=>$id));

In different phrases, don’t combine the utilization of plain SQL and question builder.

1. Getting ready Question Builder

The Yii Question Builder is offered when it comes to CDbCommand, the principle DB question class described in Knowledge Entry Objects.

To start out utilizing the Question Builder, we create a brand new occasion of CDbCommand as follows,

$command = Yii::app()->db->createCommand();

That’s, we use Yii::app()->db to get the DB connection, after which name CDbConnection::createCommand() to create the wanted command occasion.

Notice that as a substitute of passing an entire SQL assertion to the createCommand() name as we do in Knowledge Entry Objects, we depart it empty. It is because we are going to construct particular person elements of the SQL assertion utilizing the Question Builder strategies defined within the following.

2. Constructing Knowledge Retrieval Queries

Knowledge retrieval queries confer with SELECT SQL statements. The question builder supplies a set of strategies to construct particular person elements of a SELECT assertion. As a result of all these strategies return the CDbCommand occasion, we will name them utilizing technique chaining, as proven within the instance originally of this part.

  • choose(): specifies the SELECT a part of the question
  • selectDistinct(): specifies the SELECT a part of the question and activates the DISTINCT flag
  • from(): specifies the FROM a part of the question
  • the place(): specifies the WHERE a part of the question
  • andWhere(): appends situation to the WHERE a part of the question with AND operator
  • orWhere(): appends situation to the WHERE a part of the question with OR operator
  • be part of(): appends an internal be part of question fragment
  • leftJoin(): appends a left outer be part of question fragment
  • rightJoin(): appends a proper outer be part of question fragment
  • crossJoin(): appends a cross be part of question fragment
  • naturalJoin(): appends a pure be part of question fragment
  • group(): specifies the GROUP BY a part of the question
  • having(): specifies the HAVING a part of the question
  • order(): specifies the ORDER BY a part of the question
  • restrict(): specifies the LIMIT a part of the question
  • offset(): specifies the OFFSET a part of the question
  • union(): appends a UNION question fragment

Within the following, we clarify learn how to use these question builder strategies. For simplicity, we assume the underlying database is MySQL. Notice that in case you are utilizing different DBMS, the desk/column/worth quoting proven within the examples could also be completely different.

choose()

perform choose($columns='*')

The choose() technique specifies the SELECT a part of a question. The $columns parameter specifies the columns to be chosen, which may be both a string representing comma-separated columns, or an array of column names. Column names can comprise desk prefixes and/or column aliases. The tactic will routinely quote the column names except a column comprises some parenthesis (which implies the column is given as a DB expression).

Under are some examples:

// SELECT * choose() // SELECT `id`, `username` choose('id, username') // SELECT `tbl_user`.`id`, `username` AS `identify` choose('tbl_user.id, username as identify') // SELECT `id`, `username` choose(array('id', 'username')) // SELECT `id`, depend(*) as num choose(array('id', 'depend(*) as num'))

selectDistinct()

perform selectDistinct($columns)

The selectDistinct() technique is comparable as choose() besides that it activates the DISTINCT flag. For instance, selectDistinct('id, username') will generate the next SQL:

SELECT DISTINCT `id`, `username`

from()

perform from($tables)

The from() technique specifies the FROM a part of a question. The $tables parameter specifies which tables to be chosen from. This may be both a string representing comma-separated desk names, or an array of desk names. Desk names can comprise schema prefixes (e.g. public.tbl_user) and/or desk aliases (e.g. tbl_user u). The tactic will routinely quote the desk names except it comprises some parenthesis (which implies the desk is given as a sub-query or DB expression).

Under are some examples:

// FROM `tbl_user` from('tbl_user') // FROM `tbl_user` `u`, `public`.`tbl_profile` `p` from('tbl_user u, public.tbl_profile p') // FROM `tbl_user`, `tbl_profile` from(array('tbl_user', 'tbl_profile')) // FROM `tbl_user`, (choose * from tbl_profile) p from(array('tbl_user', '(choose * from tbl_profile) p'))

the place()

perform the place($circumstances, $params=array())

The the place() technique specifies the WHERE a part of a question. The $circumstances parameter specifies question circumstances whereas $params specifies the parameters to be sure to the entire question. The $circumstances parameter may be both a string (e.g. id=1) or an array of the format:

array(operator, operand1, operand2, ...)

the place operator may be any of the next:

  • and: the operands must be concatenated collectively utilizing AND. For instance, array('and', 'id=1', 'id=2') will generate id=1 AND id=2. If an operand is an array, it will likely be transformed right into a string utilizing the identical guidelines described right here. For instance, array('and', 'sort=1', array('or', 'id=1', 'id=2')) will generate sort=1 AND (id=1 OR id=2). The tactic will NOT do any quoting or escaping.

  • or: comparable because the and operator besides that the operands are concatenated utilizing OR.

  • in: operand 1 must be a column or DB expression, and operand 2 be an array representing the vary of the values that the column or DB expression must be in. For instance, array('in', 'id', array(1,2,3)) will generate id IN (1,2,3). The tactic will correctly quote the column identify and escape values within the vary.

  • not in: comparable because the in operator besides that IN is changed with NOT IN within the generated situation.

  • like: operand 1 must be a column or DB expression, and operand 2 be a string or an array representing the vary of the values that the column or DB expression must be like. For instance, array('like', 'identify', '%tester%') will generate identify LIKE '%tester%'. When the worth vary is given as an array, a number of LIKE predicates will likely be generated and concatenated utilizing AND. For instance, array('like', 'identify', array('%take a look at%', '%pattern%')) will generate identify LIKE '%take a look at%' AND identify LIKE '%pattern%'. The tactic will correctly quote the column identify and escape values within the vary.

  • not like: comparable because the like operator besides that LIKE is changed with NOT LIKE within the generated situation.

  • or like: comparable because the like operator besides that OR is used to concatenated a number of LIKE predicates.

  • or not like: comparable because the not like operator besides that OR is used to concatenated a number of NOT LIKE predicates.

See also  The Legend Of Herobrine

Under are some examples of utilizing the place:

// WHERE id=1 or id=2 the place('id=1 or id=2') // WHERE id=:id1 or id=:id2 the place('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2)) // WHERE id=1 OR id=2 the place(array('or', 'id=1', 'id=2')) // WHERE id=1 AND (sort=2 OR sort=3) the place(array('and', 'id=1', array('or', 'sort=2', 'sort=3'))) // WHERE `id` IN (1, 2) the place(array('in', 'id', array(1, 2)) // WHERE `id` NOT IN (1, 2) the place(array('not in', 'id', array(1,2))) // WHERE `identify` LIKE '%Qiang%' the place(array('like', 'identify', '%Qiang%')) // WHERE `identify` LIKE '%Qiang' AND `identify` LIKE '%Xue' the place(array('like', 'identify', array('%Qiang', '%Xue'))) // WHERE `identify` LIKE '%Qiang' OR `identify` LIKE '%Xue' the place(array('or like', 'identify', array('%Qiang', '%Xue'))) // WHERE `identify` NOT LIKE '%Qiang%' the place(array('not like', 'identify', '%Qiang%')) // WHERE `identify` NOT LIKE '%Qiang%' OR `identify` NOT LIKE '%Xue%' the place(array('or not like', 'identify', array('%Qiang%', '%Xue%')))

Please word that when the operator comprises like, we now have to explicitly specify the wildcard characters (resembling % and _) within the patterns. If the patterns are from person enter, we also needs to use the next code to flee the particular characters to stop them from being handled as wildcards:

$key phrase=$_GET['q']; // escape % and _ characters $key phrase=strtr($key phrase, array('%'=>'%', '_'=>'_')); $command->the place(array('like', 'title', '%'.$key phrase.'%'));

andWhere()

perform andWhere($circumstances, $params=array())

The andWhere() technique appends given situation to the WHERE a part of a question with AND operator. Conduct of this technique is nearly the identical as the place() besides it appends situation not replaces it. Discuss with the the place() documentation for extra data on parameters of this technique.

orWhere()

perform orWhere($circumstances, $params=array())

The orWhere() technique appends given situation to the WHERE a part of a question with OR operator. Conduct of this technique is nearly the identical as the place() besides it appends situation not replaces it. Discuss with the the place() documentation for extra data on parameters of this technique.

order()

perform order($columns)

The order() technique specifies the ORDER BY a part of a question.
The $columns parameter specifies the columns to be ordered by, which may be both a string representing comma-separated columns and order instructions (ASC or DESC), or an array of columns and order instructions. Column names can comprise desk prefixes. The tactic will routinely quote the column names except a column comprises some parenthesis (which implies the column is given as a DB expression).

Under are some examples:

// ORDER BY `identify`, `id` DESC order('identify, id desc') // ORDER BY `tbl_profile`.`identify`, `id` DESC order(array('tbl_profile.identify', 'id desc'))

restrict() and offset()

perform restrict($restrict, $offset=null) perform offset($offset)

The restrict() and offset() strategies specify the LIMIT and OFFSET a part of a question. Notice that some DBMS could not help LIMIT and OFFSET syntax. On this case, the Question Builder will rewrite the entire SQL assertion to simulate the perform of restrict and offset.

Under are some examples:

// LIMIT 10 restrict(10) // LIMIT 10 OFFSET 20 restrict(10, 20) // OFFSET 20 offset(20)

be part of() and its variants

perform be part of($desk, $circumstances, $params=array()) perform leftJoin($desk, $circumstances, $params=array()) perform rightJoin($desk, $circumstances, $params=array()) perform crossJoin($desk) perform naturalJoin($desk)

The be part of() technique and its variants specify learn how to be part of with different tables utilizing INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, or NATURAL JOIN. The $desk parameter specifies which desk to be joined with. The desk identify can comprise schema prefix and/or alias. The tactic will quote the desk identify except it comprises a parenthesis that means it’s both a DB expression or sub-query. The $circumstances parameter specifies the be part of situation. Its syntax is similar as that in the place(). And $params specifies the parameters to be sure to the entire question.

Notice that not like different question builder strategies, every name of a be part of technique will likely be appended to the earlier ones.

Under are some examples:

// JOIN `tbl_profile` ON user_id=id be part of('tbl_profile', 'user_id=id') // LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND sort=1 leftJoin('pub.tbl_profile p', 'p.user_id=id AND sort=:sort', array(':sort'=>1))

group()

perform group($columns)

The group() technique specifies the GROUP BY a part of a question.
The $columns parameter specifies the columns to be grouped by, which may be both a string representing comma-separated columns, or an array of columns. Column names can comprise desk prefixes. The tactic will routinely quote the column names except a column comprises some parenthesis (which implies the column is given as a DB expression).

Under are some examples:

// GROUP BY `identify`, `id` group('identify, id') // GROUP BY `tbl_profile`.`identify`, `id` group(array('tbl_profile.identify', 'id'))

having()

perform having($circumstances, $params=array())

The having() technique specifies the HAVING a part of a question. Its utilization is similar as the place().

Under are some examples:

// HAVING id=1 or id=2 having('id=1 or id=2') // HAVING id=1 OR id=2 having(array('or', 'id=1', 'id=2'))

union()

perform union($sql)

The union() technique specifies the UNION a part of a question. It appends $sql to the present SQL utilizing UNION operator. Calling union() a number of occasions will append a number of SQLs to the present SQL.

Under are some examples:

// UNION (choose * from tbl_profile) union('choose * from tbl_profile')

Executing Queries

After calling the above question builder strategies to construct a question, we will name the DAO strategies as described in Knowledge Entry Objects to execute the question. For instance, we will name CDbCommand::queryRow() to acquire a row of end result, or CDbCommand::queryAll() to get all rows without delay.
Instance:

$customers = Yii::app()->db->createCommand() ->choose('*') ->from('tbl_user') ->queryAll();

Retrieving SQLs

In addition to executing a question constructed by the Question Builder, we will additionally retrieve the corresponding SQL assertion. This may be executed by calling CDbCommand::getText().

$sql = Yii::app()->db->createCommand() ->choose('*') ->from('tbl_user') ->textual content;

If there are any parameters to be sure to the question, they are often retrieved through the CDbCommand::params property.

Different Syntax for Constructing Queries

Typically, utilizing technique chaining to construct a question is probably not the optimum alternative. The Yii Question Builder permits a question to be constructed utilizing easy object property assignments. Specifically, for every question builder technique, there’s a corresponding property that has the identical identify. Assigning a worth to the property is equal to calling the corresponding technique. For instance, the next two statements are equal, assuming $command represents a CDbCommand object:

$command->choose(array('id', 'username')); $command->choose = array('id', 'username');

Moreover, the CDbConnection::createCommand() technique can take an array because the parameter. The name-value pairs within the array will likely be used to initialize the properties of the created CDbCommand occasion. This implies, we will use the next code to construct a question:

$row = Yii::app()->db->createCommand(array( 'choose' => array('id', 'username'), 'from' => 'tbl_user', 'the place' => 'id=:id', 'params' => array(':id'=>1), ))->queryRow();

Constructing A number of Queries

A CDbCommand occasion may be reused a number of occasions to construct a number of queries. Earlier than constructing a brand new question, nonetheless, the CDbCommand::reset() technique have to be invoked to wash up the earlier question. For instance:

$command = Yii::app()->db->createCommand(); $customers = $command->choose('*')->from('tbl_users')->queryAll(); $command->reset(); // clear up the earlier question $posts = $command->choose('*')->from('tbl_posts')->queryAll();

3. Constructing Knowledge Manipulation Queries

Knowledge manipulation queries confer with SQL statements for inserting, updating and deleting knowledge in a DB desk. Corresponding to those queries, the question builder supplies insert, replace and delete strategies, respectively. Not like the SELECT question strategies described above, every of those knowledge manipulation question strategies will construct a whole SQL assertion and execute it instantly.

  • insert(): inserts a row right into a desk
  • replace(): updates the info in a desk
  • delete(): deletes the info from a desk
See also  Ball Python Morphs: The 50 MOST Popular (Pictures & Prices)

Under we describe these knowledge manipulation question strategies.

insert()

perform insert($desk, $columns)

The insert() technique builds and executes an INSERT SQL assertion. The $desk parameter specifies which desk to be inserted into, whereas $columns is an array of name-value pairs specifying the column values to be inserted. The tactic will quote the desk identify correctly and can use parameter-binding for the values to be inserted.

Under is an instance:

// construct and execute the next SQL: // INSERT INTO `tbl_user` (`identify`, `e mail`) VALUES (:identify, :e mail) $command->insert('tbl_user', array( 'identify'=>'Tester', 'e mail'=>'tester@instance.com', ));

replace()

perform replace($desk, $columns, $circumstances='', $params=array())

The replace() technique builds and executes an UPDATE SQL assertion. The $desk parameter specifies which desk to be up to date; $columns is an array of name-value pairs specifying the column values to be up to date; $circumstances and $params are like in the place(), which specify the WHERE clause within the UPDATE assertion. The tactic will quote the desk identify correctly and can use parameter-binding for the values to be up to date.

Under is an instance:

// construct and execute the next SQL: // UPDATE `tbl_user` SET `identify`=:identify WHERE id=:id $command->replace('tbl_user', array( 'identify'=>'Tester', ), 'id=:id', array(':id'=>1));

delete()

perform delete($desk, $circumstances='', $params=array())

The delete() technique builds and executes a DELETE SQL assertion. The $desk parameter specifies which desk to delete from; $circumstances and $params are like in the place(), which specify the WHERE clause within the DELETE assertion. The tactic will quote the desk identify correctly.

Under is an instance:

// construct and execute the next SQL: // DELETE FROM `tbl_user` WHERE id=:id $command->delete('tbl_user', 'id=:id', array(':id'=>1));

4. Constructing Schema Manipulation Queries

In addition to regular knowledge retrieval and manipulation queries, the question builder additionally affords a set of strategies for constructing and executing SQL queries that may manipulate the schema of a database. Specifically, it helps the next queries:

  • createTable(): creates a desk
  • renameTable(): renames a desk
  • dropTable(): drops a desk
  • truncateTable(): truncates a desk
  • addColumn(): provides a desk column
  • renameColumn(): renames a desk column
  • alterColumn(): alters a desk column
  • addForeignKey(): provides a international key (accessible since 1.1.6)
  • dropForeignKey(): drops a international key (accessible since 1.1.6)
  • dropColumn(): drops a desk column
  • createIndex(): creates an index
  • dropIndex(): drops an index

Data: Though the precise SQL statements for manipulating database schema fluctuate extensively throughout completely different DBMS, the question builder makes an attempt to supply a uniform interface for constructing these queries. This simplifies the duty of migrating a database from one DBMS to a different.

Summary Knowledge Varieties

The question builder introduces a set of summary knowledge varieties that can be utilized in defining desk columns. Not like the bodily knowledge varieties which are particular to explicit DBMS and are fairly completely different in several DBMS, the summary knowledge varieties are impartial of DBMS. When summary knowledge varieties are utilized in defining desk columns, the question builder will convert them into the corresponding bodily knowledge varieties.

The next summary knowledge varieties are supported by the question builder.

  • pk: a generic main key sort, will likely be transformed into int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY for MySQL;
  • string: string sort, will likely be transformed into varchar(255) for MySQL;
  • textual content: textual content sort (lengthy string), will likely be transformed into textual content for MySQL;
  • integer: integer sort, will likely be transformed into int(11) for MySQL;
  • float: floating quantity sort, will likely be transformed into float for MySQL;
  • decimal: decimal quantity sort, will likely be transformed into decimal for MySQL;
  • datetime: datetime sort, will likely be transformed into datetime for MySQL;
  • timestamp: timestamp sort, will likely be transformed into timestamp for MySQL;
  • time: time sort, will likely be transformed into time for MySQL;
  • date: date sort, will likely be transformed into date for MySQL;
  • binary: binary knowledge sort, will likely be transformed into blob for MySQL;
  • boolean: boolean sort, will likely be transformed into tinyint(1) for MySQL;
  • cash: cash/forex sort, will likely be transformed into decimal(19,4) for MySQL. This sort has been accessible since model 1.1.8.

createTable()

perform createTable($desk, $columns, $choices=null)

The createTable() technique builds and executes a SQL assertion for making a desk. The $desk parameter specifies the identify of the desk to be created. The $columns parameter specifies the columns within the new desk. They have to be given as name-definition pairs (e.g. 'username'=>'string'). The $choices parameter specifies any additional SQL fragment that must be appended to the generated SQL. The question builder will quote the desk identify in addition to the column names correctly.

When specifying a column definition, one can use an summary knowledge sort as described above. The question builder will convert the summary knowledge sort into the corresponding bodily knowledge sort, in line with the at the moment used DBMS. For instance, string will likely be transformed into varchar(255) for MySQL.

A column definition also can comprise non-abstract knowledge sort or specs. They are going to be put within the generated SQL with none change. For instance, level is just not an summary knowledge sort, and if utilized in a column definition, it’ll seem as is within the ensuing SQL; and string NOT NULL will likely be transformed into varchar(255) NOT NULL (i.e., solely the summary sort string is transformed).

See also  Add & Embed a Music Player to your Website

Under is an instance displaying learn how to create a desk:

// CREATE TABLE `tbl_user` ( // `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, // `username` varchar(255) NOT NULL, // `location` level // ) ENGINE=InnoDB createTable('tbl_user', array( 'id' => 'pk', 'username' => 'string NOT NULL', 'location' => 'level', ), 'ENGINE=InnoDB')

renameTable()

perform renameTable($desk, $newName)

The renameTable() technique builds and executes a SQL assertion for renaming a desk. The $desk parameter specifies the identify of the desk to be renamed. The $newName parameter specifies the brand new identify of the desk. The question builder will quote the desk names correctly.

Under is an instance displaying learn how to rename a desk:

// RENAME TABLE `tbl_users` TO `tbl_user` renameTable('tbl_users', 'tbl_user')

dropTable()

perform dropTable($desk)

The dropTable() technique builds and executes a SQL assertion for dropping a desk. The $desk parameter specifies the identify of the desk to be dropped. The question builder will quote the desk identify correctly.

Under is an instance displaying learn how to drop a desk:

// DROP TABLE `tbl_user` dropTable('tbl_user')

truncateTable()

perform truncateTable($desk)

The truncateTable() technique builds and executes a SQL assertion for truncating a desk. The $desk parameter specifies the identify of the desk to be truncated. The question builder will quote the desk identify correctly.

Under is an instance displaying learn how to truncate a desk:

// TRUNCATE TABLE `tbl_user` truncateTable('tbl_user')

addColumn()

perform addColumn($desk, $column, $sort)

The addColumn() technique builds and executes a SQL assertion for including a brand new desk column. The $desk parameter specifies the identify of the desk that the brand new column will likely be added to. The $column parameter specifies the identify of the brand new column. And $sort specifies the definition of the brand new column. Column definition can comprise summary knowledge sort, as described within the subsection of “createTable”. The question builder will quote the desk identify in addition to the column identify correctly.

Under is an instance displaying learn how to add a desk column:

// ALTER TABLE `tbl_user` ADD `e mail` varchar(255) NOT NULL addColumn('tbl_user', 'e mail', 'string NOT NULL')

dropColumn()

perform dropColumn($desk, $column)

The dropColumn() technique builds and executes a SQL assertion for dropping a desk column. The $desk parameter specifies the identify of the desk whose column is to be dropped. The $column parameter specifies the identify of the column to be dropped. The question builder will quote the desk identify in addition to the column identify correctly.

Under is an instance displaying learn how to drop a desk column:

// ALTER TABLE `tbl_user` DROP COLUMN `location` dropColumn('tbl_user', 'location')

renameColumn()

perform renameColumn($desk, $identify, $newName)

The renameColumn() technique builds and executes a SQL assertion for renaming a desk column. The $desk parameter specifies the identify of the desk whose column is to be renamed. The $identify parameter specifies the previous column identify. And $newName specifies the brand new column identify. The question builder will quote the desk identify in addition to the column names correctly.

Under is an instance displaying learn how to rename a desk column:

// ALTER TABLE `tbl_users` CHANGE `identify` `username` varchar(255) NOT NULL renameColumn('tbl_user', 'identify', 'username')

alterColumn()

perform alterColumn($desk, $column, $sort)

The alterColumn() technique builds and executes a SQL assertion for altering a desk column. The $desk parameter specifies the identify of the desk whose column is to be altered. The $column parameter specifies the identify of the column to be altered. And $sort specifies the brand new definition of the column. Column definition can comprise summary knowledge sort, as described within the subsection of “createTable”. The question builder will quote the desk identify in addition to the column identify correctly.

Under is an instance displaying learn how to alter a desk column:

// ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULL alterColumn('tbl_user', 'username', 'string NOT NULL')

addForeignKey()

perform addForeignKey($identify, $desk, $columns, $refTable, $refColumns, $delete=null, $replace=null)

The addForeignKey() technique builds and executes a SQL assertion for including a international key constraint to a desk. The $identify parameter specifies the identify of the international key. The $desk and $columns parameters specify the desk identify and column identify that the international key’s about. If there are a number of columns, they need to be separated by comma characters. The $refTable and $refColumns parameters specify the desk identify and column identify that the international key references. The $delete and $replace parameters specify the ON DELETE and ON UPDATE choices within the SQL assertion, respectively. Most DBMS help these choices: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL. The question builder will correctly quote the desk identify, index identify and column identify(s).

Under is an instance displaying learn how to add a international key constraint,

// ALTER TABLE `tbl_profile` ADD CONSTRAINT `fk_profile_user_id` // FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`) // ON DELETE CASCADE ON UPDATE CASCADE addForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id', 'tbl_user', 'id', 'CASCADE', 'CASCADE')

dropForeignKey()

perform dropForeignKey($identify, $desk)

The dropForeignKey() technique builds and executes a SQL assertion for dropping a international key constraint. The $identify parameter specifies the identify of the international key constraint to be dropped. The $desk parameter specifies the identify of the desk that the international key’s on. The question builder will quote the desk identify in addition to the constraint names correctly.

Under is an instance displaying learn how to drop a international key constraint:

// ALTER TABLE `tbl_profile` DROP FOREIGN KEY `fk_profile_user_id` dropForeignKey('fk_profile_user_id', 'tbl_profile')

createIndex()

perform createIndex($identify, $desk, $column, $distinctive=false)

The createIndex() technique builds and executes a SQL assertion for creating an index. The $identify parameter specifies the identify of the index to be created. The $desk parameter specifies the identify of the desk that the index is on. The $column parameter specifies the identify of the column to be listed. And the $distinctive parameter specifies whether or not a novel index must be created. If the index consists of a number of columns, they have to be separated by commas. The question builder will correctly quote the desk identify, index identify and column identify(s).

Under is an instance displaying learn how to create an index:

// CREATE INDEX `idx_username` ON `tbl_user` (`username`) createIndex('idx_username', 'tbl_user', 'username')

dropIndex()

perform dropIndex($identify, $desk)

The dropIndex() technique builds and executes a SQL assertion for dropping an index. The $identify parameter specifies the identify of the index to be dropped. The $desk parameter specifies the identify of the desk that the index is on. The question builder will quote the desk identify in addition to the index names correctly.

Under is an instance displaying learn how to drop an index:

// DROP INDEX `idx_username` ON `tbl_user` dropIndex('idx_username', 'tbl_user')

Discovered a typo otherwise you assume this web page wants enchancment?
Edit it on github !

Leave a Reply

Your email address will not be published.