PHPackages                             jotagp/insert-multiple - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. jotagp/insert-multiple

ActiveLibrary[Database & ORM](/categories/database)

jotagp/insert-multiple
======================

A simple PHP package, to insert multiple records into a MySQL/MariaDB database.

v1.2(3y ago)4901[2 issues](https://github.com/jotagp/insert-multiple/issues)[1 PRs](https://github.com/jotagp/insert-multiple/pulls)MITPHP

Since Feb 9Pushed 2y ago2 watchersCompare

[ Source](https://github.com/jotagp/insert-multiple)[ Packagist](https://packagist.org/packages/jotagp/insert-multiple)[ RSS](/packages/jotagp-insert-multiple/feed)WikiDiscussions main Synced today

READMEChangelog (6)DependenciesVersions (8)Used By (0)

INSERT-MULTIPLE
===============

[](#insert-multiple)

Sumary
------

[](#sumary)

1. Introduction
    1.1 What is
    1.2 Why
    1.3 Licence
2. Get Started
    2.1 Dependences
    2.2 Instalation
    2.3 Usage
    2.4 Methods
    2.5 Exemple
3. Enjoy

1. Introduction
---------------

[](#1-introduction)

**1.1 What is**
This is a simple PHP package to insert and update multiple data into a MySQL/MariaDB database.

**1.2 Why**
This package was created with the aim of reducing data insertion time. To understand the magnitude of the problem, let's assume that we want to go through the items in a list and insert them into a database.

```
// example with tradicional insert
foreach ($list as $item) {
	$insert = "INSERT INTO `table1`(`numbers`, `description`) VALUES ({$item['number']}, '{$item['description']}');
	$connection->query($insert) or die ($connection->error);
}
```

The example above works. However, when working with a large volume of data, traditional insertion is not a viable option. This is because it inserts a single record at a time. So, if you have 100,000 records, there will be 100,000 insertions and, consequently, 100,000 trips to the hard disk to persist this data, and this task will take a long time (of course, it depends on the size of your data). One "option" is to control the transaction from the database manually, something like:

```
// example with tradicional insert and transaction control
$connection->begin_transaction();
foreach ($list as $item) {
	$insert = "INSERT INTO `table1`(`numbers`, `description`) VALUES ({$item['number']}, '{$item['description']}');
	$connection->query($insert) or die ($connection->error);
}
$connection->commit();
```

However, this approach still does not definitively solve our problem, as the time gain is not significant. What to do then? Simple! make a multiple insert:

```
// example with manually multiple insert
$connection->begin_transaction();
$insert = "INSERT INTO `table1`(`numbers`, `description`) VALUES ";
foreach ($list as $item) {
	$values[] = "({$item['number']}, '{$item['description']}')"; // concat new values
}
$insert .= implode(", ", $values); // join values separete by comma
$connection->query($insert) or die ($connection->error);
$connection->commit();
```

However (there's always a however, right?), there is a transaction limit allowed by the bank, and this limit is easily reached when a very extensive query is set up. And that, my friends, is where this package comes in. It will partition your values ​​into N Multiple slots, according to your bank's capacity:

```
// example with package
$insert = new insert_multiple($connection, "table1");
foreach ($list as $item) {
	$insert->push($item); // concat new values
}
$insert->exec(); // run inserts
```

And it's that simple.

**1.3 License**
This code is licensed under the [MIT license](https://opensource.org/licenses/MIT).

2. Get Start
------------

[](#2-get-start)

**2.1 Dependences**
The library depends only on:

- [PHP](https://www.php.net/)
- [Composer](https://getcomposer.org/)
- [MariaDB](https://mariadb.org/) or [MySQL](https://www.mysql.com/)

**2.2 Instalation**
Run the following command:

```
composer require jotagp/insert-multiple
```

**2.3 Usage**
Include the dependences in your PHP project:

```
require 'vendor/autoload.php';
use jotagp\insert_multiple\insert_multiple;
$connection = new mysqli('host', 'user', 'pass', 'database');
$insert_multiple = new insert_multiple($connection, 'table-name');
```

**2.4 Methods**
There are three possible methods:

`push($associative_array)`
This method works to include new values ​​in the insert. Note that the expected argument is an associative array, where the index of this array must always refer to the attribute of the table in question. Attributes that you do not specify will be included with their respective default values.

`exec()`
This method partitions your insert into N multiple inserts, then run, always taking into account the amount allowed in a transaction by your database instance (max\_allowed\_package) . That is, in an insert of 100 thousand records, hypothetically speaking the function will create 10 multiple inserts, each with 10 thousand records.

`config($associative_array)`
This method allows you to edit some behavior of the object. Possible configurations (so far) are:

- update\_if\_exists: updates a record if a corresponding key already exists.
    - fields\_to\_update: inside update\_if\_exists, you can specify which fields should be updated.
    - concat\_new\_values: allows concatenating new values ​​to existing ones.
    - skip\_if\_already\_exists: preserves the value that already exists, ignoring the new one.
    - skip\_if\_new\_is\_empty: preserves the value that already exists, in case the new one is empty or null.
- insert\_multiple: if false, insert records one row per time.

**2.5 Example**
Inserting 100 thousand random data into database. Consider the following data structure:

```
DROP DATABASE IF EXISTS `0temp`;
CREATE DATABASE `0temp`;
USE `0temp`;

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `number` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`number`)
)
```

Then:

```