Detailed explanation of how PHP operates MySQL database

Querying two MySQL data tables and calculating them in PHP is a common requirement. This article will introduce how to use PHP to connect to MySQL, query data tables, cross-table queries and calculate data.

1. Connecting to MySQL

Connecting to MySQL in PHP requires the use of mysqli or PDO extension. Here we use the mysqli extension. The sample code is as follows:

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($host, $username, $password, $dbname);

if(!$conn){
    die("Connection failed: " . mysqli_connect_error());
}

2. Query the data table

You need to use the SELECT statement to query the data table. First, we need to select the columns and data tables to query:

$sql = "SELECT id, name, age FROM users";
$result = mysqli_query($conn, $sql);

if(mysqli_num_rows($result) > 0){
    while($row = mysqli_fetch_assoc($result)){
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
}else{
    echo "0 results";
}

In this example, we query the id, name, and age columns in the users data table and output the values ​​of these columns.

3. Cross-table query

Cross-table query requires the use of JOIN statement. Suppose we have two data tables users and orders, and now we need to query the user's order quantity.

First, we need to use the LEFT JOIN keyword to connect the two data tables together:

$sql = "SELECT users.id, users.name, count(orders.id) as orders_count FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id, users.name";
$result = mysqli_query($conn, $sql);

In this example, we use the LEFT JOIN keyword to connect the users and orders data tables together and join the two data tables using the user_id column in the ON clause.

We then use the GROUP BY clause to group by the user's ID and name, and use the COUNT function to calculate the quantity of the user's order.

4. Calculate data

SQL functions are required to calculate data. Here, we introduce some commonly used SQL functions.

The SUM function can calculate the sum of the specified column:

SELECT SUM(price) FROM orders;

The AVG function can calculate the average of the specified column:

SELECT AVG(price) FROM orders;

The COUNT function can calculate the number of rows in the specified column:

SELECT COUNT(*) FROM orders;

The MAX function can select the maximum value of the specified column:

SELECT MAX(price) FROM orders;

The MIN function can select the minimum value of the specified column:

SELECT MIN(price) FROM orders;

5. Complete example

Now, let’s put the above together and build a complete example:

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "test";

$conn = mysqli_connect($host, $username, $password, $dbname);

if(!$conn){
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT users.id, users.name, count(orders.id) as orders_count FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id, users.name";
$result = mysqli_query($conn, $sql);

if(mysqli_num_rows($result) > 0){
    while($row = mysqli_fetch_assoc($result)){
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Orders count: " . $row["orders_count"]. "
";
    }
}else{
    echo "0 results";
}

mysqli_close($conn);
?>

In this complete example, we first connected to MySQL and then used the LEFT JOIN keyword to query users and orders data table, and calculates the order quantity for each user.

Finally, we use the mysqli_close function to close the MySQL connection.

Summary

To query two MySQL data tables and calculate them in PHP, you need to use mysqli or PDO extension to connect to MySQL, use SELECT and JOIN statements to query the data tables, use SUM, AVG, COUNT, The MAX and MIN functions calculate data.

It should be noted that before writing a query statement, we need to clearly understand the relationship between the columns to be queried and the data table, and avoid using complex query statements as much as possible. At the same time, we need to use the mysqli_real_escape_string function to filter user input to avoid SQL injection attacks.

The above is the detailed content of Detailed explanation of how PHP operates MySQL database. For more information, please follow other related articles on the PHP Chinese website!