What are the considerations for normalizing database tables when importing data from CSV files in PHP?
When importing data from CSV files into a database using PHP, it is important to consider normalizing the database tables to avoid redundancy and ensure data integrity. This involves breaking down the data into separate tables and establishing relationships between them using foreign keys. This helps in reducing data duplication and improving the overall efficiency of the database.
// Code snippet for normalizing database tables when importing data from CSV files in PHP
// Step 1: Create the necessary tables in the database
// For example, create a 'users' table and a 'roles' table with a foreign key relationship
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
role_id INT,
FOREIGN KEY (role_id) REFERENCES roles(id)
);
CREATE TABLE roles (
id INT AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(50)
);
// Step 2: Parse the CSV file and insert data into the normalized tables
// Assuming the CSV file has columns for 'name' and 'role_name'
$csvFile = 'data.csv';
$handle = fopen($csvFile, 'r');
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
$name = $data[0];
$roleName = $data[1];
// Check if role exists in 'roles' table, insert if not
$roleQuery = "SELECT id FROM roles WHERE role_name = '$roleName'";
$result = mysqli_query($conn, $roleQuery);
if(mysqli_num_rows($result) == 0) {
$insertRoleQuery = "INSERT INTO roles (role_name) VALUES ('$roleName')";
mysqli_query($conn, $insertRoleQuery);
}
// Get the role_id for the user
$roleId = mysqli_fetch_assoc(mysqli_query($conn, $roleQuery))['id'];
// Insert user data into 'users' table
$insertUserQuery = "INSERT INTO users (name, role_id) VALUES ('$name', '$roleId')";
mysqli_query($conn, $insertUserQuery);
}
fclose($handle);
Related questions
-
SQL injection cross-site scripting (XSS) input validation
-
DOMDocument DOMXPath parsing HTML
-
SQL PDO MySQLi