To establish a One to many relationships of databases in PHP MySQLi, need to perform the following steps;
- Set up your database and tables
- Established the relationship between tables. We can do it by setting a primary key in one table and a foreign key in another table that references the primary key.
- I am sharing with you an example of how you can create tables and SQL Query and you might set up a one-to-many relationship in a MySQL database;
CREATE TABLE customers (
customer_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (customer_id)
);
CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
- Now, in our PHP code, we will use mysqli functions to connect to the database and query the appropriate tables.
- Use a JOIN statement in your SELECT query to combine the data from the two tables based on the foreign key.
- Use a loop (e.g. while or for) to iterate through the results and display the data as desired.
- Here is an example of how you might show a one-to-many relationship in PHP using mysqli with SQL code:
<?php
/* making the database connection */
$mysqli = new mysqli(“localhost”, “root”, “”, “test1”);
/* checking the database connection */
if ($mysqli->connect_errno) {
printf(“Connect failed: %s\n”, $mysqli->connect_error);
exit();
}
$query = “SELECT orders.order_id, customers.name, orders.order_total FROM orders JOIN customers ON orders.customer_id=customers.customer_id”;
$result = $mysqli->query($query);
/* associative array */
while ($row = $result->fetch_assoc()) {
echo $row[“order_id”] . ” – ” . $row[“name”] . ” – ” . $row[“order_total”] . “<br>”;
}
/* free result set */
$result->free();
/* close connection */
$mysqli->close();
?>
