Summary of PHP development framework Laravel database operation methods

1. Read/Write Connections

Sometimes you may want to use one database connection for SELECT statements, and another for insert, update, and delete statements. Laravel makes this a breeze and will always use the correct connection whether using raw queries, the query builder or the Eloquent ORM.

How the read/write connection should be configured, let's look at this example:

'mysql' => array('read' => array('host' => '192.168.1.1'),'write' => array('host' => '196.168.1.2'),'driver' => 'mysql','database' =>'database','username' => 'root','password' => '','charset' => 'utf8','collation' => 'utf8_unicode_ci','prefix' => '')

Note that two keys are added to the configuration array: read and write. Both keys have array values containing one key: host. The rest of the read and write database options from the main mysql connection will be the merged array. So, we just need to put items into the read and write arrays if we wish to overwrite the values in the main array. So, in this case, 192.168.1.1 will be used as a "read" connection, while 192.168.1.2 will be used as a "write" connection. Database credentials, prefix, charset and all other options in the main mysql array will be shared across both connections.

2. Run the query

Once you have configured the database connection, you can use the DB run query class.

Run a Select query

$results = DB::select('select * from users where id = ?', array(1));

The results select method always returns an array.

Run an Insert statement

　DB::insert('insert into users (id, name) values (?, ?)', array(1, 'Dayle'));

Run an Update statement

　　DB::update('update users set votes = 100 where name = ?', array('John'));

Run a Delete statement

DB::delete('delete from users');

Note: The number of rows returned by the update and delete statements affects the operation.

Run a general statement

DB::statement('drop table users');

Query event monitor

You can query event monitor using DB::listening method:

DB::listen(function($sql, $bindings, $time){ //});

3. Database transactions

To run a set of operations within a database transaction, you can use the transaction method:

　DB::transaction(function(){ DB::table('users')->update(array('votes' => 1)); DB::table('posts')->delete();});

Note: Any exception thrown during transaction shutdown will cause the automatic transaction to be rolled back

Sometimes you may need to start a transaction:

DB::beginTransaction();

You can rollback the transaction via the rollback method:

DB::rollback();

Finally, you can commit a transaction via the commit method:

DB::commit();

4. Accessing Connections

When using multiple connections, you can access them through the DB::Connection method:

$users = DB::connection('foo')->select(...);

You can also access the original, underlying PDO instance:

$pdo = DB::connection()->getPdo();

Sometimes you may need to reconnect to a given database:

DB::reconnect('foo');

If you need to disconnect from a given database that would exceed the underlying PDO instance'smax_connections limit, use the disconnect method:

DB::disconnect('foo');

5. Query logs

By default, Laravel logs all queries running the current request in memory. However, in some cases, such as when inserting a large number of rows, this may cause the application to use excess memory. To disable the log, you can use the disableQueryLog method:

DB::connection()->disableQueryLog();

o To get a set of executed queries, you can use the getQueryLog method:

$queries = DB::getQueryLog();

For more related articles summarizing the operation methods of the PHP development framework Laravel database, please Follow PHP Chinese website!