PHP & MySQL Tutorial PHP & MySQL References

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

SymbolDescriptionExample
%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 (_).

PatternDescription
'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:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

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.