Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to insert raw data in MySQL database in Laravel?
You can make use of the Query Builder tool to insert raw data in MySQL tables. You have to include the class: Illuminate\Support\Facades\DB; or use DB;
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 | | 6 | Ashvik Khanna | ashvik@gmail.com | NULL | NULL | oooo | 16 | | 7 | Viraj Desai | viraj@gmail.com | NULL | NULL | test | 18 | | 8 | Priya Singh | priya@gmail.com | NULL | NULL | test123 | 20 | | 9 | Arbaaz | arbaaz@gmail.com | 2022-05-29 14:11:09 | 2022-05-29 14:11:09 | testing | 35 | | 10 |Niketan Vaahi |niketan@gmail.com | NULL | NULL | testing | 35 | +----+---------------+------------------+---------------------+---------------------+---------+------+
Example 1
Using insert() method
The insert() method will add a record in the table given. It takes input as an array with data in key/value pairs wherein the key is the column name and value is the value to be given for that column. The code will be as follows −
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
Example 2
Using the DB facade insert() method you can insert multiple records as shown below −
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 complete code is −
<?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 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
Example 3
We can also make use of raw insert values to the table. The code will be as follows −
DB::insert('insert into students (name, email, age,address) values (?, ?, ?, ?)',
['Niyati', 'niyati@gmail.com', 19, 'Pune']);
Following is the complete example that inserts the raw values into the tables −
<?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']); } }
Output
The above snippet adds the following rows the students table
12, 'Niyati', 'niyati@gmail.com', 'Pune', 19
Example 4
We can make use of an eloquent model student, to insert data into the table. An eloquent model is a unique class created for each table and for all queries related to the table the model class associated with that table is used.
The code for that is −
$student = new Student; $student->name = 'Amar'; $student->email = 'amar@gmail.com'; $student->age = 25; $student->address = 'Lucknow'; $student->save();
The following example inserts raw data into a table in MySQL −
<?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(); } }
Output
On executing the above code following row is added to the students table −
13, 'Amar', 'amar@gmail.com', 'Lucknow', 25
Finally, if you verify the table in MySQL you can see all the records as shown below −
mysql> select * from students; +----+---------------+-------------------+---------------------+---------------------+---------+------+ | 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 | NULL | abcd | 15 | | 5 | Nidhi Agarwal | nidhi@gmail.com | NULL | NULL | abcd | 20 | | 6 | Ashvik Khanna | ashvik@gmail.com | NULL | NULL | oooo | 16 | | 7 | Viraj Desai | viraj@gmail.com | NULL | NULL | test | 18 | | 8 | Priya Singh | priya@gmail.com | NULL | NULL | test123 | 20 | | 9 | Arbaaz | arbaaz@gmail.com | 2022-05-29 14:11:09 | 2022-05-29 14:11:09 | testing | 35 | | 10 | Niketan Vaahi | niketan@gmail.com | NULL | NULL | testing | 35 | | 11 | Niya Sethi | niya@gmail.com | NULL | NULL | Mumbai | 20 | | 12 | Niyati | niyati@gmail.com | NULL | NULL | Pune | 19 | | 13 | Amar | amar@gmail.com | NULL | NULL | Lucknow | 25 | | 14 | Peter | peter@gmail.com | NULL | NULL | Chicago | 20 | | 15 | David | david@gmail.com | NULL | NULL | London | 20 | | 16 | Niraj | niraj@gmail.com | NULL | NULL | Mumbai | 20 | | 17 | Sumit | sumit@gmail.com | NULL | NULL | Kerala | 20 | +----+---------------+-------------------+---------------------+---------------------+---------+------+ 17 rows in set (0.00 sec)
5K+ Views
