- 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 HTML5 tag can be used to embed an external application (SWF, PDF) in web page?
<mark> <embed> <canvas>
<embed src="flash_game.swf" width="450" height="350" />
Which CSS pseudo-element adds a special style to the first line of a text?
:first-letter :before :first-line
#id:first-line {
  font-weight: bold;
  color: blue;
}
Click on the window object property which gets or sets the URL of current page.
window.location window.self window.status
var url = window.location;
alert(url);
Indicate the PHP function used to get the contents of a file or page and store it into a string.
fopen() file_put_contents() file_get_contents()
$homepage = file_get_contents("http://coursesweb.net/");
echo $homepage;
Working with MySQL Database

Last accessed pages

  1. Flash Games - Free online Games (21263)
  2. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (60878)
  3. Convert JSON in JavaScript (4926)
  4. Leader Bird (205)
  5. Flying Tiger (157)

Popular pages this month

  1. Qwop (4608)
  2. Drag Racer V3 (2234)
  3. Flash Games - Free online Games (1107)
  4. Bubbles3 (992)
  5. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (982)