Laravel How to operate the database? Three ways to Laravel database operations (code)

Laravel is the PHP Web development Framework (PHP Web framework). It frees you from messy code, it helps you build a perfect web app, and every line of code can be concise and expressive. So, how does the Laravel framework operate the database? Please take a look at the specific content.

Laravel provides 3 ways to manipulate databases: DB facade (raw), query constructors, and eloquent ORM.

The configuration file for the database is in the database.php of the Config directory. Open this file and find the configuration item for MySQL.

Here's an env, which actually calls the. env file in the Laravel root directory, which stores information about the Laravel database configuration. Open it. Modify the database information for the project.

Please build a database of your own, where the database contains the Vipinfo table and inserts some data for easy use below. The structure of the table is as follows.

As the name implies: This table is a membership table, respectively, the member ID (primary key), member name, membership type, membership points and other fields.

DB facade of database operation

Create a new student controller in the App->http->controllers directory , studentcontroller.php. The studentcontroller.php code is as follows:

<?php namespace App\http\controllers;use illuminate\support\facades\db;class Studentcontroller extends Controller { }

1.laravel database queries

Add a Test1 method to the student controller, the query uses the static method of the DB Class select (), the argument is a native SQL statement, and returns a two-dimensional array. DD () is a method provided by Laravel that allows an array to be displayed in the form of a node tree. The specific code is as follows:

Public Function test1 () {    $student =db::select ("SELECT * from Vipinfo");    Returns a two-dimensional array      $student    var_dump ($student);        Outputs the result        DD ($student) as a node tree;}

Routing configuration: route::get (' test1 ', [' uses ' = ' studentcontroller@test1 ']);

URL access: http://localhost/laravel/public/index.php/test1, the result will be printed.

2. New operations

Add the static method of the DB Class Insert (), the first parameter is the SQL statement, the second parameter is an array, the array to put the data to be inserted. Over here? is a placeholder, through the database interface layer PDO, to achieve the purpose of anti-SQL injection. The result of the execution is returned. The insert succeeds returns TRUE, otherwise false.

Public Function test1 () {    $student =db::select ("SELECT * from Vipinfo");    Returns a two-dimensional array      $student    var_dump ($student);        Outputs the result        DD ($student) as a node tree;}

3. Update operations

The update uses the static method of the DB Class update (), the first argument is the SQL statement, the second argument is an array, and the elements in the array correspond to the question mark in the SQL statement. The update returns true successfully.

    $bool =db::update (' Update vipinfo set vip_fenshu=? where vip_id=? ', [700,5]);    Var_dump ($bool);  Update successful Returns True

4. Delete operation

Delete uses the static method of the DB Class Delete (), the first argument is the SQL statement, the second argument is an array, and the elements in the array correspond to the question mark in the SQL statement. Returns the number of rows that were deleted.

    $num =db::d elete (' delete from Vipinfo where vip_id=? ', [5]);    Echo $num;

Second, the database Operation query constructor

The Laravel query constructor provides a convenient and smooth interface for creating and executing database lookup syntax. The PDO parameter binding is used to exempt the application from SQL injection, so incoming parameters do not require extra escaping special characters. Basically, all database operations can be satisfied and can be performed on all supported database systems.

1. Using Query Builder to implement additions and deletions

Also test the following code in the student controller:

(1) New

    $bool =db::table ("Vipinfo")->insert ([' vip_id ' =>6, ' vip_name ' = ' zls ', ' vip_type ' = ' travel ', ' vip_fenshu ' = >800]);    Echo $bool; return bool Value    //If you want a new ID, use the Insertgetid method    $id =db::table ("Vipinfo")->insertgetid ([' vip_id ' =>5, ' Vip_ Name ' = ' wyp ', ' vip_type ' = ' travel ', ' Vip_fenshu ' =>800]);    echo $id;    Insert multiple Data    $bool =db::table ("Vipinfo")->insert ([    [' vip_id ' =>5, ' vip_name ' = ' wyp ', ' vip_type ' = ' = ') Travel ", ' Vip_fenshu ' =>800],    [' vip_id ' =>6, ' vip_name ' = ' zls ', ' vip_type ' = ' travel ', ' Vip_fenshu ' =>800],    ]);    Echo $bool; return bool Value

(2) Modification

$bool =db::table ("Vipinfo")->where (' vip_id ', 6)->update ([' Vip_fenshu ' =>500]); echo $bool;//self-increment $bool=db:: Table ("Vipinfo")->where (' vip_id ', 6)->increment ("Vip_fenshu");//self-increment 1$bool=db::table ("Vipinfo")->where (' vip_id ', 6)->increment ("Vip_fenshu", 3);//self-increment 3echo $bool;//self-reduction $bool=db::table ("Vipinfo")->where (' vip_id ', 6)- >decrement ("Vip_fenshu");//Since 1$bool=db::table ("Vipinfo")->where (' vip_id ', 6)->decrement ("Vip_fenshu", 3) ;//self-increment 3echo $bool;//increment to modify other fields $bool=db::table ("Vipinfo")->where (' vip_id ', 6)->increment ("Vip_fenshu", 3,[' Vip_name ' + ' Dbdibi ');//self-increment 3

(3) Delete

