Chabba Saad

Posted on May 26, 2023 • Edited on Apr 23

store JSON data in a non-relational table in the MySQL database ( Laravel )

#laravel #mysql #database #json

in this project we want to achieve this example below :

To create specific goals for each user with specific meals to help them achieve their goals (lose fat, gain muscle, or maintenance)

Creation de projet laravel :



composer create-project laravel/laravel laravel-json

create database called json and replace your .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

*authentification : *



composer require laravel/ui --dev

php artisan ui bootstrap --auth

npm install 


npm run dev


npm run build



php artisan migrate

after adding authentification system and bootstrap to our project now let update our controllers and model :

_Goal Model _



<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Models\Meal;

class Goal extends Model
{
    use HasFactory;

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

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


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

    public function meals()
    {
        return $this->belongsToMany(Meal::class);
    }
}

Meal Model :



<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;
use App\Models\Goal;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Meal extends Model
{
    use HasFactory;

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



    protected static function boot()
    {
        parent::boot();

        static::deleting(function ($meal) {
            $goals = Goal::whereJsonContains('mealsids', (string) $meal->id)->get();

            foreach ($goals as $goal) {
                $mealIds = $goal->mealsids;
                $mealIds = array_values(array_diff($mealIds, [(string) $meal->id]));
                $goal->mealsids = $mealIds;
                $goal->save();
            }
        });
    }
    public function goals()
    {
        return $this->belongsToMany(Goal::class);
    }
}

Meal Controller :



<?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.');
    }
}

Goal Controller :



<?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.');
    }
}

now let replace our welcome blade :



<!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>

*let's add two folders in view one called goal and other called meal
*

Goals blades and pages

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

Meals blades and pages

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

and for routing :



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 Code source below :

Source Code

DEV Community

store JSON data in a non-relational table in the MySQL database ( Laravel )

Top comments (0)

Read next

How to Image Upload with Summernote in Laravel 11 Tutorial

How to Get Current Full URL in Laravel 11

How Pipe Syntax Fix SQL Design Problems

Congrats to the Winners of the Open Source AI Challenge with pgai and Ollama!