PHP – Mysql LIKE Operator
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.
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:
- The percent sign (%) which represents zero, one, or multiple characters
- 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:
- Create database named database and create a table named student_address
- Write PHP code to insert records into it. (data1.php)
PHP
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "database" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
if ( $conn ->connect_error) {
die ( "Connection failed: " . $conn ->connect_error);
}
$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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "database" ;
$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 = "SELECT * from student_address WHERE saddress LIKE 'h%'" ;
$result = $conn ->query( $sql );
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>" ;
$sql1 = "SELECT * from student_address WHERE sname LIKE 's%'" ;
$result1 = $conn ->query( $sql1 );
while ( $row = mysqli_fetch_array( $result1 )){
echo " STUDENT-ID : " . $row [ 'sid' ], " ----- NAME : " . $row [ 'sname' ] , " ----- ADDRESS : " . $row [ 'saddress' ] ;
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output:
localhost/form.php
- PHP code demo for a letter ends with :
form1.php
PHP
<html>
<body>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "database" ;
$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 = "SELECT * from student_address WHERE sname LIKE '%h'" ;
$result = $conn ->query( $sql );
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>" ;
$sql1 = "SELECT * from student_address WHERE saddress LIKE '%u'" ;
$result1 = $conn ->query( $sql1 );
while ( $row = mysqli_fetch_array( $result1 )){
echo " STUDENT-ID : " . $row [ 'sid' ], " ----- NAME : " . $row [ 'sname' ] , " ----- ADDRESS : " . $row [ 'saddress' ] ;
echo "<br>" ;
}
$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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "database" ;
$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 = "SELECT * from student_address WHERE sname LIKE '%um%'" ;
$result = $conn ->query( $sql );
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>" ;
$sql1 = "SELECT * from student_address WHERE sname LIKE 'r%h'" ;
$result1 = $conn ->query( $sql1 );
while ( $row = mysqli_fetch_array( $result1 )){
echo " STUDENT-ID : " . $row [ 'sid' ], " ----- NAME : " . $row [ 'sname' ] , " ----- ADDRESS : " . $row [ 'saddress' ] ;
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output:
Last Updated :
07 Apr, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...