Sling Academy
Home/PHP/How to Execute SQL Queries in Laravel

How to Execute SQL Queries in Laravel

Last updated: January 18, 2024

Introduction

Laravel, a popular PHP framework for web application development, simplifies the process of interacting with databases through its elegant and expressive syntax. Whether you are a seasoned developer or a beginner, Laravel’s ORM Eloquent and the database query builder offer a powerful and flexible way to handle SQL queries. In this tutorial, we will explore different methods to execute SQL queries within Laravel.

Prerequisites

  • A basic understanding of PHP and Laravel.
  • Laravel application setup on your system. If you don’t have it yet, check this guide: How to install Laravel on Windows and Mac.
  • Database connection configured in your .env file.

Setting Up the Environment

Before executing SQL queries, ensure your Laravel application is connected to a database. This involves setting the appropriate database connection parameters within the .env file of your Laravel project.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Using Eloquent Models

Eloquent is Laravel’s default ORM. It favors Active Record implementation, making it possible to interact with the database objects and relationships using expressive PHP syntax.

Retrieving All Records

$users = App\Models\User::all();

foreach ($users as $user) {
    echo $user->name;
}

Inserting a New Record

$user = new App\Models\User;
$user->name = 'John Doe';
$user->email = '[email protected]';
$user->save();

Using the Query Builder

Laravel’s query builder provides a convenient, fluent interface to create and run database queries. It is database agnostic, meaning it can build queries for MySQL, Postgres, SQL Server, and SQLite.

Selecting Data

$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->name;
}

Inserting Data

DB::table('users')->insert([
    'name' => 'Jane Doe',
    'email' => '[email protected]',
    'password' => bcrypt('123456')
]);

Raw SQL Queries

While Eloquent and the query builder abstract away most of the direct database access, sometimes you might need to write a raw SQL query. Laravel makes this possible through a handful of methods.

Running a Select Query

$users = DB::select('SELECT * FROM users WHERE active = ?', [1]);

foreach ($users as $user) {
    echo $user->name;
}

Running an Insert Query

$success = DB::insert('INSERT INTO users (name, email, password) VALUES (?, ?, ?)', ['Jane Doe', '[email protected]', bcrypt('123456')]);

Handling Transactions

Transactions are crucial when you need to perform multiple database operations as a single unit of work. Laravel provides several methods for transaction management.

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
});

Listening To Query Events

Debugging your SQL queries can be made easier by listening to query events that Laravel fires for every query executed.

DB::listen(function ($query) {
    Log::info($query->sql, $query->bindings, $query->time);
});

Conclusion

In this tutorial, you’ve learned various ways to execute SQL queries using Laravel’s Eloquent ORM, query builder, and raw SQL statements. Additionally, we’ve touched on handling database transactions and listening for query events for debugging purposes. With these tools, you’re well-equipped to tackle any database challenges in your Laravel applications.

Next Article: How to connect to PostgreSQL database in Laravel

Previous Article: How to connect to MySQL database in Laravel

Series: Laravel & Eloquent Tutorials

PHP

You May Also Like

  • Pandas DataFrame.value_counts() method: Explained with examples
  • Constructor Property Promotion in PHP: Tutorial & Examples
  • Understanding mixed types in PHP (5 examples)
  • Union Types in PHP: A practical guide (5 examples)
  • PHP: How to implement type checking in a function (PHP 8+)
  • Symfony + Doctrine: Implementing cursor-based pagination
  • Laravel + Eloquent: How to Group Data by Multiple Columns
  • PHP: How to convert CSV data to HTML tables
  • Using ‘never’ return type in PHP (PHP 8.1+)
  • Nullable (Optional) Types in PHP: A practical guide (5 examples)
  • Explore Attributes (Annotations) in Modern PHP (5 examples)
  • An introduction to WeakMap in PHP (6 examples)
  • Type Declarations for Class Properties in PHP (5 examples)
  • Static Return Type in PHP: Explained with examples
  • PHP: Using DocBlock comments to annotate variables
  • PHP: How to ping a server/website and get the response time
  • PHP: 3 Ways to Get City/Country from IP Address
  • PHP: How to find the mode(s) of an array (4 examples)
  • PHP: Calculate standard deviation & variance of an array