Running Raw SQL Queries - marella/phd GitHub Wiki

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.

Running A Select Query

To run a basic query, we can use the select method on the DB facade:

$users = DB::select('select * from users where active = ?', [1]);

The first argument passed to the select method is the raw SQL query, while the second argument is any parameter bindings that need to be bound to the query. Typically, these are the values of the where clause constraints. Parameter binding provides protection against SQL injection.

The select method will always return an array of results.

foreach ($users as $user) {
    echo $user['name'];
}

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Running An Insert Statement

To execute an insert statement, you may use the insert method on the DB facade. Like select, this method takes the raw SQL query as its first argument, and bindings as the second argument:

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

Running An Update Statement

The update method should be used to update existing records in the database. The number of rows affected by the statement will be returned by the method:

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);

Running A Delete Statement

The delete method should be used to delete records from the database. Like update, the number of rows deleted will be returned:

$deleted = DB::delete('delete from users');

Running A General Statement

Some database statements should not return any value. For these types of operations, you may use the statement method on the DB facade:

DB::statement('drop table users');