Summary of Laravel database operation method of PHP development framework

1. Read/Write Connection

Sometimes you might want to use a database connection with one SELECT statement, and another for insert, update, and delete statements. Laravel makes this breeze and will always use the correct join whether to use the original query, query builder or the eloquent ORM.

How the read/write connection should be configured, let's take a 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. These two keys have array values containing one key: host. The remaining read-write database options from the main mysql connection will merge the array. So, we just need to put the items into the read and write arrays if we want to overwrite the values in the primary array. So, in this case, 192.168. 1.1 will be used as a "read" connection and while 192.168. 1.2 will be used as a "write" connection. Database credentials, prefixes, character sets, and all other options in the main mysql array will span two shared connections.

2. Run the query

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

Run 1 Select query

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

The selection method of the result always returns 1 array.

Run 1 Insert statement

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

Run 1 update statement

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

Run 1 Delete statement

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

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

Run 1 generic declaration

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

Query event listening

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

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

3. Database transactions

Run 1 set of operations on 1 database transaction, you can use the transaction method:

 　 DB::transaction(function(){ DB::table('users')->update(array('votes' 

=> 1)); DB::table('posts')->delete();});

Note: Any exceptional closure thrown in the transaction will cause the automatic transaction to be rolled back

Sometimes you may need to start a transaction:

DB::beginTransaction();

You can roll back the transaction by rolling back the method:

DB::rollback();

Finally, you can commit 1 transaction through the commit method

DB::commit();

4. Access the connection

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

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

You can also access the original, potential 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 will exceed the limit of the underlying PDO instance 'smax_connections, use the disconnect method:

DB::disconnect('foo');

5. Query the log

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

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

o gets 1 set of queries executed, and you can use the getQueryLog method:

$queries = DB::getQueryLog();