将JSON数据存储在MySQL数据库(Laravel)中的非关系表中
#laravel #json #database #mysql

在此项目中,我们想在下面实现此示例:

jsongoals

用特定的餐点为每个用户创建特定的目标,以帮助他们实现目标(减少脂肪,肌肉或维护)

Laravel项目创建:

composer create-project laravel/laravel laravel-json

创建名为JSON的数据库并替换您的.env

cd laravel-json
php artisan make:migration create_goals_table
 public function up(): void
    {
        Schema::create('goals', function (Blueprint $table) {

            $table->id();
            $table->string('namegoals');
            $table->string('description');
            $table->json("mealsids")->nullable();
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');

            $table->timestamps();
        });
    }
php artisan make:migration create_meals_table
 public function up(): void
    {
        Schema::create('meals', function (Blueprint $table) {
            $table->id();
            $table->string('name')->nullable();
            $table->string('status')->nullable();
            $table->timestamps();
        });
    }
php artisan make:model Meal 

php artisan make:model Goal 
php artisan make:controller GoalController --model=Goal
php artisan make:controller MealController --model=Meal

*身份验证: *

composer require laravel/ui --dev

php artisan ui bootstrap --auth

npm install 


npm run dev


npm run build 
php artisan migrate

在我们的项目中添加身份验证系统和bootstrap后,现在让我们更新我们的控制器和模型:

_GOAL模型_

<?php

namespace App\Models;

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

class Goal extends Model
{
    use HasFactory;

    protected $fillable = [
        'namegoals', 'description', 'user_id',
    ];

    protected $casts = [
        'mealsids' => 'json'
    ];

    public function meals()
    {
        return $this->hasMany(Meal::class);
    }
    public function users()
    {
        return $this->hasOne(User::class);
    }
}

膳食模型:

<?php

namespace App\Models;

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

class Meal extends Model
{
    use HasFactory;

    protected $fillable = [
        'name', 'status'
    ];
}

餐控制器:

<?php

namespace App\Http\Controllers;

use App\Models\Meal;
use Illuminate\Http\Request;

class MealController extends Controller
{
    public function index()
    {
        $meals = Meal::all();

        return view('meal.index', compact('meals'));
    }

    public function create()
    {
        return view('meal.create');
    }

    public function store(Request $request)
    {
        $request->validate([
            'name' => 'nullable|string',
            'status' => 'nullable|string',
        ]);

        $meal = new Meal();
        $meal->name = $request->input('name');
        $meal->status = $request->input('status');
        $meal->save();

        return redirect()->route('meal.index')->with('success', 'Meal created successfully.');
    }

    public function show(Meal $meal)
    {
        return view('meal.show', compact('meal'));
    }

    public function edit(Meal $meal)
    {
        return view('meal.edit', compact('meal'));
    }

    public function update(Request $request, Meal $meal)
    {
        $request->validate([
            'name' => 'nullable|string',
            'status' => 'nullable|string',
        ]);

        $meal->name = $request->input('name');
        $meal->status = $request->input('status');
        $meal->save();

        return redirect()->route('meal.index')->with('success', 'Meal updated successfully.');
    }

    public function destroy(Meal $meal)
    {
        $meal->delete();

        return redirect()->route('meal.index')->with('success', 'Meal deleted successfully.');
    }
}

目标控制器:

<?php

namespace App\Http\Controllers;

use App\Models\Goal;
use App\Models\Meal;
use App\Models\User;
use Illuminate\Http\Request;

