Laravel connect MySQL Database ทำ RESTful API CRUD [เพิ่ม(create) ,อ่าน(read) , แก้ไข(update), ลบ(delete)] EP6

Sharing is caring!

Laravel Series เดินทางมาถึง EP6 ซึ่งถ้าใครยังไม่ได้อ่านบทความ EP 1 – 5 สามารถเข้าได้ที่ลิ้งใต้ล่างนี้

  1. Laravel เริ่มติดตั้งและสร้าง route ง่าย ๆ EP1
  2. Laravel แนะนำโครงสร้างภายในโปรเจค อธิบายแต่ละส่วนการทำงาน EP2
  3. Laravel ใช้งาน views blade template engine EP3
  4. Laravel รู้จัก Routes และ การรับค่าจาก HttpRequest EP4
  5. Laravel สร้าง RESTful API ง่าย ๆ ด้วย Resource Routes Controller EP5

บทความนี้จะมาทำให้ application ของเราต่อกับฐานข้อมูล (database) ของ MySQL และเมื่อต่อได้แล้วจะมาใช้งาน migrate และมาทำระบบ CRUD ของข้อมูล Photos กัน

สร้าง Schema และ Migrate Table ด้วย artisan migrate

  1. สร้าง schema ด้วยคำสั่ง
CREATE SCHEMA `db_poolsawat` DEFAULT CHARACTER SET utf8 ;

2. แก้ไขไฟล์ .env

...
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db_poolsawat
DB_USERNAME=root
DB_PASSWORD=p@ssw0rd
...

3. ทดสอบการต่อ connection ทำตามขั้นตอนข้างล่าง ถ้าขึ้นแบบนี้แสดงว่าต่อสำเร็จ 

$ php artisan tink
// Psy Shell v0.10.4 (PHP 7.3.7 — cli) by Justin Hileman

$ DB::connection()->getPdo();
/* 
PDO {#4182
     inTransaction: false,
     attributes: {
       CASE: NATURAL,
       ERRMODE: EXCEPTION,
       AUTOCOMMIT: 1,
       PERSISTENT: false,
       DRIVER_NAME: "mysql",
       SERVER_INFO: "Uptime: 433  Threads: 4  Questions: 152  Slow queries: 0  Opens: 209  Flush tables: 3  Open tables: 130  Queries per second avg: 0.351",
       ORACLE_NULLS: NATURAL,
       CLIENT_VERSION: "mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $",
       SERVER_VERSION: "8.0.21",
       STATEMENT_CLASS: [
         "PDOStatement",
       ],
       EMULATE_PREPARES: 0,
       CONNECTION_STATUS: "127.0.0.1 via TCP/IP",
       DEFAULT_FETCH_MODE: BOTH,
     },
   }
*/

4. generate migrations file ด้วยคำสั่งเหล่านี้

php artisan make:migration create_photos_table

