php laravel db增删改查(使用sql文)

php laravel db增删改查(使用sql文) 2022-07-27 281

参考https://qiita.com/Yorinton/items/3d2e3c283b4cd8dbc955 1，首先配置/.env文件（也可配置/config/database.php），sample如下

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=db名
DB_USERNAME=db用户名
DB_PASSWORD=db密码

2，编写/routes/web.php

Route::group([prefix => user], function () {
          
   
    Route::get(/get, [TestController::class, get]);
    Route::get(/del, [TestController::class, del]);
    Route::get(/update, [TestController::class, update]);
    Route::get(/add, [TestController::class, add]);
    Route::get(/stmt, [TestController::class, stmt]);
});

3，编写/app/Http/Controllers/TestController.php

<?php

namespace AppHttpControllers;

use IlluminateHttpRequest;
use IlluminateSupportFacadesDB;

class TestController extends Controller
{
          
   
    //http://localhost:3000/public/user/add?name=gg
    public function add(Request $request)
    {
          
   
        $db = DB::table(users);
        //插入多条数据，返回bool
        //写法1
        // $result=$db->insert([
        //     [name=>$request->input(name, kitty1),age=>1],
        //     [name=>$request->input(name2, kitty2),age=>2],
        // ]);
        //写法2
        $result = DB::insert(insert into users (name,age) values (?,?),(?,?), [$request->input(name, kitty1), 1, $request->input(name2, kitty2), 2]);
        echo  $result . <br/>;
        //插入一条数据，返回其主键
        $newId = $db->insertGetId([name => $request->input(name3, kitty3)]);
        echo $newId;
    }

    //http://localhost:3000/public/user/del?id=37
    public function del(Request $request)
    {
          
   
        $result = DB::delete(delete from users WHERE age = ?, [$request->input(age, 0)]);
        echo  $result . <br/>;
    }

    //http://localhost:3000/public/user/update?id=36&name=john
    public function update(Request $request)
    {
          
   
        $result = DB::update(update users set name = ? where age = ?, [$request->input(name), $request->input(age)]);
        echo  $result . <br/>;
    }

    //http://localhost:3000/public/user/get?id=36
    public function get(Request $request)
    {
          
   
        $users = DB::select("SELECT * FROM users");
        var_dump($users);
        $user = DB::selectOne("SELECT * FROM users WHERE id = ?", [$request->input(id)]);
        echo <br/>;
        var_dump($user);
    }
    
    //http://localhost:3000/public/user/stmt?sql=delete%20from%20users%20where%20id=41
    public function stmt(Request $request)
    {
          
   
        echo $request->input(sql) . <br/>;
        $result = DB::statement($request->input(sql));
        var_dump($result);
    }
}

传入到sql文的参数全写在[ ]里，且按sql文的？的顺序排列。