class GoalController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        $goals = Goal::all();
        $meals = Meal::all();
        $users = User::all();
        return view('goal.index', compact('goals', 'meals', 'users'));
    }

    public function create()
    {
        $goals = Goal::all();
        $meals = Meal::all();
        $users = User::all();
        return view('goal.create', compact('goals', 'meals', 'users'));
    }

    public function store(Request $request)
    {
        $request->validate([
            'namegoals' => 'required|string',
            'description' => 'required|string',
            'mealsids' => 'nullable|array',
            'mealsids.*' => 'exists:meals,id',
            'user_id' => 'required|exists:users,id',
        ]);

        $goal = new Goal();
        $goal->namegoals = $request->input('namegoals');
        $goal->description = $request->input('description');
        $goal->mealsids = $request->input('mealsids');
        $goal->user_id = $request->input('user_id');
        $goal->save();

        return redirect()->route('goal.index')->with('success', 'Goal created successfully.');
    }

    public function show(Goal $goal)
    {
        $goals = Goal::all();
        $meals = Meal::all();
        $users = User::all();
        return view('goal.create', compact('goals', 'meals', 'users'));
    }

    public function edit(Goal $goal)
    {
        // $goals = Goal::all();
        $meals = Meal::all();
        $users = User::all();
        return view('goal.edit', compact('goal', 'meals', 'users'));
    }

    public function update(Request $request, Goal $goal)
    {
        $request->validate([
            'namegoals' => 'required|string',
            'description' => 'required|string',
            'mealsids' => 'nullable|array',
            'mealsids.*' => 'exists:meals,id',
            'user_id' => 'required|exists:users,id',
        ]);

        $goal->namegoals = $request->input('namegoals');
        $goal->description = $request->input('description');
        $goal->mealsids = $request->input('mealsids');
        $goal->user_id = $request->input('user_id');
        $goal->save();

        return redirect()->route('goal.index')->with('success', 'Goal updated successfully.');
    }

    public function destroy(Goal $goal)
    {
        $goal->delete();

        return redirect()->route('goal.index')->with('success', 'Goal deleted successfully.');
    }
}

现在让我们更换我们的欢迎刀片:

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>My App</title>

    <!-- Fonts -->
    <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet">

    <!-- Styles -->
    <style>
        html,
        body {
            background-color: #fff;
            color: #636b6f;
            font-family: 'Nunito', sans-serif;
            font-weight: 200;
            height: 100vh;
            margin: 0;
        }

        .full-height {
            height: 100vh;
        }

        .flex-center {
            align-items: center;
            display: flex;
            justify-content: center;
        }

        .position-ref {
            position: relative;
        }

        .top-right {
            position: absolute;
            right: 10px;
            top: 18px;
        }

        .content {
            text-align: center;
        }

        .title {
            font-size: 84px;
        }

        .links>a {
            color: #636b6f;
            padding: 0 25px;
            font-size: 13px;
            font-weight: 600;
            letter-spacing: .1rem;
            text-decoration: none;
            text-transform: uppercase;
        }

        .m-b-md {
            margin-bottom: 30px;
        }
    </style>
</head>

<body>
    <div class="flex-center position-ref full-height">
        <div class="content">
            <div class="title m-b-md">
                My tutorial App
            </div>

            <div class="links">
                @if (Route::has('login'))
                @auth
                <a href="{{ url('/meal') }}">Meals</a>
                <a href="{{ url('/goal') }}">Goals</a>
                @else
                <a href="{{ route('login') }}">Login</a>

                @if (Route::has('register'))
                <a href="{{ route('register') }}">Register</a>
                @endif
                @endauth
                @endif
            </div>
        </div>
    </div>
</body>

</html>

*让我们添加两个文件夹,以查看一个称为目标的文件夹,另一个叫做
*

目标刀片和页面

create.blade.php

@extends('layouts.app')

@section('content')
    <div class="container">
        <h1>Create Goal</h1>

        <form action="{{ route('goal.store') }}" method="POST">
            @csrf

            <div class="mb-3">
                <label for="namegoals" class="form-label">Name:</label>
                <input type="text" name="namegoals" id="namegoals" value="{{ old('namegoals') }}" class="form-control"
                    required>
                @error('namegoals')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="description" class="form-label">Description:</label>
                <textarea name="description" id="description" class="form-control" required>{{ old('description') }}</textarea>
                @error('description')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="mealsids" class="form-label">Meals:</label>
                <select name="mealsids[]" id="mealsids" class="form-control" multiple>
                    @foreach ($meals as $meal)
                        <option value="{{ $meal->id }}">{{ $meal->name }}</option>
                    @endforeach
                </select>
                @error('mealsids')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="user_id" class="form-label">User:</label>
                <select name="user_id" id="user_id" class="form-control">
                    @foreach ($users as $user)
                        <option value="{{ $user->id }}">{{ $user->name }}</option>
                    @endforeach
                </select>
                @error('user_id')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <button type="submit" class="btn btn-primary">Create Goal</button>
        </form>
    </div>
