What is an Array?

An array is a datatype or data structure or in layman terms a special variable that allows storing one or more values in a single variable e.g. — group of names, group of emails, group of products etc.

When building any kind of application, the chances that you would not manipulate an array is “almost” less than zero. Well maybe not less than zero but you get the point.

Arrays have such significance in app development that understanding the different ways to manipulate, store and retrieve them in the database becomes very crucial and an invaluable skill for developers.

In this article, I will show four options or ways to store arrays to SQL databases using PHP but these methods can be applied in other programming languages as well.

OPTION 1

Let’s create a table for use with OPTION 1 TO 3

CREATE TABLE `contents_arr` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`arr_serialize1` varchar(255) NOT NULL,
`arr_serialize2` varchar(255) NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Define two arrays — $names_arr, and $users_arr.
$names_arr Array should be Indexed type Array and
$users_arr Array should be an Associative Array.

Serialize Syntax –

serialize([Array]);

Pass the array in the serialize() method and pass the serialized values in the INSERT query.

Unserialize Syntax –

unserialize([Serialized value]);

Fetch records and pass the serialized value in the unserialize() method to convert it to Array format.

Completed Code

<?php
include “config.php”;
// Indexed Array
$names_arr = array(“Toni Abbah”,”Anastacia Mast”,”Soji Igbonna”);
// Associative Array
$users_arr[] = array(“username”=>”Torah”,”name”=>”Toni Abbah”);
$users_arr[] = array(“username”=>”Anamast”,”name”=>”Anastacia Mast”);
$users_arr[] = array(“username”=>”soggy”,”name”=>”Soji Igbonna”);
// Serialize the Array
$names_str = serialize($names_arr);
$users_str = serialize($users_arr);
// Insert record
$sql = “INSERT INTO contents_arr(arr_serialize1,arr_serialize2) VALUES(‘“.$names_str.”’,’”.$users_str.”’)”;
mysqli_query($con,$sql);
// Read record
$sql = mysqli_query($con,”SELECT * FROM contents_arr”);
while($row = mysqli_fetch_assoc($sql)){
// Unserialize
$arr_unserialize1 = unserialize($row[‘arr_serialize1’]);
$arr_unserialize2 = unserialize($row[‘arr_serialize2’]);
// Display
echo “<pre>”;
print_r($arr_unserialize1);
print_r($arr_unserialize2);
echo “</pre>”;
}

Output

Array
(
[0] => Toni Abbah
[1] => Anastacia Mast
[2] => Soji Igbonna
)
Array
(
[0] => Array
(
[username] => Torah
[name] => Toni Abbah
)
[1] => Array
(
[username] => Anamast
[name] => Anastacia Mast
)
[2] => Array
(
[username] => soggy
[name] => Soji Igbonna
)
)

OPTION 2

Use implode() to separate the $names_arr by separator (“,”) and get a string. Pass the value in the INSERT query.

Fetch records and use explode() to convert a comma-separated string in an Array format.

In the example, I am displaying the value in a string and Array format.

Completed Code

<?php
include “config.php”;
// Indexed Array
$names_arr = array(“Toni Abbah”,”Anastacia Mast”,”Soji Igbonna”);
// Separate Array by “ , “
$names_str = implode(“ , “,$names_arr);
// Insert record
$sql = “INSERT INTO contents_arr(name) VALUES(‘“.$names_str.”’)”;
mysqli_query($con,$sql);
// Read record
$sql = mysqli_query($con,”SELECT * FROM contents_arr”);
while($row = mysqli_fetch_assoc($sql)){
$name = $row[‘name’];
$name_explode = explode(“ , “,$row[‘name’]);
echo “name : “.$name.”<br>”;
echo “<pre>”;
print_r($name_explode);
echo “</pre>”;
}

Output

name : Toni Abbah, Anastacia Mast, Soji Igbonna
Array
(
[0] => Toni Abbah
[1] => Anastacia Mast
[2] => Soji Igbonna
)

OPTION 3

Loop on the $users_arr Array.

Read and pass the value in the INSERT query. New record is inserted until data is available.

Completed Code

<?php
include “config.php”;
// Associative Array
$users_arr[] = array(“username”=>”Torah”,”name”=>”Toni Abbah”);
$users_arr[] = array(“username”=>”Anamast”,”name”=>”Anastacia Mast”);
$users_arr[] = array(“username”=>”soggy”,”name”=>”Soji Igbonna”);
// Insert record
foreach($users_arr as $userid=>$user){
$username = $user[‘username’];
$name = $user[‘name’];
$sql = “INSERT INTO contents_arr(username,name) VALUES(‘“.$username.”’,’”.$name.”’)”;
mysqli_query($con,$sql);
}
// Read record
$sql = mysqli_query($con,”SELECT * FROM contents_arr”);
while($row = mysqli_fetch_assoc($sql)){
$username = $row[‘username’];
$name = $row[‘name’];
echo “username : “.$username.”, name : “.$name.”<br>”;
}

Output

username : Torah, name : Toni Abbah
username : Anamast, name : Anastacia Mast
username : soggy, name : Soji Igbonna

OPTION 4

In this option we will learn to store arrays as text so let’s create a sample table

CREATE TABLE `contents_arr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`arr_text1` text NOT NULL,
`arr_text2` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Notice that in this table we used a different datatype to store our array. We used the text datatype as it enables us to store and retrieve our arrays and manipulate directly in our code.

Define two arrays — $names_arr, and $users_arr.

As before $names_arr Array should be Indexed type Array and $users_arr should be an Associative Array.

Insert directly to Database –

INSERT INTO contents_arr (arr_text1, arr_text2) VALUES (‘$names_arr’, ‘users_arr’);

Retrieve directly into an array variable –

$names_arr = [Array value];

Fetch records and pass the array value in the directly to the Array variable.

Completed Code

<?php
include “config.php”;
// Indexed Array
$names_arr = array(“Toni Abbah”,”Anastacia Mast”,”Soji Igbonna”);
// Associative Array
$users_arr[] = array(“username”=>”Torah”,”name”=>”Toni Abbah”);
$users_arr[] = array(“username”=>”Anamast”,”name”=>”Anastacia Mast”);
$users_arr[] = array(“username”=>”soggy”,”name”=>”Soji Igbonna”);
// Insert record
$sql = “INSERT INTO contents_arr(arr_text1,arr_text2) VALUES(‘“.$names_str.”’,’”.$users_str.”’)”;
mysqli_query($con,$sql);
// Read record
$sql = mysqli_query($con,”SELECT * FROM contents_arr”);
while($row = mysqli_fetch_assoc($sql)){
// Assign directly to variable
$names_arr = $row[‘arr_text1’];
$users_arr = $row[‘arr_text2’];
// Display
echo “<pre>”;
print_r($names_arr);
print_r($users_arr);
echo “</pre>”;
}

Output

Array
(
[0] => Toni Abbah
[1] => Anastacia Mast
[2] => Soji Igbonna
)
Array
(
[0] => Array
(
[username] => Torah
[name] => Toni Abbah
)
[1] => Array
(
[username] => Anamast
[name] => Anastacia Mast
)
[2] => Array
(
[username] => soggy
[name] => Soji Igbonna
)
)

This last method or option is what I like to call an awesome way to store arrays to SQL database, just make sure you are using one of the recent sql installations.

Lee Luong – Co-Founder & CEO