MySQL LIKE Clause

We know that in MySQL, we use SQL SELECT command to read data, At the same time, we can use WHERE clause in the SELECT statement to retrieve specified records.

WHERE clause can use the equal sign = to set the conditions for retrieving data, such as "w3codebox_author = 'oldtoolbag.com".

But sometimes we need to get w3All records in the codebox_author field containing the character 'COM', at this time we need to use SQL LIKE clause in the WHERE clause.

In SQL LIKE clause, the percentage sign % character represents any character, similar to the asterisk in UNIX or regular expressions. *.

If the percentage sign % is not used, the LIKE clause has the same effect as the equal sign =.

Syntax

The following is the general syntax for using LIKE clause in SQL SELECT statement to read data from a table:

SELECT field1, field2,...fieldN　
FROM table_name
WHERE field1　LIKE condition1　[AND [OR]] field2　= 'somevalue'

• You can specify any condition in the WHERE clause.

• You can use LIKE clause in the WHERE clause.

• You can use LIKE clause instead of the equal sign =.

• LIKE is usually used with %, similar to a wildcard search.

• You can use AND or OR to specify one or more conditions.

• You can use WHERE...LIKE clause in DELETE or UPDATE commands to specify conditions.

Use LIKE clause in the command prompt

Below we will use WHERE...LIKE clause in the SQL SELECT command to retrieve data from MySQL table w3codebox_tbl to read data.

Online example

Below is what we will use w3codebox_tbl table to get w3All records ending with COM in the codebox_author field:

mysql> use w3codebox;
Database changed
mysql> SELECT　*　from w3codebox_tbl WHERE w3codebox_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| w3codebox_id            | w3codebox_title         | w3codebox_author | submission_date |
+-----------+---------------+---------------+-----------------+
|　3　　　　　　　　　| Learn Java            | oldtoolbag.com            |　2015-05-01　　　　　　|
|　4　　　　　　　　　| Learn Python         | oldtoolbag.com            |　2016-03-06　　　　　　|
+-----------+---------------+---------------+-----------------+
2　rows in set (0.01　sec)

Using LIKE clause in PHP script

You can use the mysqli_query() function and the same SQL SELECT command with WHERE...LIKE clause to retrieve data.

This function is used to execute SQL commands and then output all query data through the PHP function mysqli_fetch_array().

However, if you use WHERE...LIKE clause in DELETE or UPDATE SQL statements, there is no need to use the mysqli_fetch_array() function.

Online example

The following is a PHP script we use in w3Reading w from the codebox_tbl table3All records ending with COM in the codebox_author field:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent garbled Chinese characters
mysqli_query($conn, "set names utf8');
　
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl
　　　　　　　　WHERE w3codebox_author LIKE "%COM"';
　
mysqli_select_db($conn, 'w3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2">Basic Tutorial Website mysqli_fetch_array Test<h2">
echo '<table border="1><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
　　　　echo "<tr><td>" {$row['w3codebox_id'}</td>".
　　　　　　　　　"<td>{$row['w3codebox_title']} </td>".
　　　　　　　　　"<td>{$row['w3codebox_author']} </td>".
　　　　　　　　　"<td>{$row['submission_date']} </td>".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown as follows: