Laravel 8: MySQL Database Query Tip Sheet #1

For interacting with databases, Laravel provides the query builder, a fluent interface for interacting with different types of databases with one API. It also provides Eloquent, an object-relational mapper that is built on the query builder. Assuming a MySQL database where necessary, three tip sheets, of which this article is the first, will cover how to use both the Query Builder and Eloquent for each of the following types of database queries:

1. inserts
2. updates
3. deletes
4. selects

Using the Query Builder

The query builder provides a single interface that can be used to interact with different types of database systems. It allows users to use method chaining to build complex database queries. The DB facade provides the interface for executing query builder commands as well as raw queries. We will consider both types of queries in this section. Before we proceed, always remember to add the DB facade to the file in which you define your queries. You can do this by adding the following line to your file:

use Illuminate\Support\Facades\DB;

Raw SQL

Raw SQL calls can be made to databases, using the statement() method. Generally the statement() is used in the following format:

DB::statement("sql_query");

There are, however, more specific methods for executing the four types of SQL queries; they are discussed below. In addition to being more descriptive, the update() and delete() methods return the number of rows affected by the queries they execute.

1. inserts

Generally a raw insert query would look like this:

DB::insert("sql_query");

Here are some sample uses of the the insert method

DB::insert("insert into table_name (column_name, column_name)values (?, ?)", [value, value]); // general format

DB::insert("insert into books (title, author) values (?, ?)", 
[
"The Old Man and the Sea", 
"Ernest Hemmingway"
]); //Inserting book title, author

DB::insert("insert into students (email, name, address, age) values (?, ?, ?, ?)", [
"runolfsdottir.darrick@example.org", 
"Layne Schamberger", 
"55292 Veronica Locks Napoleonland, GA 28551", 
22
]); //Inserting email, name, address, age

2. updates

Generally a raw update query would look like this:

DB::update("sql_query");

Here are some sample uses of the the update method

DB::update("update table_name set column_name = ? where column_name = ?", [column_value, column_value]); // general format
DB::update("update students set name = ? where name = ?", ["Charles Dickens", "Layne Schamberger"]);
DB::update("update students set age = ? where name = ?", [30, "Layne Schamberger"]);

3. deletes

Generally a raw delete query would look like this:

DB::delete("sql_query");

Here are some sample uses of the the delete method

DB::delete("delete from table_name where column_name = ?", [column_value]); // general format
DB::delete("delete from students where name= ?", ["Charles Dickens"]);
DB::delete("delete from students where name= ?", ["Lord Byron"]);

4. selects

Generally a raw select query would look like this:

DB::select("sql_query");

Here are some sample uses of the the select method

DB::select("select * from students");
DB::select("select * from students where name = ?", ["Charles Dickens"]);
DB::select("select * from students where name = ? and email = ?", ["Mr. Nathen Harris I", "langworth.natasha@example.org"]);

Using the Query Builder

1. inserts

The query builder also uses provides an insert method for inserting data into a database table. Instead of a raw SQL query, however, a single associative array or an array containing one or more associative arrays is passed to the insert() method. Generally a query builder insert will look like this:

DB::table(table_name)->insert([
column_name => column_value, 
column_name => column_value,
...
]); //Inserting a single row
DB::table(table_name)->insert([
[column_name => column_value, column_name => column_value, ...],
[column_name => column_value, column_name => column_value, ...],
...
]); //Inserting multiple rows

Here are some sample query builder inserts

DB::table("students")->insert([
"email" => "xander08@example.org",
"name" => "Rafael Sabatini",
"address" => "478 Casper Club Corkeryview, GA 66365-9878",
"age" => 25
]); //Inserting single row
DB::table("students")->insert([
["email" => "xander08@example.org", "name" => "Rafael Sabatini","address" => "478 Casper Club Corkeryview, GA 66365-9878","age" => 25],
["email" => "minerva69@example.org", "name" => "Charles Dickens","address" => "60860 Lemke Viaduct East Melany, AL 10746","age" => 19],
["email" => "minerva69@example.org", "name" => "Emily Dickinson","address" => "9829 Allison Curve North Riley, KY 54280-9260","age" => 22],
]); //Inserting multiple rows

2. updates

A query builder updates look something like this:

DB::table(table_name)
->where(column_name, comparison_operator, value)
->update([column_name => value]);

Here are some sample query builder updates

DB::table("students")
->where("name", "=", "Charles Dickens")
->update(["name" => "Lord Byron"]);
DB::table("students")
->where("name", "=", "Charles Dickens")
->update(["email" => "xander08@example.org"]);

3. deletes

A query builder updates look something like this:

DB::table(table_name)
->where(column_name, comparison_operator, value)
->delete();

Here are some sample query builder deletes

DB::table("students")
->where("name", "=", "Lord Byron")
->delete();
DB::table("students")
->where("name", "=", "Rafael Sabatini")
->delete();

Query builder selects will be covered on the next tipsheet. Happy Coding !!!

Related Articles

Laravel 8: MySQL Database Query Tip Sheet #2

Laravel 8: MySQL Database Query Tip Sheet #3

Laravel 8: MySQL Database Query Tip Sheet #4