LIKE Query in MySQL

We know that WHERE clause can be used to filter data retrieved via SELECT statement. For instance you can select record of a patient whose first name is ‘abc’ via WHERE clause. However a problem with WHERE clause is that it looks for a perfect match. What if we want to retrieve record of all those patients whose first name starts with let’s say ‘b’ or the patient who have ‘er’ in their names? LIKE query allows us to select records that partially contain a specific string. Syntax of LIKE query is as follows:

SELECT column1, column2, column3 FROM table
WHERE column1 LIKE somestring AND/OR condition2 .... AND/OR conditionN

LIKE clause in Object Oriented MySQLi

To select filtered table data using LIKE query via MySQLi, pass the query for selecting data along with the condition in the LIKE clause to the query function of the mysqli object. Take a look at the following example.




Using LIKE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing LIKE clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "WHERE patient_fname LIKE '%n'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns for all records in Patient table where patient_fname column has a value that ends with ‘n’. Notice that ‘%’ symbol is used for specifying the position of the characters to be ignored. Here ‘%n’ there can be anything before ‘n’. Similarly ‘%er%’ means any string that contains ‘er’

LIKE clause in Procedural MySQLi

Selecting filtered table data using LIKE clause in MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using LIKE clause in MySQLi



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing LIKE clause

$query = "SELECT patient_fname, patient_age FROM Patient ".
    "WHERE patient_fname LIKE '%er%'";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age columns for all records in Patient table where patient_fname column has a value that contains “er” in it.

<<< Sorting MySQL table data via PHP ScriptSelect Unique Data via Distinct In MySQL >>>