How can PHP retrieve the ID from a color table and insert it into a junction table along with additional data, such as a car?

To retrieve the ID from a color table in PHP and insert it into a junction table along with additional data, such as a car, you can use SQL queries to fetch the color ID based on the color selected, and then insert the color ID and car ID into the junction table. You can achieve this by first querying the color table to get the ID corresponding to the selected color, and then inserting the retrieved color ID and the car ID into the junction table. 

// Assume $selectedColor contains the selected color and $carID contains the car ID

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

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

// Retrieve the color ID from the color table
$colorQuery = "SELECT id FROM color_table WHERE color = '$selectedColor'";
$colorResult = $connection->query($colorQuery);
$colorRow = $colorResult->fetch_assoc();
$colorID = $colorRow['id'];

// Insert the color ID and car ID into the junction table
$junctionQuery = "INSERT INTO junction_table (color_id, car_id) VALUES ('$colorID', '$carID')";
if ($connection->query($junctionQuery) === TRUE) {
    echo "Record inserted successfully";
} else {
    echo "Error inserting record: " . $connection->error;
}

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

Keywords

PHP database SQL junction table foreign key

Related Questions