{"id":4396,"date":"2024-07-30T21:58:35","date_gmt":"2024-07-30T14:58:35","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4396"},"modified":"2024-08-18T10:10:23","modified_gmt":"2024-08-18T03:10:23","slug":"php-sql-server-insert-multiple-rows","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-sql-server-insert-multiple-rows\/","title":{"rendered":"PHP SQL Server: Inserting Multiple Rows"},"content":{"rendered":"\n

Summary<\/strong>: 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.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0inserting data into a table<\/a>\u00a0left off.<\/p>\n\n\n\n

We’ll read author data from a CSV file and perform a multi-insert statement to insert author data into the Authors<\/code> table.<\/p>\n\n\n\n

Reading a CSV file #<\/a><\/h2>\n\n\n\n

Create a csv.php<\/code> file and define a function that reads data from the authors.csv<\/code> file and returns a list of Author<\/code> objects:<\/p>\n\n\n

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