Insert Data Array Into MySQL Table Using PHP

This lets you insert data from a PHP array into your MySQL database table. It will insert multiple rows into the table, where each row will take values from one array element.

Before inserting, you must prepare your data into an array consisting of records that you want to insert into the database. This should be a two-dimensional array, in which each element of the array is an array of field values for one database record. For example, if your table has 10 fields (fields are columns, records are rows):

/* Example of PHP array of data to INSERT into a MySQL table */
$data = array(
	array(	// record 1, or row 1
		"Value for field 1",
		"Value for field 2",
		"Value for field 3",
		"Value for field 4",
		"Value for field 5",
		"Value for field 6",
		"Value for field 7",
		"Value for field 8",
		"Value for field 9",
		"Value for field 10"
	),
	array(	// record 2, or row 2
		"Value for field 1",
		"Value for field 2",
		"Value for field 3",
		"Value for field 4",
		"Value for field 5",
		"Value for field 6",
		"Value for field 7",
		"Value for field 8",
		"Value for field 9",
		"Value for field 10"
	),
	array(	// record 3, or row 3
		"Value for field 1",
		"Value for field 2",
		"Value for field 3",
		"Value for field 4",
		"Value for field 5",
		"Value for field 6",
		"Value for field 7",
		"Value for field 8",
		"Value for field 9",
		"Value for field 10"
	),   
       
     // etc...

);

See Harvest Data From A Text File To Insert Into MySQL of 2 Columns or Harvest Data From Multiple Text Files To Insert Into MySQL for help on harvesting your data.

If you already have your array of data, like the example above, then let’s begin.

Here is the PHP script that will insert values from your array into your database table. This is a prepared mysqli INSERT query, with a foreach loop.

See the notes below for what to edit in the script.

<?php


/*** @todo Set your data to the $data variable above this line.
*    See the examle in the code block, above.
*
************************************************************/

$ready = '';
$fail = '';

$data = array_filter($data);

// Make database connection

$con = new mysqli("HOST","USERNAME","PASSWORD","DATABASE_NAME");

// Check connection
if ($con->connect_errno) {
    echo 'Connect failed: ' . $con->connect_error;
    exit();
}

$stmt = $con->prepare(
	"INSERT INTO table (
	field1,
	field2,
	field3,
	field4,
	field5,
	field6,
	field7,
	field8,
	field9,
	field10
	)
	VALUES (?,?,?,?,?,?,?,?,?,?)" );

// Check if prepare() failed.
if ( false === $stmt ) {
	echo 'prepare() failed: ' . htmlspecialchars($stmt->error);
	trigger_error($con->error, E_USER_ERROR);
}

$con->query("START TRANSACTION");

foreach ($data as $row) {
  
	// Bind parameters. Types: s = string, i = integer, d = double,  b = blob
	$bind = $stmt->bind_param('ssssssssss',
		$row[0],
		$row[1],
		$row[2],
		$row[3],
		$row[4],
		$row[5],
		$row[6],
		$row[7],
		$row[8],
		$row[9]
	);
		
	// Check if bind_param() failed.
	if ( false === $bind ) {
		echo 'bind_param() failed: ' . htmlspecialchars($stmt->error);
	}

	$exec = $stmt->execute();

	// Check if execute() failed.
	if ( false === $exec ) {
		$fail .= sprintf("%s will not be inserted because execute() failed: %s<br />", $row[0], htmlspecialchars($stmt->error));
	} else {
		$ready .= sprintf("%s will be inserted in database.<br />", $row[0]);
	}

}

// Close the prepared statement
$stmt->close();

if ( ! empty( $ready ) )
	echo $ready;
if ( ! empty( $fail ) )
	echo $fail;

$commit = $con->query("COMMIT");

if ( false === $commit ) {
    echo "Transaction commit failed<br />";
}

echo "<br />End of script.<br />";

// Close the database connection
$con->close();
?>

Edit the script above

1. Set your data to the $data variable before line 4. It should look like the example in the first code block at the top of the page.
2. Add your own database host, database username, password, and database name on line 16.
3. Replace ‘table‘ with your own table name on line 25.
4. Replace ‘field1, field2, field3, etc‘ on lines 26–35 with the names of your own database columns which you are entering data into. It’s important to list the column names in order so that they match up with their corresponding values.

   Add to, or take away, as many fields on lines 26–35 as needed, depending on how many columns you are working with. This example enters data into 10 columns. The number of fields on lines 26–35 should be the same as the number of columns in your table. For example, if you only have 5 columns in your table, then you should delete lines 31–35 (fields 6 through 10).

5. Also, the number of question marks (?) on line 37 must match the the number of fields on lines 26–35. For example, if you only have 5 columns in your table, then you should only have 5 question marks on line 37. Since the example on the page has 10 fields, there are currently 10 question marks on line 37.
6. Also, the number of s letters on line 50 must match the the number of fields on lines 26–35, and must match the number of question marks on line 37. For example, if you only have 5 columns in your table, then you should only have 5 s letters on line 50. Since the example on the page has 10 fields, there are currently 10 s letters on line 50 that look like this: ssssssssss
7. The number of $row elements on lines 51–60 must also match the number of columns you are entering into. So if your table has 5 columns, then you should only have 5 $row elements, like this:

   $row[0],
   $row[1],
   $row[2],
   $row[3],
   $row[4]
   

   If your table only has 2 columns, then leave only 2 $row elements:

   $row[0],
   $row[1]
   

   Notice that I remove the comma from the final row element. Remember, the number of row elements here must also match the number of s letters on line 50, the number of question marks on line 37, and the number of fields on lines 26–35.

8. By default, this script assumes that all of your data values are strings. If you have a field who’s value is a numerical value (decimals are okay), you can change one of the s letters on line 50 to a d. Or, if a value type is “integer” (no decimals), then change the s letter to an i. It’s very important that you change the correct letter. The first s corresponds to the first field, the second s corresponds to the second field, etc. Only change the letter for the field which takes a numerical or integer value.

Done editing the script.

Final Steps

1. Save the script as a .php file. For example, you can name it my-insert-script.php
2. Upload the PHP file to your server.
3. Run the script by navigating on your browser the PHP file.
4. After you run this script once, you should remove it from the server since it will re-insert fields if you refresh the page, or if you navigate to this page again. This script does not create a unique key field, so it will re-insert all of you data again, creating duplicate fields, if you run this script again. So, only run it once, then remove the file from your server.