Laravel operation MySQL Database (add to check) example

1, connect the database

Laravel The database configuration file is config/database.php, open the file, the default content is as follows:

<?php

return [
Default return result set to PHP object instance
' Fetch ' => pdo::fetch_class,
The default database connection is MySQL, and you can modify db_connection values in the. env file
' Default ' => env (' db_connection ', ' MySQL '),

' Connections ' => [
SQLite database-related configuration
' SQLite ' => [
' Driver ' => ' SQLite ',
' Database ' => storage_path (' Database.sqlite '),
' Prefix ' => ',
],
MySQL Database related configuration
' MySQL ' => [
' Driver ' => ' MySQL ',
' Host ' => env (' db_host ', ' localhost '),
' Database ' => env (' db_database ', ' Forge '),
' Username ' => env (' db_username ', ' Forge '),
' Password ' => env (' Db_password ', '),
' CharSet ' => ' UTF8 ',
' Collation ' => ' utf8_unicode_ci ',
' Prefix ' => ',
' Strict ' => false,
],
Postgres database-related configuration
' Pgsql ' => [
' Driver ' => ' pgsql ',
' Host ' => env (' db_host ', ' localhost '),
' Database ' => env (' db_database ', ' Forge '),
' Username ' => env (' db_username ', ' Forge '),
' Password ' => env (' Db_password ', '),
' CharSet ' => ' UTF8 ',
' Prefix ' => ',
' Schema ' => ' public ',
],
SQL Server database related configuration
' Sqlsrv ' => [
' Driver ' => ' sqlsrv ',
' Host ' => env (' db_host ', ' localhost '),
' Database ' => env (' db_database ', ' Forge '),
' Username ' => env (' db_username ', ' Forge '),
' Password ' => env (' Db_password ', '),
' CharSet ' => ' UTF8 ',
' Prefix ' => ',
],

],
Migrate table names
' Migrations ' => ' migrations ',
Redis database-related configuration
' Redis ' => [

' Cluster ' => false,

' Default ' => [
' Host ' => ' 127.0.0.1 ',
' Port ' => 6379,
' Database ' => 0,
],

],

];
If you want to modify the database configuration information, modify the. env corresponding value. Our example tutorial uses the Homestead development environment default configuration and does not make changes. If you do not use homestead, you need to modify the corresponding configuration value based on the local configuration.

When we talk about installing homestead on Windows, we have demonstrated the database connection test, and how to use the local Navicat connection Homestead database, here is no longer to repeat, the following directly into how to use the database for the increase and deletion check.

2, Database initialization

We use the Artisan command in the project root to run laravel migration to generate the Users table and Password_reset table:

Corresponds to generating three tables in the database:

3, the use of DB façade for the increase and deletion of the search

3.1 Inserting data

We use DB façade to execute native SQL statements, insert operation using DB Façade Insert method, code as follows:

<?php

namespace App\http\controllers;

Use Illuminate\http\request;

Use app\http\requests;
Use App\http\controllers\controller;

Use DB;

Class TestController extends Controller
{

/**
* Display A listing of the resource.
*
* @return Response
*/
Public Function Index ()
{

Db::insert (' INSERT into users (ID, name, email, password) VALUES (?,?,?,?) ',
[1, ' laravel ', ' laravel@test.com ', ' 123 ']);
Db::insert (' INSERT into users (ID, name, email, password) VALUES (?,?,?,?) ',
[2, ' Academy ', ' academy@test.com ', ' 123 ']);

}
}
Enter Http://laravel.app:8000/test in the browser and insert two records in the database after successful execution:

Db-insert-data

3.2 Query Statements

The query operation uses the DB façade's Select method with the following code:

$user = Db::select (' select * from users where id =? ', [1]);
DD ($user);
Enter Http://laravel.app:8000/test in the browser address bar, and the output reads as follows:

You can see that the result returned by the Select query is an array. And each element in the array is a PHP object.

We can also see that parameter bindings are used when executing the query to avoid SQL injection. In addition, you can use named bindings:

$user = Db::select (' select * from users WHERE id =: id ', [': Id ' =>1]);
Effect.

3.3 UPDATE statement

Update table records can use the DB façade Update method, which returns the number of rows affected:

$affected = db::update (' Update users set name= "Laravelacademy" where name =? ', [' Academy ']);
Echo $affected;
The print result is 1, and returns 0 if no records are updated.

3.4 Delete Statements

Delete a table record the Delete method using the DB façade, similar to update, returns the number of rows that were deleted:

$deleted = DB::d elete (' Delete from users ');
Echo $deleted;
The print result is 2 and the table data is deleted by us.

3.5 General statements

In addition to these DML (Insert/update/delete) and DQL (SELECT) statements, the SQL statement includes DCL (Create/drop, etc.) and the DDL (Grant, and so on) to run the latter, We can call the DB Façade statement method:

Db::statement (' drop table users ');
When the statement is executed, the data table users are deleted.

4, listening to query events
In addition, we can listen to query events through the DB façade listen method, for example, when we log and debug, this will help us to determine the problem, you can register the listener in the service provider's boot method, For example, we define the listener in the Appserviceprovider boot method as follows:

/**
* Start all application services
*
* @return void
*/
Public Function boot ()
{
Db::listen (function ($sql, $bindings, $time) {
echo ' SQL statement execution: '. $sql. ', Parameters: '. Json_encode ($bindings). ', time consuming: '. $time. ' MS ';
});
}
So we run the following SQL statement:

Db::insert (' INSERT into users (ID, name, email, password) VALUES (?,?,?,?) ',
[3, ' laravelacademy ', ' laravel-academy@test.com ', ' 123 ']);
The browser will output the following:

SQL statement execution: INSERT into users (ID, name, email, password) VALUES (?,?,?,?), Parameters: [3, "Laravelacademy", "laravel-academy@test.c Om "," 123 "], time consuming: 1.26ms
5. Database transaction
Most of the time, we need to do a series of operations, and any one of these operations failure, the entire process fails, need to be back again, this time we will use database transactions.

DB façade provides two ways to support database transactions, one is to call the transaction method and then pass in the closure as a parameter, we will need to conduct the logic of transaction operations in the closure function:

Db::transaction (function () {
Db::table (' users ')->update ([' ID ' => 1]);
Db::table (' posts ')->delete ();
});
The other is to use BeginTransaction, rollback, and commit three methods to build a complete transaction operation:

Db::begintransaction ();
if ($somethingIsFailed) {
Db::rollback ();
return false;
}
Db::commit ();
In addition, transactions that are provided with the DB façade also support Query Builder and eloquent ORM database operations.