PHP – Mysql LIKE Operator
Last Updated :
07 Apr, 2021
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:
Similar Reads
RLIKE operator in MySQL
RLIKE : This operator in MySQL is used to performs a pattern match of a string expression against a pattern. Syntax : RLIKE pattern Parameters : This method accepts one parameter as mentioned in syntax. pattern -The pattern which we want to match against an expression. Various pattern and their usag
2 min read
PHP - MySQL : INTERSECTION operation
In this article, we are going to perform a database operation that includes an intersection of two tables using PHP in xampp server. So, we are taking the student database. Requirements -xampp server Introduction : MySQL - It is a database query language to manage databases. PHP is a server-side pro
4 min read
SQL LIKE Operator
The SQL LIKE operator is used for performing pattern-based searches in a database. It is used in combination with the WHERE clause to filter records based on specified patterns, making it essential for any database-driven application that requires flexible search functionality. In this article, we w
5 min read
PHP Operators
In PHP, operators are special symbols used to perform operations on variables and values. Operators help you perform a variety of tasks, such as mathematical calculations, string manipulations, logical comparisons, and more. Understanding operators is essential for writing effective and efficient PH
9 min read
Node.js MySQL OR Operator
NodeJs: An open-source platform for executing javascript code on the server-side. Also, a javascript runtime built on Chrome’s V8 JavaScript engine. It can be downloaded from here. Mysql An open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is the
2 min read
PHP | MySQL ORDER BY Clause
The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. Syntax : The basic syntax of the Order By clause is - Implementation of the Order By Clause : Let us consider the
3 min read
SQL - Logical Operators
SQL Logical Operators are essential tools used to test the truth of conditions in SQL queries. They return boolean values such as TRUE, FALSE, or UNKNOWN, making them invaluable for filtering, retrieving, or manipulating data. These operators allow developers to build complex queries by combining, n
9 min read
SQL NOT EQUAL Operator
The SQL NOT EQUAL operator is a comparison operator used to check if two expressions are not equal to each other. It helps filter out records that match certain conditions, making it a valuable tool in SQL queries. In this article, We will explore the SQL NOT EQUAL operator, including its syntax, us
4 min read
SQL IN Operator
The SQL IN operator filters data based on a list of specific values. In general, we can only use one condition in the Where clause, but the IN operator allows us to specify multiple values. In this article, we will learn about the IN operator in SQL by understanding its syntax and examples. IN Opera
4 min read
PHP - Mysql Joins
In this article, we are going to join two tables using PHP and display them on the web page. Introduction : PHP is a server-side scripting language, which is used to connect with databases. Using this, we can get data from the database using PHP scripts. The database language that can be used to com
9 min read