How to search records by part of a string
Short answer
The % signs are added to the value, not to the query:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$statement = $pdo->prepare('SELECT title FROM products WHERE title LIKE ?');
$statement->execute(['%tea%']);
echo implode(', ', $statement->fetchAll(PDO::FETCH_COLUMN));Output:
Tea, Green tea% and _ in the user's query
In LIKE the % sign means "any characters" and _ means "one character". To search for them literally, escape them:
<?php
$search = '50%_off';
echo str_replace(['\\', '%', '_'], ['\\\\', '\%', '\_'], $search);Output:
50\%\_offLarge tables
A search starting with % uses no index and is slow on large tables. Those use full-text search.
Functions used in this recipe
- PDO — A database connection.
- PDOStatement — A prepared statement and its result.
- str_replace — Replaces all occurrences of a substring with another string.