After this lesson you can filter a catalogue with the query builder while keeping request values separate from SQL structure. The builder helps assemble queries through methods. It reduces repetitive SQL construction, but you still need to understand which parts are values and which parts control the query.
Ask a precise question
Suppose the fictional bookshop wants titles containing Orchard. We need a filter on title, an explicit order and a bounded result count. The title fragment is data. The column name and ordering are application decisions. That distinction prevents a visitor from choosing arbitrary SQL structure through a search box.
This PHP fragment can run inside a controller method after the database and seeder are ready. It was also exercised in the isolated verification application.
$rows = db_connect()->table('books')
->select('id, title')
->like('title', 'Orchard')
->orderBy('id', 'ASC')
->limit(10)
->get()
->getResultArray();The chain was also executed through an isolated diagnostic command and serialized for inspection. Against the seeded records, its captured result was:
[{"id":1,"title":"The Paper Orchard"}]The identifier reflects the fresh course database; yours can differ. The diagnostic serialized the array only to make the result visible. The query itself returns an array, not an HTTP response.
The number ten is a demonstration result limit, not a measured performance target. The fixed seeded data contains one matching title. If you have added more matching records, your result differs. A limit without a deliberate ordering can make pages unstable because it does not define which matching rows come first.
The official Query Builder Class guide documents like, orderBy, limit and result handling. Its escaping facilities are helpful, but the application must not disable protection or insert untrusted raw expressions and then claim the builder makes everything safe.
Separate values from identifiers
A search term belongs in the value argument to like or where. Do not concatenate it into a SQL string. A sort column is different: accepting any submitted column name can expose unintended fields or create invalid queries. Use an application allowlist when offering user-selectable sorting.
The builder call above fixes both the selected columns and order. That is sufficient for this course stage. A larger search interface could map a small public sort choice such as title to a known column and direction. The mapping would be application code, not a string copied directly into orderBy from the request.
Likewise, do not treat a raw SQL expression as an ordinary bound value. Raw expressions deliberately cross the structural boundary and require extra care. This beginner course does not need them to search two fictional titles.
Query builder and model are related but distinct
The model uses database facilities and can expose builder-style filtering. A direct builder query, however, is not the same operation as a model insert or update. Model-specific allowed fields, callbacks and validation do not automatically protect every independent builder operation.
Use BookModel for the bookshop's ordinary record operations so that its table mapping stays visible. Use a builder when you need to understand or compose a particular query. If an application grows complex, you can move recurring query logic behind a named method rather than copy the chain into several controllers.
Handle empty results deliberately
An empty result array means no matching records were returned. It does not by itself mean the database connection failed. The view should present a helpful empty state, such as no matching books, without exposing internal query details. A failed query is a different condition and belongs in diagnostics and error handling.
Search also has input rules. An absent search term might mean show all books; an excessively long term might be rejected. Those are product choices that should be documented and tested. Do not invent an arbitrary behavior through whatever PHP happens to do with an unexpected array input.
Verify both normal and hostile-looking data
A useful check searches for Orchard and confirms the intended row. Another tries punctuation that would be dangerous if concatenated into SQL. The goal is not to prove security with one magic string; it is to confirm that input stays in the data position and does not redefine the query.
The fetched official Query Builder guide explains the methods used here. Our search requirement and small limit are fictional application decisions. They do not imply performance characteristics for a large catalogue or a different database driver.
Keep SQL troubleshooting output private. A development error can reveal table names and paths that do not belong in a public page. The logging lesson explains how to retain useful diagnostic information without returning internal detail to visitors.
Common mistakes
Concatenating a search term into SQL defeats the value boundary. Accepting any sort column from a request confuses identifiers with values. Assuming direct builder writes inherit model allowedFields creates a protection gap. Finally, treating an empty result as an exception makes ordinary no-match searches look like application failures.
Exercise
Explain which parts of the example query are fixed application structure and which part is a search value. Then predict the result for Orchard against a database seeded exactly once, and describe what a no-match result should look like to a reader.
Show an answer
The table, selected columns, sort direction and limit are application structure. Orchard is the search value. The seeded dataset returns The Paper Orchard. A no-match search returns an empty array and should produce a normal empty-state message rather than a stack trace or an invented book.
Previous lesson · Next lesson · Related: explicit routes · Related: security boundaries
Key points
- Query values and SQL identifiers require different handling.
- Direct builder operations do not inherit every model safeguard.
- Empty search results are a normal application state.