@endsection

index.blade.php

@extends('layouts.app')

@section('content')
    <h1>
        Goals</h1>

    <a href="{{ route('goal.create') }}" class="btn btn-primary mb-3">Create New Goal</a>

    <table class="table">
        <thead>
            <tr>
                <th>Name</th>
                <th>Description</th>
                <th>User</th>
                <th>Meals</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            @foreach ($goals as $goal)
                <tr>
                    <td>{{ $goal->namegoals }}</td>
                    <td>{{ $goal->description }}</td>
                    <td>
                        @if ($goal->user_id && ($user = $users->where('id', $goal->user_id)->first()))
                            {{ $user->name }}
                        @else
                            -
                        @endif
                    </td>
                    <td>
                        @if ($goal->mealsids)
                            <ul>
                                @foreach ($goal->mealsids as $mealId)
                                    @if ($meal = $meals->where('id', $mealId)->first())
                                        <span class="badge bg-success">
                                            <li>{{ $meal->name }}</li>
                                        </span>
                                    @endif
                                @endforeach
                            </ul>
                        @else
                            -
                        @endif
                    </td>
                    <td>

                        <a href="{{ route('goal.edit', $goal->id) }}" class="btn btn-secondary">Edit</a>
                        <form action="{{ route('goal.destroy', $goal->id) }}" method="POST" class="d-inline">
                            @csrf
                            @method('DELETE')
                            <button type="submit" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
            @endforeach
        </tbody>
    </table>
@endsection

edit.blade.php

@extends('layouts.app')

@section('content')
    <div class="container">
        <h1>Edit Goal</h1>

        <form action="{{ route('goal.update', $goal->id) }}" method="POST">
            @csrf
            @method('PUT')

            <div class="mb-3">
                <label for="namegoals" class="form-label">Name:</label>
                <input type="text" name="namegoals" id="namegoals" value="{{ $goal->namegoals }}" class="form-control"
                    required>
                @error('namegoals')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="description" class="form-label">Description:</label>
                <textarea name="description" id="description" class="form-control" required>{{ $goal->description }}</textarea>
                @error('description')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="mealsids" class="form-label">Meals:</label>
                <select name="mealsids[]" id="mealsids" class="form-control" multiple>
                    @foreach ($meals as $meal)
                        <option value="{{ $meal->id }}" @if (in_array($meal->id, $goal->mealsids)) selected @endif>
                            {{ $meal->name }}</option>
                    @endforeach
                </select>
                @error('mealsids')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <div class="mb-3">
                <label for="user_id" class="form-label">User:</label>
                <select name="user_id" id="user_id" class="form-control">
                    @foreach ($users as $user)
                        <option value="{{ $user->id }}" @if ($user->id === $goal->user_id) selected @endif>
                            {{ $user->name }}</option>
                    @endforeach
                </select>
                @error('user_id')
                    <p class="text-danger">{{ $message }}</p>
                @enderror
            </div>

            <button type="submit" class="btn btn-primary">Update Goal</button>
        </form>
    </div>
@endsection

餐叶片和页面

create.blade.php

@extends('layouts.app')

@section('content')
    <div class="container">
        <div class="row">
            <div class="col-md-6 offset-md-3">
                <h1>Create Meal</h1>

                <form action="{{ route('meal.store') }}" method="POST">
                    @csrf

                    <div class="form-group">
                        <label for="name">Name:</label>
                        <input type="text" class="form-control" name="name" id="name">
                    </div>

                    <div class="form-group">
                        <label for="status">Status:</label>
                        <input type="text" class="form-control" name="status" id="status">
                    </div>

                    <!-- Add more fields as needed -->

                    <button type="submit" class="btn btn-primary">Create Meal</button>
                </form>
            </div>
        </div>
    </div>
@endsection

index.blade.php

