-
Running Raw SQL Queries
Laravel currently supports following 4 databases: MySQL, Postgres, SQLite, and SQL Server.
- In this tutorial I'll use
Laravel with MySQL database, which is also the Default Database to work with.
Connecting to MySQL Database
First, create a Database in MySQL. For this tutorial I created with PhpMyAdmin a database called "
lrvt".
Then, open the
.env file (in the directory where you have installed Laravel) and add your data for connecting to MySQL.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=lrvt
DB_USERNAME=root
DB_PASSWORD=null
- It is better to have the same data added in the
config/database.php file, to the "
mysql" array in "
connections".
'mysql'=>[
'driver'=> 'mysql',
'host'=> env('DB_HOST', '127.0.0.1'),
'port'=> env('DB_PORT', '3306'),
'database'=> env('DB_DATABASE', 'lrvt'),
'username'=> env('DB_USERNAME', 'root'),
'password'=> env('DB_PASSWORD', ''),
//...
],
The
env() method gets the value of an environment variable or return a default value (passed in the second argument).
The file config/database.php is the actual configuration file that will be used. It just happens to pull some values from the env. This allows you to not have to change the config files when you have a project on different servers. You can just have a different .env file.
If you want just to test the connection to the MySQL database, add this code in
routes/web.php :
Route::get('test-conn',function(){
// Test database connection
try {
DB::connection()->getPdo();
if(DB::connection()->getDatabaseName()){
return 'Successfully connected to the DB: '. DB::connection()->getDatabaseName();
}
} catch (\Exception $e){
return 'Could not connect to the database. Please check your configuration.';
}
});
- Then, access this URL:
//localhost:8000/test-conn
Running Raw SQL Queries
Once you have configured your database connection, you may run queries using the
DB facade.
The DB facade provides methods for each type of query:
select, update, insert, delete, and
statement.
statement() method
The
DB::statement() method is used for queries that do not return any value, like "
CREATE TABLE" and "
DROP TABLE".
DB::statement('drop table users');
Insert Statement
The
DB::insert() method is used to execute INSERT query. It takes the raw SQL query as its first argument, and an array with values for "?" placeholders as its second argument. Returns True or False.
DB::insert('insert into users (id, name) values (?, ?)', [1, 'MarPlo']);
- Or, with named bindings:
DB::insert('insert into users (id, name) values (:id, :name)', ['id'=>1, 'name'=>'MarPlo']);
To get the
last inserted id in an AUTO_INCREMENT 'id' column when an Insert query is performed with the
DB::insert() method, use the
DB::getPdo()->lastInsertId() method after the insert statement is executed.
$sql ='insert into users (id, name) values (:id, :name)';
DB::insert($sql, ['id'=>1, 'name'=>'MarPlo']);
$last_id = DB::getPdo()->lastInsertId();
Select Query
The
DB::select() method returns an array of results.
$results = DB::select('select * from users where id = ?', [1]);
- Or, with named bindings:
$results = DB::select('select * from users where id = :id', ['id'=>1]);
The returned rows can be parsed with a
foreach() instruction:
$sql ='select * from users where id > :id';
$res = DB::select($sql, ['id'=>2]);
foreach($res as $row){
//$row is an object with the columns name as properties
echo $row->column_name;
}
Update Statement
The
DB::update() method is used to perform UPDATE query. It returns the number of affected rows.
$nr_afr = DB::update('update users set votes = 100 where name = ?', ['John']);
- Or, with named bindings:
$nr_afr = DB::update('update users set votes = 100 where name = :name', ['name'=>'John']);
Delete Statement
The
DB::delete() method is used to perform DELETE query. It returns the number of affected rows.
$nr_afr = DB::delete('delete from users where id = ?', [2]);
- Or, with named bindings:
$nr_afr = DB::delete('delete from users where id = :id', ['id'=>2]);
- Documentation:
Laravel - Database: Getting Started