How can prepared statements and bindParam be utilized in PHP to customize the insertion of data from a CSV file into a MySQL database?

When inserting data from a CSV file into a MySQL database in PHP, using prepared statements and bindParam can help prevent SQL injection attacks and improve performance. By preparing the SQL statement once and binding parameters dynamically for each row of data, you can efficiently insert data without the need to escape special characters manually. 

<?php
$csvFile = 'data.csv';

$pdo = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$handle = fopen($csvFile, 'r');
if ($handle !== false) {
    $stmt = $pdo->prepare("INSERT INTO table_name (column1, column2) VALUES (:value1, :value2)");

    while (($data = fgetcsv($handle)) !== false) {
        $stmt->bindParam(':value1', $data[0]);
        $stmt->bindParam(':value2', $data[1]);
        $stmt->execute();
    }

    fclose($handle);
}
?>

Keywords

prepared statements bindParam PHP MySQL CSV file

Related Questions