Php подготовленные запросы like

Using mysqli prepared statements with LIKE operator in SQL

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.

Related articles:

Got a question?

I am the only person to hold a gold badge in , and on Stack Overflow and I am eager to show the right way for PHP developers.

Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.

SEE ALSO:

• Top 10 PHP delusions
• PDO Examples
• Mysqli Examples
• Principles of web-programming
• Mysqli SELECT query with prepared statements
• How to run a SELECT query using Mysqli
• How to run an INSERT query using Mysqli
• How to run an UPDATE query using Mysqli
• Mysqli prepared statement with multiple values for IN clause
• Mysqli examples
• How to call stored procedures with mysqli
• How to create a search filter for mysqli
• How to run 1000s insert queries with mysqli?

Latest comments:

• 28.07.23 14:08
  PHP programmer for (The only proper) PDO tutorial:
  In the section about transactions, you say that you should write . catch.
  read more
• 24.07.23 01:16
  Tim Dawson for PDO Examples:
  Further to my previous reply to your e-mail, I now find that the following, with double quotes.
  read more
• 23.07.23 16:47
  Tim Dawson for PDO Examples:
  I have a web site where visitors can look at accommodation reviews. A selection of reviews is.
  read more
• 17.07.23 21:18
  Jim for (The only proper) PDO tutorial:
  Thanks for the reply! I was hoping you would know if what I’m attempting is even possible! :).
  read more
• 16.07.23 00:35
  Jim for (The only proper) PDO tutorial:
  Hi, I just discovered this site today, so first and foremost, thank you for all your work.
  read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator’s review.

Comments:

Thank you so much. I searched for hours before I found your solution. In this piece of code, $name = «%$name%»; I was using single quotes, changed to double quotes and my queries work like a charm. Thank you so much once again.

As of 2/05/2020, in both SQL queries on this page there is an equal sign after the ‘LIKE’ that should not be there.

Источник

PDO LIKE

Summary: in this tutorial, you’ll learn how to use PDO to execute a query with the LIKE operator.

Introduction to the SQL LIKE operator

The LIKE operator returns true if a character string matches a specified pattern. Typically, a pattern includes wildcard characters like:

For example, the %er% will match any string that contains the string er , e.g., peter , understand , etc.

Typically, you use the LIKE operator in the WHERE clause of the SELECT , UPDATE , and DELETE statement.

Execute a query that contains the LIKE operator in PDO

To execute a query that contains a LIKE operator in PDO, you need to construct the pattern upfront.

For example, to select the book with titles that contain the string ‘es, you first construct a SELECT statement like this:

$sql = 'SELECT book_id, title FROM books WHERE title LIKE :pattern';Code language: PHP (php)

And then bind the string ‘%es%’ to the prepared statement.

The following example illustrates how to execute a query that includes the LIKE operator:

 /** * Find books by title based on a pattern */ function find_book_by_title(\PDO $pdo, string $keyword): array < $pattern = '%' . $keyword . '%'; $sql = 'SELECT book_id, title FROM books WHERE title LIKE :pattern'; $statement = $pdo->prepare($sql); $statement->execute([':pattern' => $pattern]); return $statement->fetchAll(PDO::FETCH_ASSOC); > // connect to the database $pdo = require 'connect.php'; // find books with the title matches 'es' $books = find_book_by_title($pdo, 'es'); foreach ($books as $book) < echo $book['title'] . '
'; >Code language: PHP (php)

The function find_book_by_title() returns the books with the title that matches with the $keyword .

First, make the pattern by adding the wildcard characters to the beginning and end of the $keyword :

$pattern = '%' . $keyword . '%';Code language: PHP (php)

Second, construct an SQL statement that contains a LIKE operator in the WHERE clause:

$sql = 'SELECT book_id, title FROM books WHERE title LIKE :pattern';Code language: PHP (php)

Third, create a prepared statement:

$statement = $pdo->prepare($sql);Code language: PHP (php)

After that, execute the statement with the value that comes from the pattern:

$statement->execute([':pattern' => $pattern]);Code language: PHP (php)

Finally, return all rows from the result set by using the fetchAll() method:

return $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

The following code find books with the title contains the keyword ‘es’ :

// connect to the database $pdo = require 'connect.php'; // find books with the title matches 'es' $books = find_book_by_title($pdo, 'es'); foreach ($books as $book) < echo $book['title'] . '
'; >Code language: PHP (php)

Marcus Makes a Movie Box of ButterfliesCode language: PHP (php)

Summary

Источник