We know that in MySQL we use the SQL SELECT command to read data, and we can use the WHERE clause in the SELECT statement to get the specified record.

The WHERE clause can use the equal sign = to set conditions for obtaining data, as in “runoob_author = ‘RUNOOB.COM'”.

But sometimes we need to get all the records in the runoob_author field that contain the “COM” character, and then we need to use the SQL LIKE clause in the WHERE clause.

SQL LIKE clauses use the percent character to represent arbitrary characters, similar to the asterisk * in UNIX or regular expressions.

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

grammar

Here is the general syntax for SQL SELECT statements that use the LIKE clause to read data from a table:

SELECT field1, field2,... fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'Copy the code
  • You can specify any condition in the WHERE clause.
  • You can use the LIKE clause in the WHERE clause.
  • You can use the LIKE clause instead of the equal sign =.
  • LIKE is usually used with %, similar to a metacharacter search.
  • You can specify one OR more conditions using AND OR OR.
  • You can use WHERE… in DELETE or UPDATE commands. The LIKE clause specifies the condition.

Use the LIKE clause in the command prompt

Below we will use WHERE… in SQL SELECT command. The LIKE clause reads data from the MySQL table runoob_tbl.

The instance

Here are all the records in the runoob_tbL table that end with COM in the runoob_author field:

SQL LIKE statement:

mysql> use RUNOOB; Database changed mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE ‘%COM’; +———–+—————+—————+—————–+ | runoob_id | runoob_title | runoob_author | Submission_date | + — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + | 3 | learn Java | | RUNOOB.COM The 2015-05-01 | | | | 4 learning Python RUNOOB.COM | | + — — — — — 2016-03-06 — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — + 2 rows in set (0.01sec)

Use the LIKE clause in PHP scripts

You can use the PHP function mysqli_query() and the same SQL SELECT with WHERE… Command of the LIKE clause to get the data.

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

But if you use WHERE for DELETE or UPDATE… Mysqli_fetch_array () is not required for S QL statements of LIKE clauses.

The instance

Table runoob_tbl: runoob_author (‘ COM ‘);

MySQL LIKE clause test

<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); Mysqli_query ($conn, “set names utf8”); $sql = ‘SELECT runoob_id, runoob_title, runoob_author, submission_date FROM runoob_tbl WHERE runoob_author LIKE “%COM”‘; mysqli_select_db( $conn, ‘RUNOOB’ ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(‘ cannot read data: ‘.mysqli_error ($conn)); } echo ‘<h2> mysqli_fetch_array ‘; Echo ‘< table border = “1” > < tr > < td > tutorial ID < / td > < td > title < / td > < / td > < td > the author submit date < / td > < td > < / tr >’; while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo “<tr><td> {$row[‘runoob_id’]}</td> “. “<td>{$row[‘runoob_title’]} </td> “. “<td>{$row[‘runoob_author’]} </td> “. “<td>{$row[‘submission_date’]} </td> “. “</tr>”; } echo ‘</table>’; mysqli_close($conn); ? >

The output result is as follows: