How To Store Array In Mysql

Storing arrays in MySQL is a task that you may come across when dealing with large volumes of data. This tutorial will show you how to store an array of data in a MySQL table using PHP, for WordPress sites. The process will include creating a table in the MySQL database, storing values from an array into the table, and retrieving the values back from the table.

Step 1: Create a MySQL Table

Before we store an array in MySQL, we need to create a table to hold the data. In this tutorial, we’ll use a simple table with columns for an ID (primary key), and a value (to store the array elements). You can run the following SQL query in your MySQL client or PHPMyAdmin to create the table:

1
2
3
4
CREATE TABLE `array_storage` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `value` VARCHAR(255) NOT NULL
);

Step 2: Connect to the MySQL Database

Next, you need to establish a connection to the MySQL database from your PHP script. Replace the values for the database host, username, password, and name with the appropriate values for your database:

1
2
3
4
5
6
7
8
9
10
11
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$db_name = 'your_database_name';
 
$conn = mysqli_connect($host, $username, $password, $db_name);
 
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

Step 3: Store Array in MySQL Table

To store the array in the MySQL table, we first need to prepare a list of values to insert into the table. Suppose we have the following array of data:

Array
(
    [0] => Apple
    [1] => Banana
    [2] => Orange
)

We can use a foreach loop to iterate over the array and store each element in the array_storage table:

1
2
3
4
5
6
7
8
$array = array("Apple", "Banana", "Orange");
 
foreach ($array as $value) {
    $query = "INSERT INTO array_storage (value) VALUES ('$value')";
    if (!mysqli_query($conn, $query)) {
        echo "Error: " . mysqli_error($conn);
    }
}

Step 4: Retrieve the Array from MySQL Table

To retrieve the stored array elements from the MySQL table, use the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
$query = "SELECT value FROM array_storage";
$result = mysqli_query($conn, $query);
 
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Value: " . $row["value"] . "<br>";
    }
} else {
    echo "0 results";
}
 
mysqli_close($conn);

At the end of the script, remember to close the connection using mysqli_close(). The output of the retrieved array elements should look like:

Value: Apple
Value: Banana
Value: Orange

Full Code

Here is the full PHP code example to store and retrieve an array in a MySQL table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$db_name = 'your_database_name';
 
$conn = mysqli_connect($host, $username, $password, $db_name);
 
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
$array = array("Apple", "Banana", "Orange");
 
foreach ($array as $value) {
    $query = "INSERT INTO array_storage (value) VALUES ('$value')";
    if (!mysqli_query($conn, $query)) {
        echo "Error: " . mysqli_error($conn);
    }
}
 
$query = "SELECT value FROM array_storage";
$result = mysqli_query($conn, $query);
 
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Value: " . $row["value"] . "<br>";
    }
} else {
    echo "0 results";
}
 
mysqli_close($conn);

Conclusion

In this tutorial, we’ve demonstrated how to store an array in a MySQL table and retrieve the elements using PHP. This method is useful when working with large data sets and can be adapted for various data types and scenarios. However, it’s important to note that when working with complex data structures, using JSON or serialized data formats might be more efficient for storing and retrieving data in a MySQL database.