Three Ways of Laravel 5 Operating Database

Keywords: Programming Database Laravel PDO SQL

1. DB facade (original lookup)

        // query
        $objectArray=DB::select('select * from student');
        foreach ($objectArray as $obj){
            echo $obj->id;
        // insert
        $bool=DB::insert('insert into student(name,age) values(?,?)',['tom',18]);
        // modify
        $num=DB::update('update student set age=? where name=?',[20,'tom']);
        // delete
        $num=DB::delete('delete from student where id=?',[1001]);

2. Query Constructor

Laravel query constructor provides a convenient and smooth interface for establishing and executing database lookup syntax. pdo parameter bindings are used to protect the application from sql injection, so the incoming parameters do not need to escape special characters. Basically, it can satisfy all database operations, and it can be implemented on all supported database systems.


        // Query all data
        foreach ($objectArray as $object){
            echo $object->id;

        // Query data according to conditions
        foreach ($objectArray as $object){
            echo $object->id;

        // Query data according to multiple conditions
        $objectArray=DB::table('student')->whereRaw('id > ? and age = ?',[1001,15])->get()->toArray();
        foreach ($objectArray as $object){
            echo $object->id;

        // Take out the first data (ascending/descending)
        echo $object->id;

        // Query specified field names (you can specify field names as array subscripts, not default numeric subscripts, plus instead of lists method)

        // Query one or more fields specified

        // Query data according to specified number of records and execute corresponding methods
        DB::table('student')->orderBy('id','desc')->chunk(2,function ($objects){
            foreach ($objects as $object){
                if ($object->id==1004){
                    echo 'find';


        // Single insertion
        // Insert and get id
        // Multiple inserts


       // Single modification

        // Running this statement increases by itself (default 1)
        // Running this statement increases by itself (by 3)
        // Running this statement decreases by default (1)
        // Running this statement decreases itself (decreases 3)

        // Conditional self-reduction

        // Reduce and modify fields according to conditions


        // Single deletion
        // Delete by condition
        // Delete the entire table

Aggregate function

        // Number of Statistical Records
        // Specify field maximum
        // Specify field minimum
        // Specified field average
        // Specifies the sum of fields

Eloquent ORM

Laravel's Eloquent ORM is an ActiveRecord implementation for database operations. Each data table has a corresponding model for data table interaction

First, create a new model class file, which reads as follows:

namespace App;
use Illuminate\Database\Eloquent\Model;
class Student extends Model
    // Specify the database table name
    protected $table='student';
    // Specify primary key
    protected $primaryKey='id';
    // Automatic maintenance timestamp
    public $timestamps = true;
    // Specify fields that allow batch assignment (you need to specify allowable fields when using the create method to increase batches)
    protected $fillable=['name','age'];
    // Specify fields that do not allow batch assignment
    protected $guarded=[];

    // Automatic Formatting Time
    protected function getDateFormat()
        return time();
    // Direct return timestamp (getDateFormat and asDateTime coexist, asDateTime takes effect)
    protected function asDateTime($value)
        return $value;

Next, call the new model class in the controller


        // Query all data
        // Query by primary key
        // No record error was found

        // [Query constructor] Query all data, omit the specified table name in ORM, and use the rest in the same way.


        // New data added to the model
        $student=new Student();

        // New batch data added by model creation method
        // Search for records by attributes, add if not
        // Find records by attributes, create new instances if not, and save() manually if you need to save them to the database.

        // [Query Constructor] Insert Data


        // Model modification data

        // [Query Constructor] Modify according to conditions


        // Model deletion data
        // Delete by primary key (also in array form)

        // [Query Constructor] Delete by Conditions

Posted by jboy6t9 on Mon, 30 Sep 2019 01:06:13 -0700