How to Upload and Store file to MySQL database – Laravel 9

Storing files in a  database makes managing large amounts of data easier, particularly if they are interrelated with a record. For example – A profile picture or documents of a user, product images, etc.

In this tutorial, I show how you can upload and store file to the MySQL database in the Laravel 9 project and also show how you can retrieve them.

How to upload and store file to MySQL database - Laravel


Contents

  1. Database Configuration
  2. Table structure
  3. Model
  4. Controller – Store file to Table
  5. Route
  6. View – Upload Form and Display files
  7. Output
  8. Conclusion

1. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

2. Table structure

  • Create a new table files using migration.
php artisan make:migration create_files_table
  • Now, navigate to database/migrations/ folder from the project root.
  • Find a PHP file that ends with create_files_table and open it.
  • Define the table structure in the up() method.
public function up()
{
    Schema::create('files', function (Blueprint $table) {
        $table->id();
        $table->string('name',60); 
        $table->string('filepath');
        $table->timestamps();
    });
}
  • Run the migration –
php artisan migrate
  • The table is been created.

3. Model

  • Create Files Model.
php artisan make:model Files
  • Open app/Models/Files.php file.
  • Specify mass assignable Model attributes – name, and filepath using the $fillable property.

Completed Code

<?php

namespace App\Models;

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

class Files extends Model
{
   use HasFactory;

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

4. Controller – Store file to Table

  • Create PagesController controller.
php artisan make:controller PagesController
  • Include Validator, Session and Files model.

Create 2 methods –

  • index() – Fetch all records from the files table and assign to $data['files']. Using this display the files list in the view.

Load index view and pass $data.

  • submitform() – This method calls on <form > submit.

Define validation for the submitted values. If not validated then return to the page with errors.

If validate then upload the file to uploads folder. Assign the uploaded file path to $filepath variable.

Insert a record into the files table. Here, store $request->name to name field and $filepath to filepath field.

Set SESSION flash and redirect to /.

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Session;
use App\Models\Files;

class PagesController extends Controller
{
     public function index(){

         // Fetch all records
         $files = Files::select('*')->get();

         $data['files'] = $files;

         return view('index',$data);
     }

     // Submit form
     public function submitform(Request $request){

         $validator = Validator::make($request->all(), [
              'name' => 'required',
              'file' => 'required|mimes:png,jpg,jpeg,pdf,docx|max:2048'
         ]);

         if ($validator->fails()) {
              return redirect()->Back()->withInput()->withErrors($validator);
         }else{

              if($request->file('file')) {

                    $file = $request->file('file');
                    $filename = $file->hashName();

                    // File upload location
                    $location = 'uploads';

                    // Upload file
                    $file->move($location,$filename);

                    // File path
                    $filepath = url('uploads/'.$filename);

                    // Insert record
                    $insertData_arr = array(
                         'name' => $request->name,
                         'filepath' => $filepath
                    );

                    Files::create($insertData_arr);

                    // Session
                    Session::flash('alert-class', 'alert-success');
                    Session::flash('message','Record inserted successfully.');

              }else{

                    // Session
                    Session::flash('alert-class', 'alert-danger');
                    Session::flash('message','Record not inserted');
              }

         }

         return redirect('/');

     }
}

5. Route

  • Open routes/web.php file.
  • Define 2 routes –
    • / – Load index view.
    • /submitform – This is a POST type route to handle form submit.
<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PagesController;

Route::get('/', [PagesController::class, 'index']);
Route::post('/submitform',[PagesController::class,'submitform'])->name('submitform');

6. View – Upload Form and Display files

Create index.blade.php file in resources/views/ folder.

Create a <form method="post" action="{{ route('submitform') }}" enctype="multipart/form-data">. In the <form > create a textbox, file element, and submit button.

Retrieve files –

Using <table > to list all files. Read records by loop on $files.

Check file extension of file path if it is image type then assign true to $isImage.

If $isImage is true then display the file using <img > tag otherwise use <a> tag.

Completed Code

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="csrf-token" content="{{ csrf_token() }}">

    <title>How to upload and store file to MySQL database – Laravel 9</title>

    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" >

</head>
<body>

    <div class="container">
         <div class="row">
              <div class="col-md-8 mt-5" style="margin: 0 auto;">

                    <!-- Alert message (start) -->
                    @if(Session::has('message'))
                        <div class="alert {{ Session::get('alert-class') }} ">
                             {{ Session::get('message') }}
                        </div>
                    @endif
                    <!-- Alert message (end) -->

                    <form method="post" action="{{ route('submitform') }}" enctype="multipart/form-data">

                          @csrf

                          <div class="form-group mb-4">

                                <label class="control-label col-sm-2" for="name">Name:</label>

                                <div class="col-sm-10">
                                      <input type="text" class="form-control" placeholder="Enter Name" name="name" value="{{ old('name') }}">
                                </div>

                                <!-- Error -->
                                @if($errors->has('name'))

                                    <div class='text-danger mt-2'>
                                         * {{ $errors->first('name') }}
                                    </div>

                                @endif
                          </div>

                          <div class="form-group mb-4">

                                <label class="control-label col-sm-2" for="file">File:</label>
                                <div class="col-sm-10">
                                    <input type="file" name="file" class="form-control">
                                </div>

                                <!-- Error -->
                                @if($errors->has('file'))

                                    <div class='text-danger mt-2'>
                                         * {{ $errors->first('file') }}
                                    </div>
  
                                @endif

                          </div>

                          <div class="form-group "> 
                                <div class="col-sm-offset-2 col-sm-10">
                                      <button type="submit" class="btn btn-info">Submit</button>
                                </div>
                          </div>

                    </form>

              </div>

         </div>

         <!-- File list -->
         <div class="row">
              <div class="col-md-12">
                    <table class="table">
                         <thead>
                               <tr>
                                    <th>Name</th>
                                    <th>File</th>
                               </tr>
                         </thead>
                         <tbody>
                         @foreach($files as $file)
                              @php

                                  $ext = strtolower(pathinfo($file->filepath, PATHINFO_EXTENSION));

                                  $imgext_arr = array("jpg","jpeg","png");

                                  $isImage = false;
                                  if(in_array($ext,$imgext_arr)){
                                        $isImage = true;
                                  }
                              @endphp
                              <tr>
                                  <td>{{ $file->name }}</td>
                                  <td>
                                      @if($isImage)
                                           <img src="{{ $file->filepath }}" width="100px" height="100px;">
                                      @else
                                           <a href="{{ $file->filepath }}" target="_blank">View file</a>
                                      @endif
                                  </td>
                              </tr>
                         @endforeach
                         </tbody>
                    </table>
              </div>
         </div>
    </div>

</body>
</html>

7. Output

View Output


8. Conclusion

In the example, I saved a single file to the database at a time but you can modify the code to store multiple files in the database.

For this, you can either separate them by a comma before inserting, store the file path in an Array then serialize it for insertion or you can insert a new record for each file.

There are other ways of directly storing files in the database are – binary form or base64 but they can be inefficient. Instead, store the file on the server and save its path to the database which I did in the tutorial.

If you found this tutorial helpful then don't forget to share.

Leave a Comment