@extends('layouts.app')
@section('content')
    <div class="container">
        <br>
        <div class="row justify-content-center">
            <div class="col-md-6">
                <h2>Meals List</h2>
            </div>
            <div class="col-md-6">
                <div class="float-right">
                    <a href="{{ route('meal.create') }}" class="btn btn-primary"><i class="fa fa-plus"></i> Add new Meal</a>
                </div>
            </div>
            <br>
            <div class="col-md-12">
                @if (session('success'))
                    <div class="alert alert-success" role="alert">
                        {{ session('success') }}
                    </div>
                @endif
                @if (session('error'))
                    <div class="alert alert-danger" role="alert">
                        {{ session('error') }}
                    </div>
                @endif
                <table class="table table-bordered">
                    <thead class="thead-light">
                        <tr>
                            <th width="5%">#</th>
                            <th>Meal Name</th>
                            <th width="10%">
                                <center>Meal Status</center>
                            </th>
                            <th width="14%">
                                <center>Action</center>
                            </th>
                        </tr>
                    </thead>
                    <tbody>
                        @forelse  ($meals as $meal)
                            <tr>
                                <th>{{ $meal->id }}</th>
                                <td>{{ $meal->name }}</td>
                                <td>
                                    <center>{{ $meal->status }}</center>
                                </td>
                                <td>
                                    <div class="action_btn">
                                        <div class="action_btn">
                                            <a href="{{ route('meal.edit', $meal->id) }}" class="btn btn-warning">Edit</a>
                                        </div>
                                        <div class="action_btn margin-left-10">
                                            <form action="{{ route('meal.destroy', $meal->id) }}" method="post">
                                                @csrf
                                                @method('DELETE')
                                                <button class="btn btn-danger" type="submit">Delete</button>
                                            </form>
                                        </div>
                                    </div>
                                </td>
                            </tr>
                        @empty
                            <tr>
                                <td colspan="4">
                                    <center>No data found</center>
                                </td>
                            </tr>
                        @endforelse
                    </tbody>
                </table>
            </div>
        </div>
    </div>
@endsection

edit.blade.php

@extends('layouts.app')

@section('content')
    <div class="container">
        <div class="row">
            <div class="col-md-6 offset-md-3">
                <h1>Edit Meal</h1>

                <form action="{{ route('meal.update', $meal->id) }}" method="POST">
                    @csrf
                    @method('PUT')

                    <div class="form-group">
                        <label for="name">Name:</label>
                        <input type="text" class="form-control" name="name" id="name" value="{{ $meal->name }}">
                    </div>

                    <div class="form-group">
                        <label for="status">Status:</label>
                        <input type="text" class="form-control" name="status" id="status"
                            value="{{ $meal->status }}">
                    </div>

                    <!-- Add more fields as needed -->

                    <button type="submit" class="btn btn-primary">Update Meal</button>
                </form>
            </div>
        </div>
    </div>
@endsection

show.blade.php

@extends('layouts.app')

@section('content')
    <h2>{{ $meal->name }}</h2>
    <p>Status: {{ $meal->status }}</p>
@endsection


和路由:

Route::get('/meal', [MealController::class, 'index'])->name('meal.index');
Route::post('/meal', [MealController::class, 'store'])->name('meal.store');
Route::get('/meal/{meal}/edit', [MealController::class, 'edit'])->name('meal.edit');
Route::delete('/meal/{meal}', [MealController::class, 'destroy'])->name('meal.destroy');
Route::put('/meal/{meal}', [MealController::class, 'update'])->name('meal.update');
Route::get('/meal/create', [MealController::class, 'create'])->name('meal.create');
Route::get('/meal/{meal}', [MealController::class, 'show'])->name('meal.show');


Route::get('/goal', [GoalController::class, 'index'])->name('goal.index');
Route::post('/goal', [GoalController::class, 'store'])->name('goal.store');
Route::get('/goal/{goal}/edit', [GoalController::class, 'edit'])->name('goal.edit');
Route::delete('/goal/{goal}', [GoalController::class, 'destroy'])->name('goal.destroy');
Route::put('/goal/{goal}', [GoalController::class, 'update'])->name('goal.update');
Route::get('/goal/create', [GoalController::class, 'create'])->name('goal.create');
Route::get('/goal/{goal}', [GoalController::class, 'show'])->name('goal.show');

github代码来源下面:

Source Code