How can PHP developers effectively handle M:N relationships between tables in MySQL databases?

When dealing with M:N relationships between tables in MySQL databases, PHP developers can effectively handle this by creating a separate table that serves as a junction table to link the two entities together. This table will typically contain foreign keys referencing the primary keys of the related tables, allowing for a many-to-many relationship to be established and maintained. 

// Example of creating a junction table for M:N relationship in MySQL database

// Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Create the junction table
$query = "CREATE TABLE IF NOT EXISTS user_roles (
            user_id INT,
            role_id INT,
            PRIMARY KEY(user_id, role_id),
            FOREIGN KEY(user_id) REFERENCES users(id),
            FOREIGN KEY(role_id) REFERENCES roles(id)
          )";
$mysqli->query($query);

// Insert data into the junction table
$user_id = 1;
$role_id = 2;
$query = "INSERT INTO user_roles (user_id, role_id) VALUES ($user_id, $role_id)";
$mysqli->query($query);

// Retrieve data from the junction table
$query = "SELECT * FROM user_roles";
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
    echo "User ID: " . $row['user_id'] . ", Role ID: " . $row['role_id'] . "<br>";
}

// Close the database connection
$mysqli->close();

Keywords

PHP MySQL M:N relationships database design normalization

Related Questions