Use query constructor in Laravel to implement add, delete and change checking

Introduction

The last article introduced how to run a laravel project in windows environment. This article describes how to use the query constructor in laravel to add, delete and modify.

When reading this article, I acquiesce that you already have the following knowledge:

  • Understanding the basic syntax of php
  • Understanding database design
  • Understand common sql queries

text

Before realizing the censorship of additions, deletions and modifications, we should first prepare some steps:

  • php, nginx, mysql services properly enabled
  • New database and its data table

Open Services We open Wnmp.exe-> Start all described in the previous article
Then type the command on cmd:

D:/wnmp/Wnmp/php/php-cgi.exe -b 127.0.0.1:9000 -c D:/wnmp/Wnmp/php/php.ini

Create a new database, I call it shop, and then create two new tables
They are:

  • Goods - > Goods List
  • User - > user table

After the new data table was completed, I inserted several pieces of data randomly into it. The following figure:

Now that the preparatory steps are complete, let's open the testLaravel project described in the previous article.

Looking at Laravel documents shows that the database is configured in config/database.php.
Because we are a mysql database, we find the corresponding mysql options:

Looking at the mysql configuration of the database.php file, we can see that the configuration value is a variable, pointing to the. env file in the project root directory.

Open the. env file, we fill in the corresponding mysql real configuration, the following figure:

If you look at the corresponding key value, you will know what it means. Here you put down the database password, why password? Look at the Wnmp installation directory, we can see a readme.txt file, the text of the file is as follows:

Copyright (c) 2012 - 2019, Kurt Cancemi (kurt@x64architecture.com)
Donations are appreciated no matter if big or small.
https://wnmp.x64architecture.com/donate/

Wnmp is an easy Nginx, MariaDB and PHP environment for Windows.

The default login for MySQL/MariaDB/phpMyAdmin is: 
username: root
password: password

Note: you can change it via phpMyAdmin

According to the content of the file, we know that the password is: password, because it is the local development environment, there will be no password change.
But if it is a production environment, you must change the database password when you use the integrated environment package.

In addition, the specific catalogue structure of the project is not introduced in the article. It is suggested that you look at the official documents and get familiar with them.

The database configuration of the project has been allocated and the local shop database has been associated.

Now we need to configure the routing. All Laravel routing configurations are in the routes of the root directory. There are four routing configurations in the routes directory. We don't talk about the differences here. We only focus on web.php. We will add all the routes later in this file.

Seeing the students here also suggest to look at the laravel routing document, because the text is not very detailed, portal:
https://learnku.com/docs/lara...

By the way, I personally chose laravel as the php framework for in-depth learning. The elegant route of laravel is an important reason for my choice.

Let me write a few routes first. Let's feel it.

Route::get('test/queryUsers',['uses' => 'TestController@queryUsers']);
Route::post('test/queryUsers',['uses' => 'TestController@queryUsers']);
Route::get('test/queryUsers/{id}',['uses' => 'TestController@queryUsers']);

Route::match(['get','post'],'test/queryUsers',['uses' => 'TestController@queryUsers']);
Route::any('test/queryUsers',['uses' => 'TestController@queryUsers']);

We know that there are two well-known http requests, get and post.

  • a. Let's analyze the above routing in turn. First, look at Article 1:
Route::get('test/queryUsers',['uses' => 'TestController@queryUsers']);

This route specifies the get mode request, which is routed to test/queryUsers, and the logic goes to the queryUsers method of the TestController controller.

There are two noun controllers and methods.

Note: laravel is a framework of mvc, and the understanding of MVC needs to look up some information by oneself.

query

Controllers are in the App/Http/Controllers directory of the project. Here I add a TestController controller and a queryUsers method in TestController:

<?php

namespace App\Http\Controllers;
use app\Member;
use Illuminate\Support\Facades\DB;
use Symfony\Component\Routing\Annotation\Route;

