Using mysqli prepared statements with LIKE operator in SQL

1. SELECT query with LIKE operator
2. Using a helper function
3. Comments (3)

Before running any query with mysqli, make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.

SELECT query with LIKE operator

In general, SELECT with LIKE is no different from any other SELECT query with prepared statements. However, there are two certain gotchas that one should keep in mind:

1. A placeholder cannot represent an arbitrary part of a query but only a whole data literal (or, to put it simpler, a string or a number). Hence, an SQL like this field LIKE '%?%' won't work. Hence, the percent marks should be added to the source variable instead. Then ,a variabe that would contain something like '%search string%' can be bound to a placeholder the regular way.
2. bind_param() function accepts only variables, and strings are not allowed. Hence, a code like this $stmt->bind_param("s", "%$var%"); won't work, causing "Only variables can be passed by reference" error. So we must prepare our variable before binding it.

Knowing that now we can create a prepared statement with LIKE:

$name = "%$name%"; // prepare the $name variable 
$sql = "SELECT * FROM users WHERE name LIKE ?"; // SQL with parameters
$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $name); // here we can use only a variable
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$rows = $result->fetch_all(MYSQLI_ASSOC); // all rows matched

Using a helper function

As you may noted, the code for a prepared statement is quite verbose. If you like to build a code like a Lego figure, with shining ranks of operators, you may keep it as is. If you, like me, hate useless repetitions and like to write concise and meaningful code, then there is a simple helper function. With it, the code will become two times shorter:

$sql = "SELECT * FROM users WHERE name LIKE ?"; // SQL with parameters
$stmt = prepared_query($conn, $sql, ['%$name%']);
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC); // fetch an array of rows

Unlike bind_param(), our helper function accepts strings as parameters, hence you can add percent marks right at the call time.