Open In App

PHP – Mysql LIKE Operator

Improve
Improve
Like Article
Like
Save
Share
Report

Problem Statement :

In this article, we are going to display data using LIKE operator with SQL in Xampp server.

Here we are going to consider the student address database as an example.

Requirements:

Xampp

Introduction:

PHP stands for hypertext preprocessor. It is used as a server-side scripting language and can be used to connect with MySQL server with xampp tool.

MySQL is a query language for managing databases.

  • LIKE OPERATOR

The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards that can be used in conjunction with the LIKE operator. They are:

  1. The percent sign (%)  which represents zero, one, or multiple characters
  2. The underscore sign (_) represents one, single character.

Syntax:

SELECT column1, column2, ...,columnn
FROM table_name
WHERE columnn LIKE pattern;

Description

  • letter% = gives the result starts with the given letter

Example:

Consider the following table:

Query:

Address starts with h:

SELECT  * from student_address WHERE saddress LIKE 'h%'

Output:

Address starts with h:
STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad
STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad
STUDENT-ID : 5 ----- NAME : gnanesh ----- ADDRESS : hyderabad

Query:

Name ends with h:

SELECT  * from student_address WHERE sname LIKE '%h';

Output:

Name ends with h:
STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad
STUDENT-ID : 5 ----- NAME : gnanesh ----- ADDRESS : hyderabad

Query:

Address contains “um” pattern

SELECT  * from student_address WHERE sname LIKE '%um%';

Output:

STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu

Query:

Address starts with r and ends with h.

SELECT  * from student_address WHERE sname LIKE 'r%h';

Output:

STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad.

Approach:

  • Create database(named database) and create table named student_address
  • Insert data into the table using PHP
  • Write PHP code to perform like operation
  • Observe the results

Steps:

  • Start xampp server.

  • Create database named database and create a table named student_address

  • Write PHP code to insert records into it. (data1.php)

PHP




<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql  = "INSERT INTO student_address VALUES (1,'sravan kumar','kakumanu');";
$sql .= "INSERT INTO student_address VALUES (2,'bobby','kakumanu');";
$sql .= "INSERT INTO student_address  VALUES (3,'ojaswi','hyderabad');";
$sql .= "INSERT INTO student_address  VALUES (4,'rohith','hyderabad');";
$sql .= "INSERT INTO student_address  VALUES (5,'gnanesh','hyderabad');";
  
if ($conn->multi_query($sql) === TRUE) {
  echo "data stored successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
  
$conn->close();
?>


open browser and type “localhost.data1.php” to execute it.

Output:

data stored successfully
  • PHP code demo for like operator for a letter starts with :

form.php

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "Like operator demo: "; echo"</h1>";
echo "<br>";
echo "address starts with h:";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT  * from student_address WHERE saddress LIKE 'h%'";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
echo "<br>";
echo "name starts with s ";
echo "<br>";
echo "<br>";
//sql query 
$sql1 = "SELECT  * from student_address WHERE sname LIKE 's%'";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Output:

localhost/form.php

  • PHP code demo for  a letter ends with :

form1.php

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "Like operator demo: "; echo"</h1>";
echo "<br>";
echo "name ends  with h:";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT  * from student_address WHERE sname LIKE '%h'";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
echo "<br>";
echo "address ends with u ";
echo "<br>";
echo "<br>";
//sql query 
$sql1 = "SELECT  * from student_address WHERE saddress LIKE '%u'";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Output:

  • PHP code demo for a substring match and letter starts with-ends with

form2.php

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "<h1>"; echo "Like operator demo: "; echo"</h1>";
echo "<br>";
echo "address contains um:";
echo "<br>";
echo "<br>";
//sql query
$sql = "SELECT  * from student_address WHERE sname LIKE '%um%'";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
echo "<br>";
echo "name starts with r and ends with h ";
echo "<br>";
echo "<br>";
//sql query 
$sql1 = "SELECT  * from student_address WHERE sname LIKE 'r%h'";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
    echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
    echo "<br>";
      
}
  
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Output:



Last Updated : 07 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads