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(); ?>
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(); ?>
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.