$num =db::table ("Vipinfo")->where (' vip_id ', 6)->delete ();//delete 1 $num=db::table ("Vipinfo")->where (' Vip_ ID ', ' > ', 4)->delete ();//delete multiple echo $num;  Number of rows deleted $num=db::table ("Vipinfo")->truncate ();//delete whole table, cannot recover, use cautiously

(4) Query

Get () returns multiple data $student=db::table ("Vipinfo")->get ();  Var_dump ($student);  First () returns 1 data $student=db::table ("Vipinfo")->first (); Result set the first record $student=db::table ("Vipinfo")->orderby (' vip_id ', ' desc ')->first ();//Sort vip_id in reverse order var_dump ($  Student); where () condition query $student=db::table ("Vipinfo")->where (' vip_id ', ' >= ', 2)->get (); One condition $student =db::table ("Vipinfo")->whereraw (' vip_id>? and Vip_fenshu >=? ', [2,300])->get (); Multiple conditions DD ($student);//pluck () Specifies the field, followed by Get$student=db::table ("Vipinfo")->pluck (' Vip_name ');dd($student);   Lists () specifies a field that can be specified as subscript $student=db::table ("Vipinfo")->lists (' Vip_name ', ' vip_id ');   Specify vip_id as subscript dd ($student), $student =db::table ("Vipinfo")->lists (' Vip_name '); Do not specify subscript, default subscript starting from 0//select () Specify a field $student=db::table ("Vipinfo")->select (' Vip_name ', ' vip_id ')->get ();dd($ Student);//chunk () Check n $student=db::table ("Vipinfo")->chunk (2,function ($students) {//2 var_dump per check ($students    );  if (...) return false; Use return under certain conditions will not go downChecked}); 

2. Using Aggregate functions

COUNT () Statistics record number of $nums=db::table ("Vipinfo")->count (), Echo $nums,//max () the maximum value of a field, and Min is the minimum value $max=db::table (" Vipinfo ")->max (" Vip_fenshu "); Echo $max;//avg () The average of a field $avg=db::table (" Vipinfo ")->avg (" Vip_fenshu "); echo $ Avg;//sum () A field and $sum=db::table ("Vipinfo")->sum ("Vip_fenshu"); Echo $sum;

Iv. Database Operations-eloquent ORM

1. Introduction, model building and query data

Summary: Laravel's eloquent ORM is a ActiveRecord implementation for database operations. Each data table has a corresponding model for the data table interaction.

Build a model, create a student model in the app directory, i.e. student.php, without any suffix.

<?phpnamespace app;use illuminate\database\eloquent\model;class Student extends model{    //Specify table name protected $table = ' vipinfo ';//Specify PRIMARY key protected $primaryKey = ' vip_id ';}

Add a Test3 method to the student controller, configure the routing Route::get (' test3 ', [' uses ' = ' studentcontroller@test3 ']);

The Public Function test3 () {//All () method queries all data $studnets=student::all ();dd($studnets);//find () queries one, based on the primary key. Findorfail () throws an exception when finding a record that does not exist $student=student::find (5);  The record of the primary key is 5 var_dump ($student [' attributes ']);//The use of the query constructor, omitting the specified table name $student=student::get ();  Var_dump ($student);}

2. New data, custom timestamp, batch assignment

(1) Use the Save method to add

Laravel will maintain created_at,updated_at two fields by default, both of which are stored in timestamps and integer 11 bits, so you need to add both fields to the database when you use them. If this feature is not required, simply add a property to the model: public $timestamps =false; And a method to save the current timestamp to the database

    protected function GetDateFormat () {        return time ();    }

That way, you don't need those two fields.

The controller writes:

    $student =new student ();    Set data    $student->vip_name= ' xiaoming ';    $student->vip_type= ' travel ';    $student->vip_fenshu=900;    $bool = $student->save (); Save the    echo $bool;

When the timestamp of a record is obtained from the database, the default is to format a good timestamp by date, and if you want to get the original timestamp, add the Asdatetime method to the model.

    protected function Asdatetime ($val) {        return $val;    }

(2) when the Create method is added, it needs to be added in the model:

Protected $fillable =[' vip_name ', ' Vip_fenshu ', ' Vip_type '];   Fields that allow bulk assignment

The controller writes:

Student::create ([' vip_name ' = ' mmm ', ' Vip_fenshu ' =>999, ' vip_type ' = ' travel ']);

This will add success!

(3) Firstorcreate () search for records with attributes, if not, add

    $student =student::firstorcreate ([' vip_name ' = ' mmm ']);    Echo $student;

(4) Firstornew () finds records with attributes, and creates a new instance if none. If you need to save, call the Save Method Yourself ()

    $student =student::firstornew ([' vip_name ' = ' mmm ']);    $student->save ();    Echo $student;

3. Modify the data

    Update data by model    $student =student::find (2);    $student->vip_fenshu=10000;    $student->save (); Returns the BOOL value    //updated by the Query builder    $num =student::where (' vip_id ', ' > ', 2)->update ([' Vip_fenshu ' =>2000]);    Echo $num; Returns the number of rows updated

4. Delete data

    (1) Delete data by model    $student =student::find (one);    $student->delete (); return bool Value    //(2) Delete $num by primary key    =student::d Estroy (10);//delete a record with primary key 10    echo $num;//Returns the number of rows deleted    $num =student ::d Estroy (10,5); Delete more than one or $num=student::d Estroy ([10,5]);    Echo $num; Returns the number of rows deleted