How can creating a separate table for rental transactions improve data management and tracking in a PHP and MySQL setup?

Creating a separate table for rental transactions in a PHP and MySQL setup can improve data management and tracking by organizing all rental-related data in one place. This allows for easier querying and reporting on rental transactions, as well as simplifying the process of adding, updating, and deleting rental records. 

// Create a separate table for rental transactions
$createTableQuery = "CREATE TABLE rental_transactions (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        rental_date DATE,
                        customer_id INT,
                        movie_id INT,
                        rental_fee DECIMAL(10, 2)
                    )";

// Execute the query to create the table
mysqli_query($conn, $createTableQuery);

// Insert a rental transaction record into the new table
$insertQuery = "INSERT INTO rental_transactions (rental_date, customer_id, movie_id, rental_fee) 
                VALUES ('2022-01-01', 1, 5, 5.99)";
mysqli_query($conn, $insertQuery);

// Retrieve rental transactions from the new table
$selectQuery = "SELECT * FROM rental_transactions";
$result = mysqli_query($conn, $selectQuery);

// Loop through the results and display them
while ($row = mysqli_fetch_assoc($result)) {
    echo "Rental ID: " . $row['id'] . " | Rental Date: " . $row['rental_date'] . " | Customer ID: " . $row['customer_id'] . " | Movie ID: " . $row['movie_id'] . " | Rental Fee: $" . $row['rental_fee'] . "<br>";
}

Keywords

PHP MySQL data management tracking separate table

Related Questions