In PHP and database systems, foreign key mapping refers to establishing a relationship between two database tables using foreign keys. Foreign keys are a fundamental concept in relational databases, allowing you to link one table’s field (or a set of fields) to the primary key in another table. This relationship ensures referential integrity, meaning that the data in the linked tables stays consistent and accurate.
Step-by-step explanation of foreign key mapping in PHP with an example:
Step 1: Create Database Tables:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
order_details TEXT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
In the orders table, the user_id column is a foreign key that references the id column in the users table. This establishes a relationship between the users and orders tables.
Step 2: PHP Code to Interact with the Database:
Example of Inserting Data:
// Establish a database connection
$mysqli = new mysqli("localhost", "username", "password", "database_name");
// Check the connection
if ($mysqli->connect_error)
die("Connection failed: " . $mysqli->connect_error);
}
// Insert data into users table
$username = "abc";
$sql = "INSERT INTO users (id, username) VALUES (1, '$username')";
$mysqli->query($sql);
// Insert data into orders table with a foreign key reference
$orderDetails = "Sample order details";
$sql = "INSERT INTO orders (id, user_id, order_details) VALUES (1, 1, '$orderDetails')";$mysqli->query($sql);
// Close the connection
$mysqli->close();
In this example, when inserting data into the orders table, the user_id field is set to value 1, referencing the id of the “abc” user in the users table.
Example of Selecting Data:
// Select data from orders table with user information
$sql = "SELECT orders.id, orders.order_details, users.username
FROM orders
JOIN users ON orders.user_id = users.id"
$result = $mysqli->query($sql);
// Fetch data and print
while ($row = $result->fetch_assoc()) {
echo "Order ID: " . $row["id"] . "<br>";
echo "Order Details: " . $row["order_details"] . "<br>";
echo "Username: " . $row["username"] . "<br>"
}
// Close the connection
$mysqli->close();
In this select query, the JOIN statement is used to combine data from both tables based on the foreign key relationship, allowing you to retrieve information from both the users and orders tables in a single query.
Step 3: Understanding the Output:
When we run the code for selecting data, it will display the order details along with the associated username from the users table due to the foreign key mapping. This demonstrates how foreign keys can be used to establish relationships between tables and retrieve meaningful data from multiple tables using PHP.
Order ID: 1
Order Details: Sample order details
Username: abc
StudySection gives an opportunity to beginners and experts in .NET framework to go through StudySection’s .NET Certification Exam and get a .NET certification for enhancement of career in programming. If you have knowledge of the .NET framework then you can get a certificate through an online exam at StudySection.
