MySQLi - Like Clause-alljchome-开发者的教程家园

MySQLi Tutorial

MySQL

MySQLi Useful Resources

Selected Reading

MySQLi - Like Clause

We have seen the SQL SELECT command to fetch data from the MySQL table. We can also use a conditional clause called as the WHERE clause to select the required records.

A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if "tutorial_author = Sanjay ". But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay". This can be handled using SQL LIKE Clause along with the WHERE clause.

If the SQL LIKE clause is used along with the % character, then it will work pke a meta character (*) as in UNIX, while psting out all the files or directories at the command prompt. Without a % character, the LIKE clause is very same as the equal to sign along with the WHERE clause.

Syntax

The following code block has a generic SQL syntax of the SELECT command along with the LIKE clause to fetch data from a MySQL table.


SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 =  somevalue

You can specify any condition using the WHERE clause.

You can use the LIKE clause along with the WHERE clause.

You can use the LIKE clause in place of the equals to sign.

When LIKE is used along with % sign then it will work pke a meta character search.

You can specify more than one condition using AND or OR operators.

A WHERE...LIKE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.

Using the LIKE clause at the Command Prompt

This will use the SQL SELECT command with the WHERE...LIKE clause to fetch the selected data from the MySQL table – tutorials_tbl.

Example

The following example will return all the records from the tutorials_tbl table for which the author name ends with jay −


root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
   → WHERE tutorial_author LIKE  %jay ;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      |  JAVA Tutorial |     Sanjay      |    2007-05-21   |   
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Using LIKE clause inside PHP Script

PHP uses mysqp query() or mysql_query() function to select records in a MySQL table using Like clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax


$mysqp→query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sql Required - SQL query to select records in a MySQL table using Like Clause.
2	$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Sr.No.

Parameter & Description

$sql

Required - SQL query to select records in a MySQL table using Like Clause.

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using pke clause in a table −

Copy and paste the following example as mysql_example.php −


<html>
   <head>
      <title>Using Like Clause</title>
   </head>
   <body>
      <?php
         $dbhost =  localhost ;
         $dbuser =  root ;
         $dbpass =  root@123 ;
         $dbname =  TUTORIALS ;
         $mysqp = new mysqp($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqp→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqp→connect_error);
            exit();
         }
         printf( Connected successfully.<br /> );
   
         $sql =  SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author pke "Mah%" ;
		 
         $result = $mysqp→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf( No record found.<br /> );
         }
         mysqp_free_result($result);
         $mysqp→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we ve entered multiple records in the table before running the select script.


Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

MySQLi - Like Clause

Syntax

Using the LIKE clause at the Command Prompt

Example

Using LIKE clause inside PHP Script

Syntax

Example

Output

友情链接