How to use PHP to perform one-to-many correlation query

When using PHP for database operations, it is often necessary to perform one-to-many correlation queries. This kind of query can relate a piece of data in one table to multiple pieces of data in another table. In PHP, a one-to-many relational query usually results in a two-dimensional array, in which each element represents a main table record, and the corresponding values ​​are records in multiple related tables.

This article will introduce how to use PHP to perform one-to-many related queries and get the results of a two-dimensional array.

1. What is a one-to-many relational query?

In the database, if there is a primary and foreign key relationship between two tables, then you can perform a one-to-many association query. In this relationship, one record in one table corresponds to multiple records in another table.

For example, there is a "student table" and a "score table". Each student in the "student table" corresponds to records in multiple "score tables". This relationship can be bound using primary keys and foreign keys, as shown below:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the "Student Table", each record includes a unique identifier ID and a name NAME. In the "score table", each record includes the same unique identifier ID and a corresponding STUDENT_ID, which represents which student the record belongs to; in addition, there is a SCORE field, which represents the student's score.

2. How to perform one-to-many correlation query?

In PHP, you can perform one-to-many related queries by using the JOIN keyword. JOIN is a keyword in SQL that is used to join the same columns in two or more tables together and generate a large table.

In the above example, if you want to associate the student table with the grade table, you can use the following SQL statement:

SELECT student.*, score.score
FROM student
LEFT JOIN score ON student.id = score.student_id;

In this statement, first select all fields in the student table , and then connect the score table and student table through LEFT JOIN and ON keywords. The condition of the connection is that the ID field in the student table is equal to the STUDENT_ID field in the grades table. Finally, select the columns to be displayed through the SELECT statement and get the result of a two-dimensional array.

In PHP's PDO object, you can use the query method to execute the above SQL statement, and use the fetchAll method to get the result of a two-dimensional array:

$db = new PDO('mysql:host=localhost;dbname=mydb', 'myuser', 'mypassword');
$stmt = $db->query('SELECT student.*, score.score
                    FROM student
                    LEFT JOIN score ON student.id = score.student_id');
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

In the above code, first create Get a PDO object, then use the query method to execute the SQL statement, and finally use the fetchAll method to get the result.

3. How to process one-to-many association query results into a two-dimensional array?

When performing a one-to-many association query, the data in multiple tables need to be combined into a two-dimensional array result. Each element of this two-dimensional array represents a record in a main table, and the corresponding values ​​are records in multiple related tables.

The following is a method of processing one-to-many association query results into a two-dimensional array:

$data = array();
foreach ($result as $row) {
    $id = $row['id'];
    if (!isset($data[$id])) {
        $data[$id] = $row;
        $data[$id]['scores'] = array();
    }
    if (!empty($row['score'])) {
        $data[$id]['scores'][] = $row['score'];
    }
}

In the above code, first create an empty array $data, which will be used as the final The result of the two-dimensional array. Then use a foreach loop to traverse each record in the query results and save the primary key ID of each record in the $id variable.

Next, check whether there is already an element with the primary key $id in the $data array. If it does not exist, the element is inserted into the $data array and an empty array named "scores" is created to hold all records related to the primary key. If it already exists, there is no need to insert it and the existing element is used directly.

When traversing records, if a record in the "score table" is found, add the SCORE field of the record to the scores array. Since one main table record corresponds to multiple related table records, the scores array needs to be saved as a two-dimensional array.

When the loop ends, the final two-dimensional array result is stored in the $data array. Each element represents a main table record, and the corresponding values ​​are multiple records in the related table.

4. Example

The following is a complete example that demonstrates how to perform a one-to-many association query and process the result into a two-dimensional array:

$db = new PDO('mysql:host=localhost;dbname=mydb', 'myuser', 'mypassword');
$stmt = $db->query('SELECT student.*, score.score
                    FROM student
                    LEFT JOIN score ON student.id = score.student_id');
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

$data = array();
foreach ($result as $row) {
    $id = $row['id'];
    if (!isset($data[$id])) {
        $data[$id] = $row;
        $data[$id]['scores'] = array();
    }
    if (!empty($row['score'])) {
        $data[$id]['scores'][] = $row['score'];
    }
}

print_r($data);

In the above In the example, first create a PDO object and use the query method to execute the SQL statement. Then use the fetchAll method to get the query results.

Next, use a foreach loop to traverse each record in the query results and process it into a two-dimensional array. Finally, use the print_r function to output the results.

5. Conclusion

This article introduces how to perform one-to-many related queries in PHP and process the results into a two-dimensional array. When processing the results, you need to use a loop to traverse the query results and convert them into a two-dimensional array. After processing all the records, you can get the result of a two-dimensional array, in which each element represents a main table record, and the corresponding values ​​are records in multiple related tables.

The above is the detailed content of How to use PHP to perform one-to-many correlation query. For more information, please follow other related articles on the PHP Chinese website!