MySQL LIKE clause

MySQL LIKE clause

We know that SQL SELECT commands are used in MySQL to read data, and we can use the WHERE clause in SELECT statements to get the specified records.

In the WHERE clause, you can use an equal sign (=) to set the conditions for obtaining data, such as "w3cschool_author is 'Sanjay'".

But sometimes we need to w3cschool_author all the records in the "jay" character in the field, and then we need to use the SQL LIKE clause in the WHERE clause.

The SQL LIKE clause uses a percent sign (%) character to represent any character, similar to the asterisk in UNIX or a regular expression.

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

Grammar

The following is a common syntax for SQL SELECT statements to read data from a data table using the LIKE clause:

SELECT field1, field2,...fieldN 




FROM table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• You can specify any condition in the WHERE clause.
• You can use theLIKE clause in the WHERE clause.
• You can use theLIKE clause instead of the equal sign.
• LIKE is typically used with %, similar to a meta-character search.
• You can use AND or OR to specify one or more conditions.
• You can use WHERE in DELETE or UPDATE commands... THE LIKE clause to specify the condition.

Use the LIKE clause in the command prompt

Below we'll use WHERE in SQL SELECT commands... The LIKE clause reads data from the MySQL w3cschool_tbl data table.

Instance

Here'w3cschool_tbl we'll w3cschool_author records in the table that end with "jay" in the field:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT * from w3cschool_tbl 
    -> WHERE w3cschool_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| w3cschool_id | w3cschool_title | w3cschool_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Use the LIKE clause in PHP scripts

You can use the PHP mysql_query () and the same SQL SELECT with WHERE... T HE COMMAND of the LIKE clause to get the data.

This function is used to execute SQL commands and then output data for all queries mysql_fetch_array the PHP function, The File ().

But if YOUTE or UPDATE use WHERE... S QL statements for the LIKE clause, you do not need to use mysql_fetch_array() function.

Instance

Here are all the records that we w3cschool_tbl "jay" w3cschool_author in the table using PHP scripts:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl
        WHERE w3cschool_author LIKE "%jay%"';

mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['w3cschool_id']}  <br> ".
         "Title: {$row['w3cschool_title']} <br> ".
         "Author: {$row['w3cschool_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>