Understanding PHP MySQL Select WHERE Statement
In this article, we will discuss the PHP MySQL Select WHERE statement, how MySQL WHERE statement can use in PHP to retrieve data from a database based on conditions that are specified in the query.
The WHERE clause in MySQL is a clause used to filter the data based on a set of conditions that must be met.
By using this feature, we can retrieve only data that meets a specific set of criteria.
What is the PHP MySQL Select WHERE Statement?
The WHERE clause is used with the SELECT statement to filter the rows returned from the database based on specific conditions.
The WHERE clause is used to retrieve only those rows that satisfy the specified condition.
Syntax
The syntax of the PHP MySQL Select WHERE statement is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition;
In above syntax, column1, column2, … are the columns that we want to retrieve data from, and table_name is the name of the table from where we want to retrieve data.
The WHERE condition is used to specify the condition for filtering the rows.
Syntax of the WHERE Clause
The syntax of the WHERE clause is as follows:
SELECT column1, column2, ... FROM table_name WHERE condition1 {AND|OR} condition2;Here, condition1 and condition2 are the conditions that we want to apply to the WHERE clause.
We can use the AND or OR logical operators to combine multiple conditions.
PHP MySQL Select WHERE Examples
Let’s look at some examples of the PHP MySQL Select WHERE statement.
Retrieving data based on a single condition
Suppose we have a table named students with the following columns:
"id", "name", "age", and "gender"
We want to retrieve data from the “students” table where the “age” column is equal to 20.
The SQL query for this would be:
SELECT * FROM students WHERE age = 20;
This statement retrieves all the data from the “students” table where the “age” column is equal to 20.
Retrieving data based on multiple conditions
Suppose we have a table named employees with the following columns:
"id", "name", "age", "gender", and "salary"
We want to retrieve data from the “employees” table where the “age” column is greater than 25 and the salary column is greater than 50000.
The SQL query for this would be:
SELECT * FROM employees WHERE age > 25 AND salary > 50000;
Above statement retrieves all the data from the “employees” table where the “age” column is greater than 25 and the “salary” column is greater than 50000.
Retrieving data based on a pattern
Suppose we have a table named students with the following columns:
"id", "name", "age", and "gender"
We want to retrieve data from the “students” table where the “name” column starts with J.
The SQL query for this would be:
SELECT * FROM students WHERE name LIKE 'J%';
This statement retrieves all the data from the “students” table where the “name” column starts with “J”.
MySQLi Filtering with SELECT and WHERE
Here is an example where you select all (*) the columns that contain the lastname “Anniston” from the Users table and display them on the page:
Below is an example using MySQLi’s Object-Oriented method:
Example (MySQLi Object-oriented):
- Our first step is to create an SQL query that selects all columns from the Users table that have lastnames containing the value “Anniston” in the data.
- After that, a query is run and the result is put into a variable called $result, which is used to store the results.
- There is then a check in the function num_rows() to determine if there are more than zero rows in the result set.
- The function fetch_assoc() returns all the results that can be used to loop over and see if there are any more rows returned than zero, in which case it places them in an associative array.
- Through the use of a while() loop, all columns of the result set are output.
Example (MySQLi Procedural):
Example (MySQLi Object-oriented):
PDO and Prepared Statements for Select and Where
Example:
Example Explanation
First we create a TableRows class that extends RecursiveIteratorIterator. This class is used to format the data in a way that can be displayed in an HTML table.
The current(), beginChildren(), and endChildren() methods define how each data element should be displayed within the table.
The script then sets up the database connection using the PDO class. It prepares and executes a SQL query to retrieve all the rows from the Users table where the lastname column equals “Anniston”. The setFetchMode() method is used to set the result to be an associative array.
Finally, the script uses a foreach loop to iterate through the resulting array and pass the data to the TableRows class. The TableRows class formats the data into table cells and echos them to the webpage.
The resulting HTML table displays the id, firstname, lastname, email, age, and gender columns for all rows where the lastname column equals “Anniston“.
Conclusion
The PHP MySQL Select WHERE statement is a powerful tool for filtering the data from a database table based on specific conditions. The WHERE clause allows us to retrieve only those rows that satisfy the specified condition. By using the AND or OR logical operators, we can combine multiple conditions. By using the LIKE operator, we can retrieve data based on a pattern. The PHP MySQL Select WHERE statement is a fundamental concept in MySQL and is used extensively in web application development.
