Home PHP MySQL PHP MySQL LIKE Operator

PHP MySQL LIKE Operator

Select And Filter Data From A MySQL Database With MySQL LIKE Operator using PHP In XAMPP

By Sravan Kumar
Published: Last Updated on 166 views

In this guide, we will discuss how to select data from a table using MySQL LIKE operator and WHERE clause along with the SELECT command in a particular database using PHP in XAMPP.

If you haven't already created database and table, please refer the following guide:

For demonstration purpose, I created a table named "sales" in a MySQL database called "my_company" database with the below schema and records.

View Table Records In MySQL Database
View Table Records In MySQL Database

Heads Up: In XAMPP, the database and table names are case insensitive. It will consider upper/lower case as lowercase only.

Filtering Data From MySQL Table With LIKE Operator

The MySQL LIKE operator checks whether a specific character string matches a specified pattern.

In other words, the LIKE is the operator applied on string values in a column to get the values based on the given expression condition.

We specify an expression after the LIKE operator. The expression is used to compare the strings in a column. If the expression is present in the string, it will be displayed.

The supported expressions are given below:

  • %character - Return the value from the column if it ends with the specified character.
  • character% - Return the value from the column if it starts with the specified character.
  • character% character - Return the value from the column if it starts and ends with the specified character.
  • %sub-string% - Return the value if it contains the given substring at any position in the value.
  • character__ ( 2 underscores) - Return the value from the column if it starts with the specified character and the length of the character is 3. Please note that the length depends upon underscores with a character.
  • __character ( 2 underscores) - Return the value from the column if it ends with the specified character and the length of the character is 3.

MySQL LIKE Operator Syntax:

SELECT column1,column2,.,column n from table_name WHERE column_name LIKE expression

Steps

1. Specify the servername (E.g. localhost), database username (E.g. root), root user password and the database name (E.g. my_company). Here, my root user's password is empty.

2. Establish a connection using the mysqli_connect() function.

3. Write the SQL Query to filter data from MySQL database using WHERE clause and LIKE operator.

Code:

$query = "SELECT column1,…. from Sales where column_name LIKE expression";

4. Store the selected results into a variable called "final" using mysqli_query() function. It will take connection and query as parameters.

Code:

mysqli_query($connection, $query);

5. Get the rows one by one from the "final" variable using mysqli_num_rows() function. Then fetch the results by iterating through a while loop using mysqli_fetch_assoc() function.

Code:

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
    echo $i["column1”],…………..;
  }
} else {
  echo "No results";
}

6. Finally, we will close the connection by using the mysqli_close() function.

Syntax:

mysqli_close($connection);

Now, let us write a sample PHP code based on the above steps.

PHP code To Select Data From MySQL Database Using LIKE Operator

Example Code 1:

In this example, we will select all columns from the "sales" table where the values in name column,

  • starts with letter 'E',
  • ends with 's',
  • and contains letters 'gg'.

Create a new file named select.php under the /htdocs folder with the following contents in it.

Heads Up: If you use Linux, the htdocs folder will be under /opt/lampp/ directory. If you're on Windows, the htdocs will be usually in C:\xampp\ folder.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

//select all columns such that name column starts with 'E'
$query = "SELECT * from sales where name like 'E%'";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//take a new line
echo "<br>";
echo "<br>";

//select all columns such that name column ends with 's'
$query1 = "SELECT * from sales where name like '%s'";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//take a new line
echo "<br>";
echo "<br>";
//select all columns such that name column  contains 'gg'
$query2 = "SELECT * from sales where name like '%gg%'";

#get the result
$final2 = mysqli_query($connection, $query2);

if (mysqli_num_rows($final2) > 0) {
 //get the output of each row
  while($k = mysqli_fetch_assoc($final2)) {
      //get all columns
    echo "id: " . $k["id"]. "  ----> name: " . $k["name"]."  ----> count: " . $k["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL.

Example 1 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator
Example 1 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator

Example Code 2:

In this example, we will select all columns from the Sales table while filtering the values in the name column that only start with 'C' and end with 's'.

<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);

//select all columns such that name column starts with 'C' and ends with 's'
$query = "SELECT * from sales where name like 'C%s'";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}



//close the connection
mysqli_close($connection);
?>

Open your web browser and navigate to http://localhost/select.php URL.

Example 2 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator
Example 2 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator

Example Code 3:

In this example, we will select all columns from the "sales" table where values in the name column,

  • start with 'C' and length - 6 (Here we need to place 5 underscores after 'C'),
  • end with 's' and length - 4 (Here we need to place 3 underscores before 's').
<?php
//specify the server name and here it is localhost
$server_name = "localhost";

//specify the username - here it is root
$user_name = "root";

//specify the password - it is empty
$password = "";

//specify the database name
$database_name = "my_company";

// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password,$database_name);

// name  starts with 'C' and length - 6
$query = "SELECT * from sales where name like 'C_____'";

#get the result
$final = mysqli_query($connection, $query);

if (mysqli_num_rows($final) > 0) {
 //get the output of each row
  while($i = mysqli_fetch_assoc($final)) {
      //get all columns
    echo "id: " . $i["id"]. "  ----> name: " . $i["name"]."  ----> count: " . $i["count"]. "<br>";
  }
} else {
  echo "No results";
}

//new line
echo "<br>";
echo "<br>";

// name  ends  with 's' and length - 4
$query1 = "SELECT * from sales where name like '___s'";

#get the result
$final1 = mysqli_query($connection, $query1);

if (mysqli_num_rows($final1) > 0) {
 //get the output of each row
  while($j = mysqli_fetch_assoc($final1)) {
      //get all columns
    echo "id: " . $j["id"]. "  ----> name: " . $j["name"]."  ----> count: " . $j["count"]. "<br>";
  }
} else {
  echo "No results";
}

//close the connection
mysqli_close($connection);
?>

Open your web browser and point it to http://localhost/select.php URL.

Example 3 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator
Example 3 - Select Data From MySQL Database Using WHERE Clause And LIKE Operator

Conclusion

In this guide, we discussed how to select particular column or all columns from a MySQL table by specifying an expression through LIKE operator using PHP with 3 different examples.

You May Also Like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

This website uses cookies to improve your experience. By using this site, we will assume that you're OK with it. Accept Read More