What are some best practices for handling and formatting data from text files before importing it into a MySQL database using PHP?

When handling and formatting data from text files before importing it into a MySQL database using PHP, it is important to properly sanitize and validate the data to prevent SQL injection attacks and ensure data integrity. One common approach is to read the text file line by line, parse the data, and then insert it into the database using prepared statements to prevent SQL injection. 

<?php

// Open the text file for reading
$file = fopen("data.txt", "r");

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

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

// Read the file line by line
while (!feof($file)) {
    $line = fgets($file);

    // Parse the data and sanitize it
    $data = explode(",", $line);
    $name = $mysqli->real_escape_string($data[0]);
    $age = $mysqli->real_escape_string($data[1]);

    // Insert the data into the database using prepared statements
    $stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?, ?)");
    $stmt->bind_param("si", $name, $age);
    $stmt->execute();
}

// Close the file and database connection
fclose($file);
$stmt->close();
$mysqli->close();

?>

Keywords

file handling data formatting MySQL database PHP functions error handling

Related Questions