Inserting Array values into MySQL using PHP

by | Apr 2, 2022 | PHP

This is a short tutorial on how to insert a PHP Array into a MySQL database. You can not array values directly insert into the MySQL table. It is not supported. We have Four methods for PHP array value insert into MySQL database.

MySQL database

First We have to create products table in codeat21 database.

  • Database Name – codeat21
  • Table Name – products
CREATE TABLE `products` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `price` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  `pro_details` longtext COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Option 1) Repetitive Insert query on each row

The following code shows that how can php array value insert command for each row. 

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$user_data  = array(
    "0" => array("Liam", "2700", "yes"),
    "1" => array("Noah", "6000", "no"),
    "2" => array("Oliver", "17000", "yes")
);
if(is_array($user_data)){
    foreach ($user_data as $row) {
        $val1 = mysqli_real_escape_string($db_conn, $row[0]);
        $val2 = mysqli_real_escape_string($db_conn, $row[1]);
        $val3 = mysqli_real_escape_string($db_conn, $row[2]);
        $query ="INSERT INTO products (name, price, status) VALUES ( '".$val1."','".$val2."','".$val3."' )";	
        mysqli_query($db_conn, $query);	
    }
}
?>

Output

Option 2) Single Insert query

The following code shows that how can php array value Single Insert Command for all row. 

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$user_data  = array(
    "0" => array("Liam", "2700", "yes"),
    "1" => array("Noah", "6000", "no"),
    "2" => array("Oliver", "17000", "yes")
);
if(is_array($user_data)){
    $DataArr = array();
    foreach($user_data as $row){
        $val1 = mysqli_real_escape_string($db_conn, $row[0]);
        $val2 = mysqli_real_escape_string($db_conn, $row[1]);
        $val3 = mysqli_real_escape_string($db_conn, $row[2]);
        $DataArr[] = "('$val1', '$val2', '$val3')";
    }
    $sql = "INSERT INTO products (name, price, status) values ";
    $sql .= implode(',', $DataArr);
    mysqli_query($db_conn, $sql);
}
?>

Output

Option 3) serialized string

The serialize() function converts an array. 

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$user_data  = array(
    "0" => array("Liam", "2700", "yes"),
    "1" => array("Noah", "6000", "no"),
    "2" => array("Oliver", "17000", "yes")
);
$serialized_data  = serialize($user_data);
$sql = "insert into products (pro_details) value ('$serialized_data')";
mysqli_query($db_conn, $sql);
?>

Output

a:3:{i:0;a:3:{i:0;s:4:”Liam”;i:1;s:4:”2700″;i:2;s:3:”yes”;}i:1;a:3:{i:0;s:4:”Noah”;i:1;s:4:”6000″;i:2;s:2:”no”;}i:2;a:3:{i:0;s:6:”Oliver”;i:1;s:5:”17000″;i:2;s:3:”yes”;}}

Fetch data from the mysql database 

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$sql = mysqli_query($db_conn,"select pro_details from products");
while($row = mysqli_fetch_array($sql))
{
   $array = unserialize($row["pro_details"]);
   print_r($array);
}
?>

Output

Array ( [0] => Array ( [0] => Liam [1] => 2700 [2] => yes ) [1] => Array ( [0] => Noah [1] => 6000 [2] => no ) [2] => Array ( [0] => Oliver [1] => 17000 [2] => yes ) )

Option 4) JSON string

JSON means JavaScript Object Notation. It makes working with JSON text to JavaScript objects.
json_encode() – converts arrays into JSON format.

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$user_data  = array(
    "0" => array("Liam", "2700", "yes"),
    "1" => array("Noah", "6000", "no"),
    "2" => array("Oliver", "17000", "yes")
);
$json_data = json_encode($user_data);
$sql = "insert into products (pro_details) value ('$json_data')";
mysqli_query($db_conn, $sql);
?>

Output

[[“Liam”,”2700″,”yes”],[“Noah”,”6000″,”no”],[“Oliver”,”17000″,”yes”]]

Fetch JSON data from the mysql database 

 Index.js 
<?php 
$db_conn = mysqli_connect("localhost", "root", "", "codeat21");
$sql = mysqli_query($db_conn,"select pro_details from products");
while($row = mysqli_fetch_array($sql))
{
   $array = json_decode($row["pro_details"],true);
   print_r($array);
}
?>

Output

Array ( [0] => Array ( [0] => Liam [1] => 2700 [2] => yes ) [1] => Array ( [0] => Noah [1] => 6000 [2] => no ) [2] => Array ( [0] => Oliver [1] => 17000 [2] => yes ) )

Related keywords

  • How to insert an array into a MySQL database?
  • Convert serialized data to array PHP
  • Serialize and unserialize in PHP
  • How to insert a 2 D array into a database?
  • Saving arrays
  • insert php array into mysql
  • An awesome way to store arrays to SQL database in PHP
  • Can you insert an array into MySQL without splitting?
  • How to pass array in INSERT query in PHP
  • Build insert query from array MySQL and PHP
  • How do I pass an array to a stored procedure in MySQL?
  • How can I store multiple values in one cell in MySQL?
  • How to insert PHP array into mysql table?
  • Passing array into sql
  • Can I insert array in MySQL?
  • Insert array into MySQL database with PHP
  • PHP
  • Mysqli database
  • for loop using insert query