How can PHP be used to dynamically generate an INSERT query based on the columns present in a CSV file?

When dealing with a CSV file that has varying columns, we can dynamically generate an INSERT query in PHP by reading the first row of the CSV file to get the column names. We can then use this information to construct the INSERT query with placeholders for the values. Finally, we can loop through the remaining rows of the CSV file, binding the values to the placeholders and executing the query for each row. 

<?php

// Read the CSV file
$csvFile = 'data.csv';
$csv = array_map('str_getcsv', file($csvFile));
$columns = $csv[0]; // Get the column names from the first row

// Construct the INSERT query
$columnsString = implode(', ', $columns);
$valuesString = rtrim(str_repeat('?, ', count($columns)), ', ');
$query = "INSERT INTO table_name ($columnsString) VALUES ($valuesString)";

// Prepare the query
$stmt = $pdo->prepare($query);

// Bind and execute the query for each row
for ($i = 1; $i < count($csv); $i++) {
    $row = $csv[$i];
    $stmt->execute($row);
}

?>

Keywords

PHP INSERT query CSV file dynamic generation columns

Related Questions