Summary: in this tutorial, you will learn how to read data from a CSV file and insert it into a table using a multi-insert statement.
This tutorial begins where the inserting data into a table left off.
We’ll read author data from a CSV file and perform a multi-insert statement to insert author data into the Authors table.
Reading a CSV file
Create a csv.php file and define a function that reads data from the authors.csv file and returns a list of Author objects:
<?php
function readAuthorsFromCSV($filePath)
{
$authors = [];
if (($handle = fopen($filePath, 'r')) !== false) {
// Skip the header line
fgetcsv($handle);
// Read each line of the CSV
while (($data = fgetcsv($handle)) !== false) {
// Create a new Author object
$author = new Author($data[0], $data[1], $data[2]);
// Add to the authors array
$authors[] = $author;
}
fclose($handle);
}
return $authors;
}Code language: PHP (php)How it works.
Step 1. Define a new function named readAuthorsFromCSV that has one parameter $filepath, which is the path to the CSV file:
function readAuthorsFromCSV($filePath)Code language: PHP (php)Step 2. Initialize an empty $authors array to store the Author objects created from parsing data in the CSV file:
$authors = [];Code language: PHP (php)Step 3. Open the CSV file for reading using the fopen() function.
if (($handle = fopen($filePath, 'r')) !== false) {Code language: PHP (php)The fopen() function returns a file handle if reads the file successfully or false otherwise. The if statement checks if the file was opened successfully.
Step 4. Read a line from the CSV file using the fgetcsv() function to skip the header line of the CSV file:
fgetcsv($handle);Code language: PHP (php)Step 5. Read each line from the CSV file as an array containing the CSV fields and assign it to the $data variable. The while statement and condition check will read all the lines:
while (($data = fgetcsv($handle)) !== false) {Code language: PHP (php)Step 6. Create an Author object for each CSV line using values from the $data array:
$author = new Author($data[0], $data[1], $data[2]);Code language: PHP (php)Step 7. Add the Author object to the $authors array:
$authors[] = $author;Code language: PHP (php)Step 8. Close the file handle to free up any resources associated with it:
fclose($handle);Code language: PHP (php)Step 9. Return the array of Author object:
return $authors;Code language: PHP (php)Performing multi-insert statement
Define the insertMany method to insert a list of authors into the Authors table:
public function insertMany($authors)
{
$sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)
VALUES ';
$placeholders = array_fill(0, count($authors), '(?, ?, ?)');
$sql .= implode(', ', $placeholders);
$stmt = $this->conn->prepare($sql);
$data = [];
foreach ($authors as $author) {
$data[] = $author->getFirstName();
$data[] = $author->getLastName();
$data[] = $author->getBirthDate();
}
$stmt->execute($data);
}Code language: PHP (php)How it works.
Step 1. Define a public method named insertMany that takes an array of Authors objects as a parameter:
public function insertMany($authors)Code language: PHP (php)Step 2. Construct a multi-insert statement for inserting multiple rows into the Authors table:
$sql = 'INSERT INTO Authors(FirstName, LastName, BirthDate)
VALUES ';
$placeholders = array_fill(0, count($authors), '(?, ?, ?)');
$sql .= implode(', ', $placeholders);Code language: PHP (php)The statement uses a set of placeholders (?) to prevent SQL injection attacks by separating SQL code from data.
The code constructs an INSERT statement that can insert multiple rows in one operation, improving performance over executing multiple single-row insert statements.
Step 3. Create a prepared statement object to execute the multi-insert statement with the provided data:
$stmt = $this->conn->prepare($sql);Code language: PHP (php)Step 4. Collect data from the array of Author objects into a flat array, matching the order of placeholders in the prepared statement.
$data = [];
foreach ($authors as $author) {
$data[] = $author->getFirstName();
$data[] = $author->getLastName();
$data[] = $author->getBirthDate();
}Code language: PHP (php)Step 5. Execute the prepared statement with data:
$stmt->execute($data);Code language: PHP (php)This statement fills the placeholders with the corresponding values from the $data array and executes the multi-insert statement.
Importing data from the CSV file
Modify the index.php file to read data from the authors.csv file and insert it into the Authors table:
<?php
require_once 'authordb.php';
require_once 'csv.php';
// Connect to the SQL Server
$conn = require_once 'connect.php';
// Read data from CSV file
$authors = readAuthorsFromCSV('authors.csv');
// Insert data into the Authors table
$authorDB = new AuthorDB($conn);
$authorDB->insertMany($authors);Code language: PHP (php)How it works.
Step 1. Include two PHP files authordb.php and csv.php using the require_once function:
require_once 'authordb.php';
require_once 'csv.php';Code language: PHP (php)Step 2. Connect to the SQL Server and assign the Connection object to the $conn variable:
$conn = require_once 'connect.php';Code language: PHP (php)Step 3. Read data from the authors.csv file and return an array of Author objects:
$authors = readAuthorsFromCSV('authors.csv');Code language: PHP (php)Step 4. Create an instance of the AuthorDB class, passing the database connection $conn to its constructor:
$authorDB = new AuthorDB($conn);Code language: PHP (php)Step 5. Call the insertMany() method of the AuthorDB object to insert an array of Author objects into the Authors table:
$authorDB->insertMany($authors);Code language: PHP (php)Running the script
Open the index.php page on your web browser to import data from the authors.csv file into the Authors table.
Verifying inserts
Step 1. Open the Microsoft SQL Server and connect to the BookStore database.
Step 2. Execute the following query to retrieve data from the Authors table:
SELECT * FROM Authors;Code language: PHP (php)Output:
Summary
- Use a multi-insert statement to insert multiple rows into a table from PHP.
