In this guide, we will discuss how to select records from a MySQL database based on specific conditions with the WHERE term and the SELECT statement using PHP in the XAMPP stack.
Prerequisites
Make sure you have created a database and a table in the XAMPP stack as described in the wizard below.
For demonstration purposes, I created a table with the name “discounts” in a MySQL database called “my company” with the following format and entries.
Filter data from the MySQL table with a WHERE clause using PHP
The WHERE clause is used to export only records that correspond to a specific condition. The WHERE clause will check the condition by getting an operator followed by a value.
WHERE Drafting a clause:
SELECT column1,column2,.,column n from table_name WHERE column_name operator value;
Where,
column_name– the column to which the condition applies.operator– Used to control the situation.value– It is the string / numeric value compared to each column value in the table.
Steps
1. Specify the server name (eg localhost), the database username (e.g. localhost) root), root user password and database name (eg my_company). Here, my root the user password is empty.
2. Create a link using mysqli_connect() mode. It will take the server name, username and password as parameters.
Code:
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);
3. Specify the SQL query to select a specific column or record all columns from the conditional table. In this step, we can specify the SQL query to select columns from the table in a variable.
For example, I’re going to use the name of the database called my company and store it in a variable named Question. The name of the table is discounts which has three columns.
Code:
$query = "SELECT column1,…. from sales where column_name operator value";
4. Save the selected results to a variable called “final” using the mysqli_query() mode. It will take the “login” and “query” parameters.
Code:
mysqli_query($connection, $query);
5. Take the rows one by one from the “final” variable using mysqli_num_rows() mode. Then retrieve the results by repeating through a while using loop mysqli_fetch_assoc() mode. will need “final“variable as a parameter.
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, close the link using mysqli_close() mode.
Code:
mysqli_close($connection);
Now, let’s write a sample PHP code based on the above steps.
PHP code To select data from the MySQL database using the WHERE clause
Example Code 1:
In this example, we will select all the columns from the “sales” table where ID value is greater than 4 and display the result on a PHP page.
So our operator will be greater than (“>”) and the price is 4 identifying it column_name as ID card.
Create a new file by name select.php in accordance with /htdocs folder with the following contents.
Head up: If you are using Linux, the htdocs the folder will be down /opt/lampp/ Index. If you are using Windows, the htdocs will usually be inside 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 - "my_company"
$database_name = "my_company";
// Creating the connection by specifying the connection details
$connection = mysqli_connect($server_name, $user_name, $password, $database_name);
//sql query to select particular columns
//select all columns such that id is greater than 4
$query = "SELECT * from sales where id>4";
#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 browser and place it in it URL. You can see that the data is selected where The ID is greater than 4 and the result is displayed in the browser window.
Example Code 2:
In this example, we will select all the columns from the “sales” table with name as “Eggs” and display the result on the PHP page.
So our operator will be equal to (“=”) and the value is “Eggs” specifying column_name as name.
<?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);
//sql query to select particular columns
//select all columns such that name equals to Eggs
$query = "SELECT * from sales where name="Eggs"";
#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 browser and place it in it URL. You can see that the data is selected where name = “Eggs” is displayed.
Example Code 3:
In this example, we will select all the columns from the “Sales” table with count to 45 and display the result on the PHP page.
So our operator will be equal to (“=”) and the value is 45 specifying the column_name as count.
<?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);
//sql query to select particular columns
//select all columns such that count is 45
$query = "SELECT * from sales where count =45";
#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 browser and place it in it URL. You can see that the data is selected where count = 45 and the resulting column is displayed.
conclusion
In this tutorial, we have discussed three different ways to select data from a MySQL database based on specific conditions using PHP with a WHERE clause. The first example showed you how to filter data using the numeric value “id”. The second and third examples explained how to select data using the string value “name” and the numeric value “count” respectively.