4.1 ตรวจสอบไฟล์ที่ถูกสร้างขึ้นมาที่ database/migrations/*_create_photos_table.php (* คือวันที่ generate file) จากนั้นเปิดไฟล์ขึ้นมาแก้ไข ตามนี้

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePhotosTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('photos', function (Blueprint $table) {
            $table->id();
            $table->string('photo_name');
            $table->integer('photo_size');
            $table->string('photo_url');
            $table->enum('photo_status', ['active', 'inactive']);
            $table->dateTime('photo_date', 0);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('photos');
    }
}

ในการกำหนด datatype ของ column ตารางมีรูปแบบที่หลากหลาย มี function รองรับอยู่แล้ว สำหรับการกำหนดสร้าง สามารถดูเพิ่มเติมได้จากตารางนี้

Available Column Types

The schema builder contains a variety of column types that you may specify when building your tables:

CommandDescription
$table->id();Alias of $table->bigIncrements('id').
$table->foreignId('user_id');Alias of $table->unsignedBigInteger('user_id').
$table->bigIncrements('id');Auto-incrementing UNSIGNED BIGINT (primary key) equivalent column.
$table->bigInteger('votes');BIGINT equivalent column.
$table->binary('data');BLOB equivalent column.
$table->boolean('confirmed');BOOLEAN equivalent column.
$table->char('name', 100);CHAR equivalent column with a length.
$table->date('created_at');DATE equivalent column.
$table->dateTime('created_at', 0);DATETIME equivalent column with precision (total digits).
$table->dateTimeTz('created_at', 0);DATETIME (with timezone) equivalent column with precision (total digits).
$table->decimal('amount', 8, 2);DECIMAL equivalent column with precision (total digits) and scale (decimal digits).
$table->double('amount', 8, 2);DOUBLE equivalent column with precision (total digits) and scale (decimal digits).
$table->enum('level', ['easy', 'hard']);ENUM equivalent column.
$table->float('amount', 8, 2);FLOAT equivalent column with a precision (total digits) and scale (decimal digits).
$table->geometry('positions');GEOMETRY equivalent column.
$table->geometryCollection('positions');GEOMETRYCOLLECTION equivalent column.
$table->increments('id');Auto-incrementing UNSIGNED INTEGER (primary key) equivalent column.
$table->integer('votes');INTEGER equivalent column.
$table->ipAddress('visitor');IP address equivalent column.
$table->json('options');JSON equivalent column.
$table->jsonb('options');JSONB equivalent column.
$table->lineString('positions');LINESTRING equivalent column.
$table->longText('description');LONGTEXT equivalent column.
$table->macAddress('device');MAC address equivalent column.
$table->mediumIncrements('id');Auto-incrementing UNSIGNED MEDIUMINT (primary key) equivalent column.
$table->mediumInteger('votes');MEDIUMINT equivalent column.
$table->mediumText('description');MEDIUMTEXT equivalent column.
$table->morphs('taggable');Adds taggable_id UNSIGNED BIGINT and taggable_type VARCHAR equivalent columns.
$table->uuidMorphs('taggable');Adds taggable_id CHAR(36) and taggable_type VARCHAR(255) UUID equivalent columns.
$table->multiLineString('positions');MULTILINESTRING equivalent column.
$table->multiPoint('positions');MULTIPOINT equivalent column.
$table->multiPolygon('positions');MULTIPOLYGON equivalent column.
$table->nullableMorphs('taggable');Adds nullable versions of morphs() columns.
$table->nullableUuidMorphs('taggable');Adds nullable versions of uuidMorphs() columns.
$table->nullableTimestamps(0);Alias of timestamps() method.
$table->point('position');POINT equivalent column.
$table->polygon('positions');POLYGON equivalent column.
$table->rememberToken();Adds a nullable remember_token VARCHAR(100) equivalent column.
$table->set('flavors', ['strawberry', 'vanilla']);SET equivalent column.
$table->smallIncrements('id');Auto-incrementing UNSIGNED SMALLINT (primary key) equivalent column.
$table->smallInteger('votes');SMALLINT equivalent column.
$table->softDeletes('deleted_at', 0);Adds a nullable deleted_at TIMESTAMP equivalent column for soft deletes with precision (total digits).
$table->softDeletesTz('deleted_at', 0);Adds a nullable deleted_at TIMESTAMP (with timezone) equivalent column for soft deletes with precision (total digits).
$table->string('name', 100);VARCHAR equivalent column with a length.
$table->text('description');TEXT equivalent column.
$table->time('sunrise', 0);TIME equivalent column with precision (total digits).
$table->timeTz('sunrise', 0);TIME (with timezone) equivalent column with precision (total digits).
$table->timestamp('added_on', 0);TIMESTAMP equivalent column with precision (total digits).
$table->timestampTz('added_on', 0);TIMESTAMP (with timezone) equivalent column with precision (total digits).
$table->timestamps(0);Adds nullable created_at and updated_at TIMESTAMP equivalent columns with precision (total digits).
$table->timestampsTz(0);Adds nullable created_at and updated_at TIMESTAMP (with timezone) equivalent columns with precision (total digits).
$table->tinyIncrements('id');Auto-incrementing UNSIGNED TINYINT (primary key) equivalent column.
$table->tinyInteger('votes');TINYINT equivalent column.
$table->unsignedBigInteger('votes');UNSIGNED BIGINT equivalent column.
$table->unsignedDecimal('amount', 8, 2);UNSIGNED DECIMAL equivalent column with a precision (total digits) and scale (decimal digits).
$table->unsignedInteger('votes');UNSIGNED INTEGER equivalent column.
$table->unsignedMediumInteger('votes');UNSIGNED MEDIUMINT equivalent column.
$table->unsignedSmallInteger('votes');UNSIGNED SMALLINT equivalent column.
$table->unsignedTinyInteger('votes');UNSIGNED TINYINT equivalent column.
$table->uuid('id');UUID equivalent column.
$table->year('birth_year');YEAR equivalent column.
https://laravel.com/docs/8.x/migrations

4.2 หลังจากที่แก้ไขไฟล์ *_create_photos_table.php เป็นที่เรียบร้อยแล้ว ให้ รันคำสั่งต่อไปนี้เพิ่มเพิ่ม migrate table (artisan จะทำการไป create table ให้ที่ database ) 

php artisan migrate

เปรียบเหมือนว่าเรากำลังใช้คำสั่งชุดนี้เพื่อสร้างตาราง

CREATE TABLE `photos` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `photo_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo_size` int NOT NULL,
  `photo_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo_status` enum('active','inactive') COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo_date` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

5. สร้าง Model class ด้วย artisan cli

php artisan make:model Photo

ตรวจสอบไฟล์ที่ app/Models/Photo.php

5.1 แก้ไขไฟล์ app/Models/Photo.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Photo extends Model
{
  use HasFactory;
  protected $fillable = [
    'created_at', 'id', 'photo_date', 'photo_name', 'photo_size',
    'photo_status', 'photo_url', 'updated_at'
  ];
}

6. สร้าง Controller class ด้วย artisan cli

php artisan make:controller PhotoController --resource

ตรวจสอบไฟล์ที่ app/Http/Controller/PhotoController.php

6.1 แก้ไขไฟล์ app/Http/Controller/PhotoController.php ตามข้างล่างนี้ ส่วนนี้จะได้มี function RESTful API template ที่ได้จากการ artisan cli แล้ว บทความ “Laravel สร้าง RESTful API ง่าย ๆ ด้วย Resource Routes Controller EP5” ได้อธิบายไว้บ้างแล้ว บทความนี้จะขอข้ามส่วนนี้ไป

<?php

namespace App\Http\Controllers;

use App\Models\Models\Photo as ModelsPhoto;
use App\Models\Photo;
use Carbon\Carbon;
use Illuminate\Http\Request;

class PhotoController extends Controller
{
  /**
   * Display a listing of the resource.
   *
   * @return \Illuminate\Http\Response
   */
  public function index()
  {
    return response()->json(['name' => 'index', 'photos' => Photo::all()]);
  }

  /**
   * Show the form for creating a new resource.
   *
   * @return \Illuminate\Http\Response
   */
  public function create()
  {
    $photo = new Photo();
    $photo->photo_name = 'laravel';
    $photo->photo_size = 1024;
    $photo->photo_url = 'https://laravel.com/img/logotype.min.svg';
    $photo->photo_status = 'active';
    $photo->photo_date = Carbon::now();
    return response()->json(['name' => 'create', 'status' => $photo->save()]);
  }

  /**
   * Store a newly created resource in storage.
   *
   * @param  \Illuminate\Http\Request  $request
   * @return \Illuminate\Http\Response
   */
  public function store(Request $request)
  {
    $photo = new Photo($request->all());
    $photo->photo_date = Carbon::now();
    return response()->json(['name' => 'store', 'payload' => $request->all(), 'status' => $photo->save()]);
  }

  /**
   * Display the specified resource.
   *
   * @param  int  $id
   * @return \Illuminate\Http\Response
   */
  public function show($id)
  {
    $photo = Photo::find($id);
    return response()->json(['name' => 'show', 'id' => $id, 'photo' => $photo]);
  }

  /**
   * Show the form for editing the specified resource.
   *
   * @param  int  $id
   * @return \Illuminate\Http\Response
   */
  public function edit($id)
  {
    $photo = Photo::find($id);
    return response()->json(['name' => 'edit', 'id' => $id, 'photo' => $photo]);
  }

  /**
   * Update the specified resource in storage.
   *
   * @param  \Illuminate\Http\Request  $request
   * @param  int  $id
   * @return \Illuminate\Http\Response
   */
  public function update(Request $request, $id)
  {
    $photo = Photo::find($id);
    $photo->photo_name = $request->photo_name;
    $photo->photo_size = $request->photo_size;
    $photo->photo_url = $request->photo_url;
    $photo->photo_status = $request->photo_status;
    $photo->photo_date = Carbon::now();
    return response()->json(['name' => 'update', 'status' => $photo->save(),  'payload' => $request->all(), 'id' => $id]);
  }

  /**
   * Remove the specified resource from storage.
   *
   * @param  int  $id
   * @return \Illuminate\Http\Response
   */
  public function destroy($id)
  {
    $photo = Photo::find($id);
    return response()->json(['name' => 'destroy', 'status' => $photo->delete(), 'id' => $id]);
  }
}

