How to insert raw data in MySQL database in Laravel?

In Laravel, you can insert raw data into MySQL database tables using the Query Builder tool. This requires including the Illuminate\Support\Facades\DB facade or adding use DB; at the top of your controller.

Prerequisites: Ensure Laravel is installed and a MySQL database connection is configured in your .env file.

Assume we have created a table named students using the CREATE statement as shown below

CREATE TABLE students(
   id          INTEGER      NOT NULL   PRIMARY KEY,
   name        VARCHAR(15)  NOT NULL,
   email       VARCHAR(20)  NOT NULL,
   created_at  VARCHAR(27),
   updated_at  VARCHAR(27),
   address     VARCHAR(30)  NOT NULL,
   age         INTEGER
);

Assume we have populated the above table with the following data

+----+---------------+------------------+---------------------+---------------------+---------+------+
| id |    name       |      email       |      created_at     |     updated_at      | address | age  |
+----+---------------+------------------+---------------------+---------------------+---------+------+
| 1  | Siya Khan     | siya@gmail.com   | 2022-05-01 13:45:55 | 2022-05-01 13:45:55 | xyz     | 20   |
| 2  | Rehan Khan    | rehan@gmail.com  | 2022-05-01 13:49:50 | 2022-05-01 13:49:50 | xyz     | 18   |
| 3  | Rehan Khan    | rehan@gmail.com  | NULL                | NULL                | testing | 20   |
| 4  | Rehan         | rehan@gmail.com  | NULL                | 2022-05-29 14:17:02 | abcd    | 50   |
| 5  | Nidhi Agarwal | nidhi@gmail.com  | NULL                | NULL                | abcd    | 20   |
+----+---------------+------------------+---------------------+---------------------+---------+------+

Using insert() Method for Single Record

The insert() method adds a record to the specified table. It takes an array with key/value pairs where the key is the column name and value is the data to insert

<?php
use Illuminate\Support\Facades\DB;

DB::table('students')->insert([
   'name' => 'Niya Sethi',
   'email' => 'niya@gmail.com',
   'age' => 20,
   'address' => 'Mumbai'
]);
?>

The above snippet adds the following row to the students table

11, 'Niya Sethi', 'niya@gmail.com', 'Mumbai', 20

Using insert() Method for Multiple Records

You can insert multiple records by passing an array of arrays to the insert() method

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StudentController extends Controller {
   public function index() {
      DB::table('students')->insert([
         ['name' => 'Peter', 'email' => 'peter@gmail.com', 'age' => 20, 'address' => 'Chicago'],
         ['name' => 'David', 'email' => 'david@gmail.com', 'age' => 20, 'address' => 'London'],
         ['name' => 'Niraj', 'email' => 'niraj@gmail.com', 'age' => 20, 'address' => 'Mumbai'],
         ['name' => 'Sumit', 'email' => 'sumit@gmail.com', 'age' => 20, 'address' => 'Kerala']
      ]);
   }
}
?>

The above snippet adds the following rows to the students table

14, 'Peter', 'peter@gmail.com', 'Chicago', 20 
15, 'David', 'david@gmail.com', 'London', 20 
16, 'Niraj', 'niraj@gmail.com', 'Mumbai', 20 
17, 'Sumit', 'sumit@gmail.com', 'Kerala', 20

Using Raw SQL Insert Queries

You can also use raw SQL insert queries with placeholders for security

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StudentController extends Controller {
   public function index() {
      DB::insert('insert into students (name, email, age, address) values (?, ?, ?, ?)',
         ['Niyati', 'niyati@gmail.com', 19, 'Pune']);
   }
}
?>

The above snippet adds the following row to the students table

12, 'Niyati', 'niyati@gmail.com', 'Pune', 19

Using Eloquent Models

You can use Eloquent models to insert data, which provides an object-oriented approach

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Student;

class StudentController extends Controller {
   public function index() {
      $student = new Student;
      $student->name = 'Amar';
      $student->email = 'amar@gmail.com';
      $student->age = 25;
      $student->address = 'Lucknow';
      $student->save();
   }
}
?>

The above code adds the following row to the students table

13, 'Amar', 'amar@gmail.com', 'Lucknow', 25

Conclusion

Laravel provides multiple methods to insert raw data: Query Builder's insert() method for arrays, raw SQL with DB::insert() for custom queries, and Eloquent models for object-oriented operations. Use Query Builder for simple insertions and Eloquent for complex model relationships.