How to write database query filter statements using PHP

PHP (Hypertext Preprocessor) is an open source general-purpose scripting language that can be embedded in HTML. PHP is most widely used for writing database applications. In this article, we will learn how to write database query filter statements using PHP.

Database query is a basic operation because we usually need to retrieve specific data in the database rather than the entire data set. In order to achieve this, we need to use query statements. A query statement is a statement written in standard SQL language that helps us retrieve data from the database. In PHP, we use the mysql_query() function to execute the query and the mysql_fetch_array() function to retrieve the result set.

We will use the following data table to demonstrate how to use query statements:

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary DECIMAL(8,2)
);

The following are some basic query statements:

SELECT * FROM employee  // 检索所有行和列
SELECT name, age FROM employee  // 只检索姓名和年龄列
SELECT * FROM employee WHERE name='John'  // 只检索名字为John的行
SELECT * FROM employee WHERE age>30  // 只检索年龄大于30岁的行

In the above example, use the WHERE sub Sentences can be selected based on specified conditions. Other operators can also be used, such as <, >, =, !=, LIKE, BETWEEN, etc.

SELECT * FROM employee WHERE age BETWEEN 30 AND 40;  // 检索年龄在30到40岁之间的员工
SELECT * FROM employee WHERE name LIKE 'J%';  // 检索以字母J开头的名字
SELECT * FROM employee WHERE salary > 5000 AND age < 40;  // 检索工资大于5000且年龄小于40岁的员工

The above example illustrates how to use the WHERE clause and operators to add filter conditions in the query.

In addition to the WHERE clause and operators, we can also use the ORDER BY clause to sort the result set and the GROUP BY clause to group the result set.

SELECT * FROM employee ORDER BY salary DESC;  // 按工资降序排列
SELECT name, AVG(salary) FROM employee GROUP BY name;  // 按姓名分组并计算平均工资

Finally, we can also use the LIMIT clause to limit the size of the result set.

SELECT * FROM employee LIMIT 10;  // 限制结果集为10行
SELECT * FROM employee LIMIT 5,10;  // 从第6行开始，取10行结果

Summary:

In this article, we learned how to use PHP to write database query filter statements. We saw how to execute a query using the mysql_query() function and retrieve the result set using the mysql_fetch_array() function. We also covered how to add filtering conditions using the WHERE clause and operators, and how to sort and group the result set using the ORDER BY and GROUP BY clauses. Finally, we also learned how to use the LIMIT clause to limit the size of the result set. By using these techniques, we can easily retrieve the required data from large amounts of data and perform operations such as sorting, grouping restrictions, etc.

The above is the detailed content of How to write database query filter statements using PHP. For more information, please follow other related articles on the PHP Chinese website!