7. แก้ไขไฟล์ routes/api.php เพิ่มโค๊ด 1 บรรทัด

...
Route::resource('photos', 'App\Http\Controllers\PhotoController');
...

8. ทดสอบ RESTful API

ก่อนที่จะทดสอบติดตั้ง npm i -g json เพื่อ pritty json response เพื่อความสวยงาม

$ curl localhost:8000/api/photos | json

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    28    0    28    0     0     65      0 --:--:-- --:--:-- --:--:--    65
{
  "name": "index",
  "photos": []
}
/* fetch photos all */
$ curl localhost:8000/api/photos/create | json

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    31    0    31    0     0     73      0 --:--:-- --:--:-- --:--:--    73
{
  "name": "create",
  "status": true
}
/* create photo */
$ curl -X POST -H "Content-Type: application/json" -d "{\"photo_name\":\"laravel\",\"photo_size\":1024,\"photo_url\":\"https://laravel.com/img/logotype.min.svg\",\"photo_status\":\"active\",\"photo_date\":\"2020-10-06 10:24:01\"}" http://127.0.0.1:8000/api/photos | json

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   357    0   201  100   156   1116    866 --:--:-- --:--:-- --:--:--  1994
{
  "name": "store",
  "payload": {
    "photo_name": "laravel",
    "photo_size": 1024,
    "photo_url": "https://laravel.com/img/logotype.min.svg",
    "photo_status": "active",
    "photo_date": "2020-10-06 10:24:01"
  },
  "status": true
}
/* create photo with payload */
$ curl localhost:8000/api/photos/1 | json

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   286    0   286    0     0    722      0 --:--:-- --:--:-- --:--:--   722
{
  "name": "show",
  "id": "1",
  "photo": {
    "id": 1,
    "photo_name": "laravel",
    "photo_size": 1024,
    "photo_url": "https://laravel.com/img/logotype.min.svg",
    "photo_status": "active",
    "photo_date": "2020-10-06 10:32:24",
    "created_at": "2020-10-06T10:32:24.000000Z",
    "updated_at": "2020-10-06T10:32:24.000000Z"
  }
}
/* fetch photo by id */
$ curl localhost:8000/api/photos/1/edit | json

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   286    0   286    0     0    709      0 --:--:-- --:--:-- --:--:--   709
{
  "name": "edit",
  "id": "1",
  "photo": {
    "id": 1,
    "photo_name": "laravel",
    "photo_size": 1024,
    "photo_url": "https://laravel.com/img/logotype.min.svg",
    "photo_status": "active",
    "photo_date": "2020-10-06 10:32:24",
    "created_at": "2020-10-06T10:32:24.000000Z",
    "updated_at": "2020-10-06T10:32:24.000000Z"
  }
}
/* fetch photo by id for edit */
$ curl -X PATCH -H "Content-Type: application/json" -d "{\"id\":1,\"photo_name\":\"laravel patch\",\"photo_size\":2048,\"photo_url\":\"https://laravel.com/img/logotype.min.svg\",\"photo_status\":\"active\",\"photo_date\":\"2020-10-06 10:24:01\"}" http://127.0.0.1:8000/api/photos/1 | json

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   393    0   224  100   169   1108    836 --:--:-- --:--:-- --:--:--  1945
{
  "name": "update",
  "status": true,
  "payload": {
    "id": 1,
    "photo_name": "laravel patch",
    "photo_size": 2048,
    "photo_url": "https://laravel.com/img/logotype.min.svg",
    "photo_status": "active",
    "photo_date": "2020-10-06 10:24:01"
  },
  "id": "1"
}
/* patch photo with payload */

