Insert PHP Array into MySQL Table

In This post, we will learn how to insert a PHP array into MySQL table. This is a very common problem when we have multiple rows of data that we want to insert into MySQL as a row. We can do this very easily using PHP to insert an array into MySQL.

This is a beginner tutorial on PHP and MySQL, You’ll learn here basic MySQL operations with PHP.

The following code will help to create a PHP function to insert array PHP data into MySQL.For Ex. We have the following PHP array.

$records = array(
    "0" => array("Parvez", "PHP", "12"),
    "1" => array("Devid", "Java", "34"),
    "2" => array("Ajay", "Nodejs", "22")
);

I want to insert the above PHP array into the MySQL database table. We have two options to insert a PHP array into MySQL using PHP.

We will follow the below steps to insert arrays of data into MySQL –

1. Using the Repetitive Insert command on each row
2. Using the Single Insert command by concatenating all array values into array

Also Checkout other dynamic MySQL query tutorials,

• Merge Two Array or Multiple Array in PHP
• How To Convert XML To Associative Array in PHP
• Remove Duplicates From Multidimensional Array
• How To Convert XSD into Array Using PHP
• Insert PHP Array into MySQL Table
• Merge Two Array or Multiple Array in PHP

Option 1: PHP Insert Array into MySQL Using Repetitive Insert Command

Here we will iterate on each row of data and execute separate insert commands for each row.

if(is_array($records)){
    foreach ($records as $row) {
        $fieldVal1 = mysqli_real_escape_string($conn, $row[0]);
        $fieldVal2 = mysqli_real_escape_string($conn, $row[1]);
        $fieldVal3 = mysqli_real_escape_string($conn, $row[2]);

        $query ="INSERT INTO programming_lang (field1, field2, field3) VALUES ( '". $fieldVal1."','".$fieldVal2."','".$fieldVal3."' )";
        mysqli_query($conn, $query);
    }
}

I am assuming – You have created a table and connection with MySQL, $conn is connection object, You need to replace 'programming_lang' with your table name and field.* with your column name.I haven’t taken any id for a row, I am assuming you have auto-incremented 'id' column in your table.

Option 2: PHP Insert Array into database table Using Single Insert Command

In this option, we will parse all row data and store it into a PHP array, Next step is – we will implode all row data and prepare an insert SQL command to insert all PHP array data into the MySQL table.

if(is_array($records)){
    $DataArr = array();
    foreach($records as $row){
        $fieldVal1 = mysqli_real_escape_string($conn, $row[0]);
        $fieldVal2 = mysqli_real_escape_string($conn, $row[1]);
        $fieldVal3 = mysqli_real_escape_string($conn, $row[2]);

        $DataArr[] = "('$fieldVal1', '$fieldVal2', '$fieldVal3')";
    }

    $sql = "INSERT INTO programming_lang (field1, field2, field3) values ";
    $sql .= implode(',', $DataArr);

    mysqli_query($conn, $query); 
}

PHP also provides serialize() function to insert a PHP array as a string into MySQL. You can store all PHP array into MySQL table as a string using serialize() and revert back php array using unserialize().

Conclusion

In This Post, We have learned how to insert PHP Array into the MySQL database table using PHP. There are two options to insert a PHP array into the MySQL table. We can also store PHP array into MySQL table using php serialize() function.