WDX-180
Web Development X
Search & Filtering
Helping Users Find Things
Storing data is easy.
Finding the right data is where the real challenge begins.
Imagine our CMS contains:
10 products
No problem.
Now imagine:
10,000 products
Finding:
Mechanical Keyboard
by scrolling through 1,000 pages is not a feature.
It’s a punishment.
Today we’ll build search and filtering capabilities that allow users to quickly find exactly what they’re looking for.
Learning Objectives
By the end of this lesson, students will be able to:
- Understand search and filtering concepts
- Read query parameters
- Build dynamic SQL queries safely
- Use SQL WHERE clauses
- Implement keyword search
- Filter by price ranges
- Combine search with pagination
- Preserve filters between pages
- Understand SQL indexing fundamentals
- Avoid SQL injection vulnerabilities
Part 1 — Search vs Filtering
Many beginners think these are the same thing.
They’re not.
Search
User enters:
keyboard
System searches text fields.
Example (SQL):
WHERE name LIKE '%keyboard%'
Filtering
User selects:
Price > 100
System narrows results using rules.
Example:
WHERE price > 100
Combined
Example:
Search: keyboard
Price: 50+
Results:
WHERE
name LIKE '%keyboard%'
AND price > 50
This is how most e-commerce websites work.
Part 2 — Query Parameters
Search URLs usually use query parameters.
Example:
/products?search=keyboard
Express:
const search = req.query.search;
Result:
"keyboard"
Multiple filters:
/products?search=keyboard&minPrice=50
Express:
req.query
contains:
{
search: 'keyboard',
minPrice: '50'
}
Part 3 — Building a Search Form
View:
<form method="get">
<input
type="text"
name="search"
placeholder="Search products"
>
<button type="submit">
Search
</button>
</form>
User enters:
keyboard
Browser navigates to:
/products?search=keyboard
No JavaScript required.
Simple.
Elegant.
Very 1998.
Still effective.
Part 4 — SQL LIKE
Searching typically uses:
LIKE
Example:
SELECT *
FROM products
WHERE name LIKE '%keyboard%'
Matches:
Gaming Keyboard
Mechanical Keyboard
Wireless Keyboard
Wildcard:
%
means:
Any characters
Examples:
| Pattern | Matches |
|---|---|
| keyboard% | Starts with keyboard |
| %keyboard | Ends with keyboard |
| %keyboard% | Contains keyboard |
Part 5 — First Search Query
Route:
router.get('/', (req, res) => {
const search = req.query.search || '';
const stmt = db.prepare(`
SELECT *
FROM products
WHERE name LIKE ?
ORDER BY id DESC
`);
const products = stmt.all(`%${search}%`);
res.render('products/list',
{
products,
search
}
);
});
Result:
/products?search=keyboard
returns matching products.
Part 6 — Preserving Search Values
Bad UX:
User searches:
keyboard
Results load.
Input becomes empty.
User forgets what they searched for.
Better:
<input
type="text"
name="search"
value="<%= search %>"
>
Input remains populated.
Professional applications do this everywhere.
Part 7 — Searching Multiple Columns
Current:
WHERE name LIKE ?
Better:
WHERE
name LIKE ?
OR description LIKE ?
Example:
const term = `%${search}%`;
stmt.all(
term,
term
);
Now users can find products using descriptions too.
Part 8 — Price Filtering
Users often want:
Show products above $100
Form:
<label>Minimum Price
<input type="number" name="minPrice">
</label>
URL:
/products?minPrice=100
SQL:
SELECT *
FROM products
WHERE price >= ?
Route:
const minPrice = Number(req.query.minPrice) || 0;
Part 9 — Combining Search & Price Filters
Example:
Search: keyboard
Min Price: 50
SQL:
SELECT *
FROM products
WHERE
name LIKE ?
AND price >= ?
Or you can combine multiple OR and AND together selectively like so:
SELECT *
FROM products
WHERE (name LIKE ? OR description LIKE ?) AND price >= ?
Result:
Mechanical Keyboard
$89.99
but not:
Cheap Keyboard
$19.99
Part 10 — Dynamic WHERE Clauses
Real applications rarely know which filters users will choose.
Bad:
if ( search ) {
...
}
if ( minPrice ) {
...
}
if ( maxPrice ) {
...
}
becomes messy.
Better:
const conditions = [];
const values = [];
Search:
if ( search ) {
conditions.push('(name LIKE ? OR description LIKE ?)');
values.push(`%${search}%`, `%${search}%`);
}
Min Price:
if ( minPrice ) {
conditions.push('price >= ?');
values.push(minPrice);
}
Build Query:
let sql = `
SELECT *
FROM products
`;
Add Conditions:
if(conditions.length) {
sql += `
WHERE
${conditions.join(' AND ')}
`;
}
Final query becomes:
SELECT *
FROM products
WHERE
(name LIKE ? OR description LIKE ?)
AND price >= ?
Only when needed.
Part 11 — SQL Injection Dangers
Never do this:
const sql = `
SELECT *
FROM products
WHERE name LIKE '%${search}%'
`;
Imagine:
search=' OR 1=1 --
You may accidentally return every record.
Always use placeholders:
WHERE name LIKE ?
This is one of the most important security habits in backend development.
Part 12 — Combining Search with Pagination
Current:
/products?page=2
Search:
/products?search=keyboard
Combined:
/products?search=keyboard&page=2
Pagination links must preserve filters.
Bad:
<a href="/products?page=2">
Loses search state.
Good:
<a
href="
/products?
search=<%= search %>
&page=<%= i %>
"
>
Now pagination continues searching.
Part 13 — Product Counts with Filters
Current:
SELECT COUNT(*)
FROM products
Incorrect when searching.
Need:
SELECT COUNT(*)
FROM products
WHERE ...
using the same conditions.
Why?
Suppose:
1000 products total
20 keyboard products
Pagination should display:
2 pages
not:
100 pages
Part 14 — Understanding Database Indexes
Imagine:
10 products
Search is instant.
Imagine:
1,000,000 products
Search becomes slower.
Indexes help.
Think:
Book Index
Instead of reading every page.
Create:
CREATE INDEX
idx_products_name
ON products(name);
Benefits:
Faster searches
Costs:
More storage
Slightly slower INSERTs
We’ll revisit indexing later when performance becomes a concern.
Part 15 — Building a Professional Search Form
Example:
<form method="get">
<input
name="search"
value="<%= search %>"
placeholder="Search"
>
<input
name="minPrice"
type="number"
value="<%= minPrice %>"
>
<button>
Filter
</button>
</form>
Result:
Search: keyboard
Minimum Price: 50
becomes:
/products?
search=keyboard
&minPrice=50
Simple.
Readable.
Bookmarkable.
Common Beginner Mistakes
Using POST for Search
Bad:
<form method="post">
Searches should use:
method="get"
Users can bookmark results.
Forgetting Placeholders
Bad:
${search}
inside SQL.
Always use:
?
Losing Filters During Pagination
Always preserve:
search
page
filters
sort
across links.
Not Validating Numeric Filters
Bad:
minPrice=banana
Validate everything.
Bonus Challenge
Add sorting:
Name ASC
Name DESC
Price ASC
Price DESC
while preserving:
Search
Filters
Pagination
Example:
/products?
search=keyboard
&minPrice=50
&sort=price_desc
&page=2
Professional CRUD systems often spend more code handling combinations of filters than handling the actual database operations.
Key Takeaways
Today you learned:
- Search vs filtering
- SQL LIKE
- Query parameters
- Dynamic WHERE clauses
- Combining filters
- Preserving state
- Search pagination
- SQL injection prevention
- Index fundamentals
Search and filtering are often where a CRUD application begins to feel like a real product. Once users can quickly locate data, the application becomes dramatically more useful.
⚠️ A large part of the content of this module was created using Generative AI (ChatGPT). The synthetic (AI-generated) content was reviewed and curated by Kostas Minaidis.