$ curl localhost:8000/api/photos/1 | json
% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   292    0   292    0     0    707      0 --:--:-- --:--:-- --:--:--   705
{
  "name": "show",
  "id": "1",
  "photo": {
    "id": 1,
    "photo_name": "laravel patch",
    "photo_size": 2048,
    "photo_url": "https://laravel.com/img/logotype.min.svg",
    "photo_status": "active",
    "photo_date": "2020-10-06 10:36:22",
    "created_at": "2020-10-06T10:32:24.000000Z",
    "updated_at": "2020-10-06T10:36:22.000000Z"
  }
}

curl -X DELETE http://127.0.0.1:8000/api/photos/1 | json

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    41    0    41    0     0    221      0 --:--:-- --:--:-- --:--:--   221
{
  "name": "destroy",
  "status": true,
  "id": "1"
}
/* delete photo by id */

สรุปท้ายบทความ

บทความนี้ต่อยอดความสามารถของบทความ “Laravel สร้าง RESTful API ง่าย ๆ ด้วย Resource Routes Controller EP5” ที่ RESTful template แต่บทความนี้จะสอนเพิ่มเติมการต่อ MySQL เพื่อ connect database ใช้ข้อมูลภายใน เพิ่มเติมด้วยความสามารถของ migration ที่จะ create table schema ให้เองตาม code CreatePhotosTable ที่ได้ทำการสร้างไว้จาก code PHP

บทความหน้าจะมาพูดถึงเรื่องอะไรค่อยติดตามกันนะครับ ขอบคุณที่ติดตามครับ

Leave a Reply

อีเมลของคุณจะไม่แสดงให้คนอื่นเห็น ช่องข้อมูลจำเป็นถูกทำเครื่องหมาย *