Importing data from a CSV file into a MySQL database is a common requirement in web development, especially when dealing with data from spreadsheets or exporting tools. In this blog post, we’ll walk through a PHP script that automates the process of importing CSV data into a dynamically created MySQL table.
Overview
This tutorial demonstrates how to:
- Upload a CSV file using a PHP form.
- Read the CSV file and determine the maximum length of data for each column.
- Create a new table in a MySQL database with dynamically determined column lengths.
- Insert the CSV data into the newly created table.
- Provide feedback on the success of the operation.
Prerequisites
Before diving into the code, ensure you have the following setup:
- A MySQL database and user account with permissions to create tables and insert data.
- A PHP environment, such as XAMPP, WAMP, or a server running PHP and MySQL.
- Basic knowledge of PHP and MySQL.
The PHP Script
Here’s the complete PHP script that performs the CSV import:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
<?php // MySQL database connection details $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "your_database"; // Connect to MySQL database $conn = new mysqli($servername, $username, $password, $dbname); // Check the connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } function generateRandomTableName($length = 32) { return substr(str_shuffle(str_repeat('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', $length)), 0, $length); } // Function to sanitize field names function sanitizeFieldName($fieldName) { // Remove or replace characters that are not allowed in SQL identifiers $fieldName = preg_replace('/[^a-zA-Z0-9_]/', '_', $fieldName); // Ensure the field name is not empty and does not start with a number if (empty($fieldName) || is_numeric($fieldName[0])) { $fieldName = 'field_' . $fieldName; } return $fieldName; } $tableName = ''; $message = ''; if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['csvFile'])) { $file = $_FILES['csvFile']['tmp_name']; if (($handle = fopen($file, 'r')) !== FALSE) { // Get the field names from the first row $fieldNames = fgetcsv($handle, 1000, ","); // Sanitize the field names $sanitizedFieldNames = array_map('sanitizeFieldName', $fieldNames); // Initialize an array to store the maximum length of data for each column $maxLengths = array_fill(0, count($sanitizedFieldNames), 0); // Read through the CSV to determine the maximum length of data for each column while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { for ($i = 0; $i < count($data); $i++) { $maxLengths[$i] = max($maxLengths[$i], strlen($data[$i])); } } // Close and reopen the file to start reading from the beginning again fclose($handle); $handle = fopen($file, 'r'); // Skip the first row (header) fgetcsv($handle, 1000, ","); // Generate a random table name $tableName = generateRandomTableName(); // Create SQL to create a new table with dynamic column lengths $createTableSQL = "CREATE TABLE `$tableName` (id INT AUTO_INCREMENT PRIMARY KEY, "; for ($i = 0; $i < count($sanitizedFieldNames); $i++) { $columnLength = $maxLengths[$i] > 0 ? $maxLengths[$i] : 1; // Ensure at least 1 character $createTableSQL .= "`{$sanitizedFieldNames[$i]}` VARCHAR($columnLength), "; } $createTableSQL = rtrim($createTableSQL, ', ') . ')'; // Execute the create table query if ($conn->query($createTableSQL) === TRUE) { $message = "Table '$tableName' created successfully. "; // Prepare SQL for data insertion $values = []; // Read the rest of the CSV file and insert the data while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { // Only process rows with the correct number of columns if (count($data) == count($sanitizedFieldNames)) { $rowValues = []; foreach ($data as $value) { $rowValues[] = "'" . $conn->real_escape_string($value) . "'"; } $values[] = "(" . implode(", ", $rowValues) . ")"; } } if (!empty($values)) { $insertSQL = "INSERT INTO `$tableName` (" . implode(", ", array_map(function($field) { return "`$field`"; }, $sanitizedFieldNames)) . ") VALUES "; $insertSQL .= implode(", ", $values); if ($conn->query($insertSQL) === TRUE) { $message .= "Data imported successfully."; } else { $message .= "Error importing data: " . $conn->error; } } } else { $message = "Error creating table: " . $conn->error; } fclose($handle); } } $conn->close(); echo '<!DOCTYPE html>'; echo '<html lang="en">'; echo '<head>'; echo '<meta charset="UTF-8">'; echo '<title>Upload CSV</title>'; echo '</head>'; echo '<body>'; echo '<form enctype="multipart/form-data" action="" method="POST">'; echo '<input type="file" name="csvFile" accept=".csv">'; echo '<button type="submit">Upload CSV</button>'; echo '</form>'; if (!empty($tableName)) { echo '<p>Table Name: ' . htmlspecialchars($tableName) . '</p>'; } if (!empty($message)) { echo '<p>' . htmlspecialchars($message) . '</p>'; } echo '</body>'; echo '</html>'; ?> |
Code Explanation
Let’s break down the script and understand how it works step by step:
Step 1: Database Connection
The script begins by establishing a connection to the MySQL database using the mysqli extension:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "your_database"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } |
Replace your_username, your_password, and your_database with your actual database credentials.
Step 2: File Upload Form
The HTML form allows users to upload a CSV file:
|
1 2 3 4 5 6 |
echo '<form enctype="multipart/form-data" action="" method="POST">'; echo '<input type="file" name="csvFile" accept=".csv">'; echo '<button type="submit">Upload CSV</button>'; echo '</form>'; |
Step 3: Reading and Analyzing the CSV File
When the form is submitted, the script reads the CSV file and determines the maximum length of data for each column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
if (($handle = fopen($file, 'r')) !== FALSE) { // Get the field names from the first row $fieldNames = fgetcsv($handle, 1000, ","); // Sanitize the field names $sanitizedFieldNames = array_map('sanitizeFieldName', $fieldNames); // Initialize an array to store the maximum length of data for each column $maxLengths = array_fill(0, count($sanitizedFieldNames), 0); // Read through the CSV to determine the maximum length of data for each column while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { for ($i = 0; $i < count($data); $i++) { $maxLengths[$i] = max($maxLengths[$i], strlen($data[$i])); } } // Close and reopen the file to start reading from the beginning again fclose($handle); $handle = fopen($file, 'r'); // Skip the first row (header) fgetcsv($handle, 1000, ","); } |
Step 4: Creating the MySQL Table
Using the maximum lengths determined, the script creates a new table with appropriate column sizes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$tableName = generateRandomTableName(); $createTableSQL = "CREATE TABLE `$tableName` (id INT AUTO_INCREMENT PRIMARY KEY, "; for ($i = 0; $i < count($sanitizedFieldNames); $i++) { $columnLength = $maxLengths[$i] > 0 ? $maxLengths[$i] : 1; // Ensure at least 1 character $createTableSQL .= "`{$sanitizedFieldNames[$i]}` VARCHAR($columnLength), "; } $createTableSQL = rtrim($createTableSQL, ', ') . ')'; if ($conn->query($createTableSQL) === TRUE) { $message = "Table '$tableName' created successfully. "; } |
The table name is generated randomly to avoid collisions with existing tables.
Step 5: Inserting CSV Data into the Table
After creating the table, the script inserts the CSV data into the new table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$values = []; while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { // Only process rows with the correct number of columns if (count($data) == count($sanitizedFieldNames)) { $rowValues = []; foreach ($data as $value) { $rowValues[] = "'" . $conn->real_escape_string($value) . "'"; } $values[] = "(" . implode(", ", $rowValues) . ")"; } } if (!empty($values)) { $insertSQL = "INSERT INTO `$tableName` (" . implode(", ", array_map(function($field) { return "`$field`"; }, $sanitizedFieldNames)) . ") VALUES "; $insertSQL .= implode(", ", $values); if ($conn->query($insertSQL) === TRUE) { $message .= "Data imported successfully."; } else { $message .= "Error importing data: " . $conn->error; } } |
Step 6: Displaying the Result
Finally, the script outputs the result of the operation, including the table name and success message:
|
1 2 3 4 5 6 7 8 |
if (!empty($tableName)) { echo '<p>Table Name: ' . htmlspecialchars($tableName) . '</p>'; } if (!empty($message)) { echo '<p>' . htmlspecialchars($message) . '</p>'; } |
Conclusion
This PHP script efficiently imports CSV data into a MySQL database by dynamically determining the appropriate column lengths. This approach ensures optimal use of database resources while providing flexibility to handle varying data sizes.
Feel free to customize the script to suit your specific needs, such as adding error handling or supporting additional CSV formats. By understanding each step, you can extend and adapt the script to integrate with larger systems or automate regular data imports.
With this solution, you can easily manage data from CSV files, providing a seamless bridge between various data sources and your MySQL database.