Focus on PHP database query statements

PHP is a popular server-side scripting language that can bring together many different data types and databases. Especially in the development of web applications, it is often necessary to access databases to retrieve and manipulate data. This article will focus on PHP database query statements.

1. What is a database query statement

In the process of operating the database, we often need to obtain data from the database. At this time, we need to use query statements. Database query (SQL Query) is a set of instructions for retrieving data from a database. SQL is the abbreviation of Structured Query Language, which is a language widely used in relational data management systems (RDBMS) for accessing and operating databases.

In PHP, we can use different facilities to access various types of databases. PHP supports MySQL, PostgreSQL, Oracle and other databases. Each database has slightly different query statements, but they are basically implemented using SQL language.

2. The structure of the query statement

A simple query statement usually contains three parts: SELECT, FROM and WHERE. In SELECT, specify the columns to be retrieved; in FROM, specify the name of the table; in the WHERE clause, specify the search conditions.

The following is the basic query statement structure:

SELECT column1, column2, ...
FROM table
WHERE condition;

column indicates what needs to be retrieved Column names, separate multiple column names with commas. Usually * (asterisk) is used instead of column names to indicate selecting all columns.

table indicates the name of the table that needs to be operated.

condition is an optional option that specifies the conditions and restrictions for retrieving records and uses logical operators (such as AND, OR, etc.) to connect.

For example, the following example retrieves records in the "users" table where the username and email fields are "admin" and the password field is "123":

SELECT username, email, password
FROM users
WHERE username = 'admin' AND password = '123';

However, for some more complex query requirements, just using SELECT, FROM, and WHERE may not be able to meet the requirements. In this case, you need Use advanced query statements such as JOIN statements and subqueries. This will be covered in later chapters.

3. Processing of query results

The result returned by the query statement is usually a table containing multiple rows, each row representing the corresponding record. For PHP application developers, a table entry is an array, and the content of each cell is an element in the array. The processing of query results includes three parts:

1. Execute the database query and obtain the result set.

Use mysqli_query and PDO::query in PHP to execute query statements and return result sets. For example:

$query = "SELECT username, email, password FROM users WHERE username = 'admin' AND password = '123'";
$result = mysqli_query($connection, $query);

2. Process the result set.

Use mysqli_fetch_assoc, PDO::fetch and other functions to extract data from the result set. For example:

while ($row = mysqli_fetch_assoc($result)) {

echo "username: " . $row["username"] . " email: " . $row["email"] . " password: " . $row["password"];

}

3. Release the result set.

In PHP, when you finish using the result set, you should use the mysqli_free_result or PDOStatement::closeCursor function to release the result set to avoid waste of resources. For example:

mysqli_free_result($result);

4. Advanced query technology

1. JOIN

The JOIN statement can combine two Or multiple tables are joined together to produce a new, large table. Common JOIN statements include inner joins, outer joins (left joins and right joins), self-joins, etc.

Inner JOIN: Only records that meet the conditions in the two tables are returned. That is, when connecting two tables, only the same values ​​in both tables will be retrieved.

For example:

SELECT orders.OrderID, customers.CustomerName, orders.OrderDate
FROM orders
INNER JOIN customers
ON orders.CustomerID=customers.CustomerID;

This query will return all orders in the orders table (orders) that are associated with the customers table (customers). The symbol for the inner join is JOIN or INNER JOIN, and the ON keyword specifies the primary key/foreign key used for the link. Key conditions.

2. Subquery

A subquery refers to embedding a SQL query in another SQL statement. Typically, subqueries are used to limit the result set of the main query. Subqueries can be nested within multiple queries, which is called nested queries.

For example:

SELECT AVG(DISTINCT Price) FROM Products
WHERE ProductName IN (SELECT ProductName FROM Orders WHERE CustomerID=1);

This query will return ID The average price of all products ordered by customer 1 is actually to use a subquery list to query all the products purchased by customer 1, and then put an AVG function outside to calculate the average.

3. UNION

The UNION statement combines the result sets of two or more SELECT statements (the results must have the same fields, so sometimes aliases are used to declare them) , remove duplicate rows from the result set. The UNION statement format is as follows:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For example:

SELECT ProductName FROM Products
UNION
SELECT ProductName FROM Suppliers
ORDER BY ProductName;

This query will return all product names, including from suppliers (Suppliers) and products (Products ) query results, remove duplicates and sort by name.

Summary

This article aims to introduce PHP database query statements, focusing on the structure of query statements and processing methods of query results, as well as commonly used advanced query technologies, including JOIN, subquery and UNION statement. Proper use of these advanced query technologies can obtain the required data from the database more efficiently.

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