- 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

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which tag is used to add definition lists into a <dl> element?
<dt> <dd> <li>
<dl>
 <dt>HTML</dt>
  <dd> - Hyper Text Markup Language</dd>
  <dd> - Language for web pages</dd>
</dl>
Which CSS property can hide an element on page, letting an empty space in its place?
display position visibility
#id {
  visibility: hidden;
}
Click on the event which is triggered when the mouse clicks on an object.
onclick onmouseover onfocus
document.getElementById("id").onclick = function(){
  alert("http://CoursesWeb.net/");
}
Indicate the PHP variable that contains the contents of both $_GET, $_POST, and $_COOKIE arrays.
$_SESSION $_GET $_REQUEST
if(isset($_REQUEST["id"])) {
  echo $_REQUEST["id"];
}
Laravel - Working with MySQL Database

Last accessed pages

  1. Penalty Fever (443)
  2. Zuma Deluxe (33019)
  3. Sport Games (4720)
  4. Number Lines (3985)
  5. Christmas Marble Popper (85)

Popular pages this month

  1. Bubbles3 (3057)
  2. Butterfly Kyodai (2670)
  3. Zuma Deluxe (1976)
  4. Qwop (1247)
  5. Backgammon (1166)