Lesson 28 of 50 - Learn how to retrieve, insert, update, delete, and filter database records using Laravel Query Builder.
Laravel Query Builder provides a simple way to interact with database tables without writing complete SQL queries.
It supports operations such as:
Example:
use Illuminate\Support\Facades\DB;
$students = DB::table('students')->get();
The DB facade lets you access Query Builder.
This example retrieves all rows from the students table.
Query Builder uses parameter binding for values in its normal query methods, helping protect against SQL injection.
Use get() to retrieve multiple records
and first() to retrieve the first matching
record.
Retrieve all students:
use Illuminate\Support\Facades\DB;
$students = DB::table('students')->get();
foreach ($students as $student) {
echo $student->name . '<br>';
}Retrieve one student:
$student = DB::table('students')->first();
if ($student) {
echo $student->name;
}Retrieve a student by ID:
$student = DB::table('students')
->where('id', 1)
->first();
if ($student) {
echo $student->name;
}Query Builder returns result objects rather than Eloquent model instances.
Use select() when you need only particular
columns from a table.
Select name and email:
$students = DB::table('students')
->select('name', 'email')
->get();Display the selected columns:
foreach ($students as $student) {
echo $student->name;
echo ' - ';
echo $student->email;
echo '<br>';
}Retrieve a single column's values:
$names = DB::table('students')
->pluck('name');
foreach ($names as $name) {
echo $name . '<br>';
}Selecting only the required columns can reduce the amount of data retrieved from the database.
The where() method filters records
according to a condition.
Find students with ID 1:
$student = DB::table('students')
->where('id', 1)
->first();Find students enrolled in ADCA:
$students = DB::table('students')
->where('course', 'ADCA')
->get();Find students whose fee is greater than 5000:
$students = DB::table('students')
->where('fee', '>', 5000)
->get();Use multiple conditions:
$students = DB::table('students')
->where('course', 'ADCA')
->where('status', 'active')
->get();
Multiple where() conditions are combined
with AND logic by default. Your table must contain
the referenced columns.
Laravel provides additional methods for more flexible filtering.
Use OR conditions:
$students = DB::table('students')
->where('course', 'ADCA')
->orWhere('course', 'Tally Prime')
->get();Match multiple possible values:
$students = DB::table('students')
->whereIn('course', ['ADCA', 'Tally Prime'])
->get();Filter values within a range:
$students = DB::table('students')
->whereBetween('fee', [1000, 10000])
->get();Search using LIKE:
$students = DB::table('students')
->where('name', 'like', '%Rahul%')
->get();The LIKE pattern above matches names containing "Rahul". Whether matching is case-sensitive depends on your database and its collation.
Use orderBy() to sort records,
limit() to restrict the number of records,
and offset() to skip records.
Sort by name in ascending order:
$students = DB::table('students')
->orderBy('name', 'asc')
->get();Sort by ID in descending order:
$students = DB::table('students')
->orderBy('id', 'desc')
->get();Retrieve only five students:
$students = DB::table('students')
->orderBy('id', 'asc')
->limit(5)
->get();Skip five records and retrieve the next five:
$students = DB::table('students')
->orderBy('id', 'asc')
->offset(5)
->limit(5)
->get();Use a consistent ordering when limiting or skipping records so results are predictable.
Insert a new student:
DB::table('students')->insert([
'name' => 'Rahul Kumar',
'email' => 'rahul@example.com',
'course' => 'ADCA',
]);This assumes the table has these columns and any required columns have defaults or supplied values.
Update a student:
DB::table('students')
->where('id', 1)
->update([
'course' => 'Tally Prime',
]);Delete a student:
DB::table('students')
->where('id', 1)
->delete();Insert multiple records:
DB::table('students')->insert([
[
'name' => 'Amit',
'email' => 'amit@example.com',
'course' => 'ADCA',
],
[
'name' => 'Priya',
'email' => 'priya@example.com',
'course' => 'Tally Prime',
],
]);Important: Always verify your WHERE conditions before updating or deleting records. An update or delete without a WHERE condition can affect every row.
Query Builder supports aggregate methods for calculating values directly in the database.
Count students:
$totalStudents = DB::table('students')->count();
echo $totalStudents;Calculate total fees:
$totalFees = DB::table('students')->sum('fee');
echo $totalFees;Calculate average fee:
$averageFee = DB::table('students')->avg('fee');
echo $averageFee;Find the highest fee:
$highestFee = DB::table('students')->max('fee');
echo $highestFee;Check whether a matching record exists:
$exists = DB::table('students')
->where('email', 'rahul@example.com')
->exists();
if ($exists) {
echo 'Student found';
} else {
echo 'Student not found';
}
These examples assume the students table contains a
numeric fee column where required.
Create a student listing page using Laravel Query Builder.
Challenge:
Build a page that searches students by name and course, sorts the results by name, and displays the total number of matching records.
Use parameterized Query Builder methods for user-supplied values. Never concatenate untrusted input into raw SQL.
1. Which method retrieves multiple records?
2. Which method counts matching records?
3. Which method filters records using a condition?