database-basics.md 6.48 KB
Newer Older
1 2 3 4
Database basics
===============

Yii has a database access layer built on top of PHP's [PDO](http://www.php.net/manual/en/ref.pdo.php). It provides
5 6 7 8 9 10 11 12
uniform API and solves some inconsistencies between different DBMS. By default Yii supports the following DBMS:

- [MySQL](http://www.mysql.com/)
- [SQLite](http://sqlite.org/)
- [PostgreSQL](http://www.postgresql.org/)
- [CUBRID](http://www.cubrid.org/) (version 9.1.0 and higher).
- Oracle
- MSSQL
13

14

15 16 17 18 19 20 21 22 23 24 25 26 27
Configuration
-------------

In order to start using database you need to configure database connection component first by adding `db` component
to application configuration (for "basic" web application it's `config/web.php`) like the following:

```php
return array(
	// ...
	'components' => array(
		// ...
		'db' => array(
			'class' => 'yii\db\Connection',
28 29 30
			'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
			//'dsn' => 'sqlite:/path/to/database/file', // SQLite
			//'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
31
			//'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
32 33 34
			//'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
			//'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
			//'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
35
			//'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
36 37 38 39 40 41 42 43
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
		),
	),
	// ...
);
```
44 45
Please refer to the [PHP manual](http://www.php.net/manual/en/function.PDO-construct.php) for more details
on the format of the DSN string.
46

47
After the connection component is configured you can access it using the following syntax:
48 49 50 51 52 53

```php
$connection = \Yii::$app->db;
```

You can refer to [[\yii\db\Connection]] for a list of properties you can configure. Also note that you can define more
54
than one connection component and use both at the same time if needed:
55 56 57 58 59 60

```php
$primaryConnection = \Yii::$app->db;
$secondaryConnection = \Yii::$app->secondDb;
```

61
If you don't want to define the connection as an application component you can instantiate it directly:
62 63 64 65 66 67 68 69 70 71

```php
$connection = new \yii\db\Connection(array(
	'dsn' => $dsn,
 	'username' => $username,
 	'password' => $password,
));
$connection->open();
```

72

73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
Basic SQL queries
-----------------

Once you have a connection instance you can execute SQL queries using [[\yii\db\Command]].

### SELECT

When query returns a set of rows:

```php
$command = $connection->createCommand('SELECT * FROM tbl_post');
$posts = $command->queryAll();
```

When only a single row is returned:

```php
90
$command = $connection->createCommand('SELECT * FROM tbl_post WHERE id=1');
91
$post = $command->queryOne();
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
```

When there are multiple values from the same column:

```php
$command = $connection->createCommand('SELECT title FROM tbl_post');
$titles = $command->queryColumn();
```

When there's a scalar value:

```php
$command = $connection->createCommand('SELECT COUNT(*) FROM tbl_post');
$postCount = $command->queryScalar();
```

### UPDATE, INSERT, DELETE etc.

If SQL executed doesn't return any data you can use command's `execute` method:

```php
113
$command = $connection->createCommand('UPDATE tbl_post SET status=1 WHERE id=1');
114 115 116
$command->execute();
```

117
Alternatively the following syntax that takes care of proper table and column names quoting is possible:
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141

```php
// INSERT
$connection->createCommand()->insert('tbl_user', array(
	'name' => 'Sam',
	'age' => 30,
))->execute();

// INSERT multiple rows at once
$connection->createCommand()->batchInsert('tbl_user', array('name', 'age'), array(
	array('Tom', 30),
	array('Jane', 20),
	array('Linda', 25),
))->execute();

// UPDATE
$connection->createCommand()->update('tbl_user', array(
	'status' => 1,
), 'age > 30')->execute();

// DELETE
$connection->createCommand()->delete('tbl_user', 'status = 0')->execute();
```

142 143 144
Quoting table and column names
------------------------------

145
Most of the time you would use the following syntax for quoting table and column names:
146 147

```php
148
$sql = "SELECT COUNT([[$column]]) FROM {{$table}}";
149 150 151
$rowCount = $connection->createCommand($sql)->queryScalar();
```

152
In the code above `[[X]]` will be converted to properly quoted column name while `{{Y}}` will be converted to properly
153 154
quoted table name.

155 156 157 158 159 160 161 162 163
The alternative is to quote table and column names manually using [[\yii\db\Connection::quoteTableName()]] and
[[\yii\db\Connection::quoteColumnName()]]:

```php
$column = $connection->quoteColumnName($column);
$table = $connection->quoteTableName($table);
$sql = "SELECT COUNT($column) FROM $table";
$rowCount = $connection->createCommand($sql)->queryScalar();
```
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237

Prepared statements
-------------------

In order to securely pass query parameters you can use prepared statements:

```php
$command = $connection->createCommand('SELECT * FROM tbl_post WHERE id=:id');
$command->bindValue(':id', $_GET['id']);
$post = $command->query();
```

Another usage is performing a query multiple times while preparing it only once:

```php
$command = $connection->createCommand('DELETE FROM tbl_post WHERE id=:id');
$command->bindParam(':id', $id);

$id = 1;
$command->execute();

$id = 2;
$command->execute();
```

Transactions
------------

If the underlying DBMS supports transactions, you can perform transactional SQL queries like the following:

```php
$transaction = $connection->beginTransaction();
try {
	$connection->createCommand($sql1)->execute();
 	$connection->createCommand($sql2)->execute();
	// ... executing other SQL statements ...
	$transaction->commit();
} catch(Exception $e) {
	$transaction->rollback();
}
```

Working with database schema
----------------------------

### Getting schema information

You can get a [[\yii\db\Schema]] instance like the following:

```php
$schema = $connection->getSchema();
```

It contains a set of methods allowing you to retrieve various information about the database:

```php
$tables = $schema->getTableNames();
```

For the full reference check [[\yii\db\Schema]].

### Modifying schema

Aside from basic SQL queries [[\yii\db\Command]] contains a set of methods allowing to modify database schema:

- createTable, renameTable, dropTable, truncateTable
- addColumn, renameColumn, dropColumn, alterColumn
- addPrimaryKey, dropPrimaryKey
- addForeignKey, dropForeignKey
- createIndex, dropIndex

These can be used as follows:

```php
238
// CREATE TABLE
239 240 241 242 243 244 245 246
$connection->createCommand()->createTable('tbl_post', array(
	'id' => 'pk',
	'title' => 'string',
	'text' => 'text',
);
```

For the full reference check [[\yii\db\Command]].