Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 | [email protected] | 2022-05-01 13:45:55 | 2022-05-01 13:45:55 | xyz | 20 | | 2 | Rehan Khan | [email protected] | 2022-05-01 13:49:50 | 2022-05-01 13:49:50 | xyz | 18 | | 3 | Rehan Khan | [email protected] | NULL | NULL | testing | 20 | | 4 | Rehan | [email protected] | NULL | 2022-05-29 14:17:02 | abcd | 50 | | 5 | Nidhi Agarwal | [email protected] | NULL | NULL | abcd | 20 | | 6 | Ashvik Khanna | [email protected] | NULL | NULL | oooo | 16 | | 7 | Viraj Desai | [email protected] | NULL | NULL | test | 18 | | 8 | Priya Singh | [email protected] | NULL | NULL | test123 | 20 | | 9 | Arbaaz | [email protected] | 2022-05-29 14:11:09 | 2022-05-29 14:11:09 | testing | 35 | | 10 |Niketan Vaahi |[email protected] | 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' => '[email protected]', 'age'=>'20', 'address'=>'Mumbai' ]);
The above snippet adds the following row to the students table.
11, 'Niya Sethi', '[email protected]', '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' => '[email protected]', 'age'=>'20', 'address'=>'Chicago'], ['name' => 'David', 'email' => '[email protected]', 'age'=>'20', 'address'=>'London'], ['name' => 'Niraj', 'email' => '[email protected]', 'age'=>'20', 'address'=>'Mumbai'], ['name' => 'Sumit', 'email' => '[email protected]', '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' => '[email protected]', 'age'=>'20', 'address'=>'Chicago'], ['name' => 'David', 'email' => '[email protected]', 'age'=>'20', 'address'=>'London'], ['name' => 'Niraj', 'email' => '[email protected]', 'age'=>'20', 'address'=>'Mumbai'], ['name' => 'Sumit', 'email' => '[email protected]', 'age'=>'20', 'address'=>'Kerala'] ]); } }
The above snippet adds the following rows the students table ?
14, 'Peter', '[email protected]', 'Chicago', 20 15, 'David', '[email protected]', 'London', 20 16, 'Niraj', '[email protected]', 'Mumbai', 20 17, 'Sumit', '[email protected]', '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', '[email protected]', 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', '[email protected]', 19, 'Pune']); } }
Output
The above snippet adds the following rows the students table
12, 'Niyati', '[email protected]', '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 = '[email protected]'; $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 = '[email protected]'; $student->age = 25; $student->address = 'Lucknow'; $student->save(); } }
Output
On executing the above code following row is added to the students table ?
13, 'Amar', '[email protected]', '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 | [email protected] | 2022-05-01 13:45:55 | 2022-05-01 13:45:55 | Xyz | 20 | | 2 | Rehan Khan | [email protected] | 2022-05-01 13:49:50 | 2022-05-01 13:49:50 | Xyz | 18 | | 3 | Rehan Khan | [email protected] | NULL | NULL | testing | 20 | | 4 | Rehan | [email protected] | NULL | NULL | abcd | 15 | | 5 | Nidhi Agarwal | [email protected] | NULL | NULL | abcd | 20 | | 6 | Ashvik Khanna | [email protected] | NULL | NULL | oooo | 16 | | 7 | Viraj Desai | [email protected] | NULL | NULL | test | 18 | | 8 | Priya Singh | [email protected] | NULL | NULL | test123 | 20 | | 9 | Arbaaz | [email protected] | 2022-05-29 14:11:09 | 2022-05-29 14:11:09 | testing | 35 | | 10 | Niketan Vaahi | [email protected] | NULL | NULL | testing | 35 | | 11 | Niya Sethi | [email protected] | NULL | NULL | Mumbai | 20 | | 12 | Niyati | [email protected] | NULL | NULL | Pune | 19 | | 13 | Amar | [email protected] | NULL | NULL | Lucknow | 25 | | 14 | Peter | [email protected] | NULL | NULL | Chicago | 20 | | 15 | David | [email protected] | NULL | NULL | London | 20 | | 16 | Niraj | [email protected] | NULL | NULL | Mumbai | 20 | | 17 | Sumit | [email protected] | NULL | NULL | Kerala | 20 | +----+---------------+-------------------+---------------------+---------------------+---------+------+ 17 rows in set (0.00 sec)
6K+ Views
