MySQL LIKE clause

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

  Friends who need the framework source code can see my personal profile and contact me, Recommended distributed architecture source code.  

  An equal sign can be used in the WHERE clause  =  To set the conditions for obtaining data, such as "xxxxxx_author = 'XXXXXX.COM'".

But sometimes we need to get XXXXXX_ The author field contains all records with the "COM" character, so we need to use the SQL LIKE clause in the WHERE clause.

Use percent sign in SQL LIKE clause  % Character to represent any character, similar to the asterisk in UNIX or regular expressions  *.

If a percent sign is not used  %, LIKE clause and equal sign  =  The effect is the same.

grammar

The following is the general syntax for SQL SELECT statements to read data from data tables using the LIKE clause:

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

• 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 associated with  %  Used together, similar to a metacharacter search.
• You can use AND OR to specify one OR more conditions.
• You can use the WHERE...LIKE clause in the DELETE or UPDATE command to specify conditions.

Use the LIKE clause at the command prompt

Below, we will use the WHERE...LIKE clause in the SQL SELECT command to retrieve XXXXXX from the MySQL data table_ Read data from TBL.

example

The following is what we will xxxxxx_ Get XXXXXX from TBL table_ In the author field   COM   All records ending with:

SQL LIKE statement:

mysql> use XXXXXX;
Database changed
mysql> SELECT * from xxxxxx_tbl  WHERE xxxxxx_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| xxxxxx_id | xxxxxx_title  | xxxxxx_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | study Java   | XXXXXX.COM    | 2021-11-20      |
| 4         | study Python | XXXXXX.COM    | 2021-11-21      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

Using the LIKE clause in PHP scripts

You can use mysqli of PHP functions_ Query() and the same SQL SELECT command with WHERE...LIKE clause to obtain data.

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

However, if it is an S QL statement that uses the WHERE...LIKE clause in DELETE or UPDATE, you do not need to use mysqli_fetch_array() function.

example

The following is our PHP script in XXXXXX_ Read XXXXXX from TBL table_ All records ending in COM in the author field:

MySQL LIKE clause test:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';            // mysql user name
$dbpass = '123456';          // mysql username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
// Set the code to prevent Chinese random code
mysqli_query($conn , "set names utf8");
 
$sql = 'SELECT xxxxxx_id, xxxxxx_title, 
        xxxxxx_author, submission_date
        FROM xxxxxx_tbl
        WHERE xxxxxx_author LIKE "%COM"';
 
mysqli_select_db( $conn, 'XXXXXX' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>xx course mysqli_fetch_array test<h2>';
echo '<table border="1"><tr><td>course 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['xxxxxx_id']}</td> ".
         "<td>{$row['xxxxxx_title']} </td> ".
         "<td>{$row['xxxxxx_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>