class TestController extends Controller
{
    public function queryUsers() {
        $users = DB::table('user')->get();
        return $users;
    }
}

Let's look at the results first. Type in the address bar (domain name + routing): http://test.lara.com/test/queryUsers:

A bright line in the code:

DB::table('user')->get();

This line of code represents the query for all the data in the user table of the shop database, so we can see that all three records in the user table have been returned.

  • b. Now let's parse Article 2 of the above routing:
Route::post('test/queryUsers',['uses' => 'TestController@queryUsers']);

There is only one post difference between this route and the first route. Yes, this is a post request route. We know that the post request way can not be typed in the browser address bar to get the result. So we need to use a tool Postman, which is a good thing. If students have not used it or don't know it, we recommend it. Check it out and get familiar with it. It's not introduced in this article.

Now let's create a new request in Postman, as shown below:

We click send to get:

What? How can we not get the desired results? Obviously, what's wrong? Let's look at the routing documentation again.

https://learnku.com/docs/lara...

In the document, I looked up the relevant trace of the post word and saw:

Originally caused by CSRF, both front-end students and back-end students should be familiar with the word, cross-station Request Forgery

In order to solve the hidden danger of CSRF in laravel, we need to configure the CSRF whitelist by default. According to the documents, we know that the CSRF whitelist is configurated in the Verify CsrfToken middleware, where the Verify CsrfToken middleware is located:
App/Http/Middleware/VerifyCsrfToken.php

Open the file and add the CSRF whitelist.

protected $except = [
        'http://test.lara.com/test/queryUsers',
    ];

After that we open the postman and click send to see the data as expected:

  • c. Now let's parse Article 3 of the above routing:
Route::get('test/queryUsers/{id}',['uses' => 'TestController@queryUsers']);

It can be seen that the difference between this route is {id} at the end of the route. In development, we will have the requirement that the front end pass a user ID and the back end return the user information corresponding to this ID.

At this point, you need to modify the TestController controller queryUsers method:

public function queryUsers() {
        $id = request()->id;    // Obtaining parameters in this way
        $users = DB::table('user')->where('id', $id)->get();
        return json_encode($users);
    }

Explain this sql in vernacular: query the table table for records with id = $id (the value passed by the route).
View the results, in line with expectations:

  • d. Now let's parse Articles 4 and 5 of the above routing:
Route::match(['get','post'],'test/queryUsers',['uses' => 'TestController@queryUsers']);
Route::any('test/queryUsers',['uses' => 'TestController@queryUsers']);

I won't demonstrate these two routes. Explain them separately. Routing in match is the specified request mode, one or more.
Anything matches any way, whether through get or post, we can get the result we want.

Let's look at the goods table I designed above.

We can see that these two data do not know who the created user is, but there is a uid value. At this time, we hope that an interface can not only return commodity information but also let me know who the creator is. At this time, we need to use multi-table joining. We add a new route:

Route::get('test/queryGoods',['uses' => 'TestController@queryGoods']);

Then a new queryGoods method is added to TestController:

public function queryGoods() {
        $goods = DB::table('goods')
                        ->leftJoin('user', 'goods.uid', '=', 'user.id')
                        ->get();
        return json_encode($goods);
    }

Return data as expected:

[{
        id: 1,
        uid: 1,
        name: "Testing commodity 1",
        desc: null,
        img_urls: null,
        price: "99.00",
        old_price: "78.00",
        real_name: "economic expert",
        nickname: "Poor shake.",
        head_url: null,
        age: null,
        gender: null,
        level: null
    },
    {
        id: 2,
        uid: 2,
        name: "Testing Commodity 2",
        desc: null,
        img_urls: null,
        price: "290.00",
        old_price: "389.00",
        real_name: "Ouyang Xinxin",
        nickname: "Xiao Xinxin",
        head_url: null,
        age: null,
        gender: null,
        level: null
    }
]

But all fields of user information are returned. Sometimes we just need to return the specified fields for security. For example, we only need to return the real_name and head_url fields in the user table. Then we need to do some field constraints:

public function queryGoods() {
        $goods = DB::table('goods')
                        ->leftJoin('user', 'goods.uid', '=', 'user.id')
                        ->select('goods.*', 'user.real_name', 'user.head_url')
                        ->get();
        return json_encode($goods);
    }

Focus on this line of code:

select('goods.*', 'user.real_name', 'user.head_url')

This line of code represents the return of all fields in the goods table, as well as the real_name and head_url fields in the user table, and achieves the desired results perfectly.

Newly added

So far, the query has almost been written, and we are starting to add new data to the table.

We added a route test/addUser for post requests:

Route::post('test/addUser',['uses' => 'TestController@addUser']);

Add CSRF Routing Whitelist

protected $except = [
        'http://test.lara.com/test/queryUsers',
        'http://test.lara.com/test/addUser',
    ];

Then add an addUser method in TestController:

public function addUser() {
        $real_name = request()->real_name;
        $nickname = request()->nickname;

        DB::table('user')->insert(
            ['real_name' => $real_name, 'nickname' => $nickname]
        );

        $users = DB::table('user')->get();
        return json_encode($users);
    }

Open postman and add parameters:

{"real_name":"Liu Yifei","nickname":"Phillip Phillips"}

Click send to find that the data is inserted correctly as expected.

The query constructor provides insert methods for inserting records into the database. Insert method receives field names and field values in array form for insertion operation

DB::table('user')->insert(
            ['real_name' => $real_name, 'nickname' => $nickname]
        );

edit

We add a put request routing test/updateUser:

Route::put('test/updateUser',['uses' => 'TestController@updateUser']);

Similarly add CSRF routing whitelist

protected $except = [
        'http://test.lara.com/test/queryUsers',
        'http://test.lara.com/test/addUser',
        'http://test.lara.com/test/updateUser',
    ];

Then an updateUser method is added to TestController:

public function updateUser() {
        $id = request()->id;
        $nickname = request()->nickname;
        
        DB::table('user')
            ->where('id', $id)
            ->update(['nickname' => $nickname]);

        $users = DB::table('user')->get();
        return json_encode($users);
    }

Open postman, we know that the ID of Liu Yifei returned from the newly added record is 4. We update its nickname according to id, and add request parameters in postman:

{"id":"4", "nickname": "Little Afee~"}

Click send to find that the data is updated correctly as expected.

The query constructor provides an update method for updating existing records. Accept an array containing fields and values to be updated

->where('id', $id)->->update(['nickname' => $nickname]);

delete

We add a routing test/deleteUser for delete requests:

Route::delete('test/deleteUser',['uses' => 'TestController@deleteUser']);

Similarly add CSRF routing whitelist

protected $except = [
        'http://test.lara.com/test/queryUsers',
        'http://test.lara.com/test/addUser',
        'http://test.lara.com/test/updateUser',
        'http://test.lara.com/test/deleteUser',
    ];

Then add a deleteUser method in TestController:

public function deleteUser() {
        $id = request()->id;

        DB::table('user')
            ->where('id', $id)
            ->delete();

        $users = DB::table('user')->get();
        return json_encode($users);
    }

Open postman. Now we want to delete Liu Yifei's record in the database. We know her id is 4. We delete it according to id and add request parameters:

{"id":"4"}

Click send to find that the data is deleted correctly as expected (no record with id 4 is available).

The query constructor in laravel can delete records from tables using the delete method. Add where clause to constrain the condition of delete:

->where('id', $id)->delete();

There are many ways not introduced in the article. It is suggested that students who need to learn read the following documents, portal:

https://learnku.com/docs/lara...

Ending

So far, this article is over. More front-end / php / photography related articles about public concern number: I would like to make light of it.
Reader's attention is the driving force of my original work.

Tags: PHP Database Laravel MySQL

Posted on Thu, 05 Sep 2019 05:01:17 -0700 by Nameless12