PHP & MySQL - LIKE Clause

The MySQL LIKE clause is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the LIKE clause are given below:

Wildcard Characters in MySQL

Symbol	Description	Example
%	Represents zero, one or multiple characters.	'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_	Represents one character.	'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.

The syntax for using LIKE Clause is given below:

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

The table below describes patterns which is used with LIKE clause and uses (%) and (_).

Pattern	Description
'J%'	A value that start with "J".
'%n'	A value that end with "n".
'%oh%'	A value that have "oh" in any position.
'_o%'	A value that have "o" in the second position.
'J_%'	A value that start with "J" and have at least 2 characters.
'J__%'	A value that start with "J" and have at least 3 characters.
'J%n'	A value that start with "J" and ends with "n".

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.

Select by pattern - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The example below demonstrates how to use the LIKE clause to select records of Employee table where City starts with "New" using object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//getting query result from the database
$sql = 'SELECT Name, Age, City 
FROM Employee 
WHERE City LIKE "New%";';
$result = $mysqli->query($sql);

//fetching associative array
while ($row = $result->fetch_assoc()) {
  printf("%s, %d, %s\n", $row["Name"], $row["Age"], $row["City"]);
}

//free result set
$result->free_result();

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

Marry, 24, New York
Ramesh, 28, New Delhi

Select by pattern - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//getting query result from the database
$sql = 'SELECT Name, Age, City 
FROM Employee 
WHERE City LIKE "New%";';
$result = mysqli_query($mysqli, $sql);

//fetching associative array
while ($row = mysqli_fetch_assoc($result)) {
  printf("%s, %d, %s\n", $row["Name"], $row["Age"], $row["City"]);
}

//free result set
mysqli_free_result($result);

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

Marry, 24, New York
Ramesh, 28, New Delhi

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.