How to store array into database in php

When using PHP for web development, we usually need to store some data in the database for subsequent use. The most commonly used data type is an array.

Next, let’s explore how to store arrays in the database.

1. Prepare the database

First, we need to create a table to store our array data. Taking MySQL as an example, there are the following steps:

1) Create a database, such as test.

2) Create a table under the database, such as array_data, to store our array data. The following SQL statement can be used:

CREATE TABLE `array_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This table contains two fields, namely id and data. Among them, id is the primary key, used to uniquely identify a piece of data; the data field is used to store the array data we want to store.

2. Convert array to string

Before storing the array in the database, we need to convert it to a string. Because only string type data can be stored in the database. To convert an array into a string, you can use the serialize() function provided by PHP.

For example, we have the following array data:

$data = [
    'name' => 'Tom',
    'age' => 25,
    'gender' => 'male'
];

You can use the serialize() function to convert it into a string:

$data_str = serialize($data);

At this time, The value of $data_str is:

a:3:{s:4:"name";s:3:"Tom";s:3:"age";i:25;s:6:"gender";s:4:"male";}

3. Write data to the database

It is very simple to store the string in the database. We can use PHP's PDO extension to connect to the database and then use prepared statements to write data to the database.

The specific code is as follows:

try {
    $dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
    $username = "root";
    $password = "";

    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $data = [
        'name' => 'Tom',
        'age' => 25,
        'gender' => 'male'
    ];
    $data_str = serialize($data);

    $stmt = $pdo->prepare("INSERT INTO array_data (data) VALUES (?)");
    $stmt->bindParam(1, $data_str);
    $stmt->execute();
} catch (PDOException $e) {
    echo $e->getMessage();
}

In the above code, we first create a PDO object, and then use the prepare() function to create a prepared statement. Among them, ? means that the current position requires a parameter, and we pass in the string to be stored as a parameter. Finally, use the execute() function to execute the prepared statement.

4. Reading array data from the database

When we need to read the array data in the database, we can first read the stored string from the database, and then Use the unserialize() function to convert it to an array.

The specific code is as follows:

try {
    $dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
    $username = "root";
    $password = "";

    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->query("SELECT data FROM array_data WHERE id = 1");
    $data_str = $stmt->fetchColumn();

    $data = unserialize($data_str);

    print_r($data);
} catch (PDOException $e) {
    echo $e->getMessage();
}

In the above code, we use the query() function to execute the SQL statement and the fetchColumn() function Get the first column of data from the result set, which is the string holding our array data. Then, use the unserialize() function to convert it to an array type.

Finally, use the print_r() function to output the array data.

Summary

In PHP programs, storing arrays into the database is usually divided into the following steps:

1. Prepare the database and create a text type table of fields.
2. Convert array to string.
3. Save the string into the database.
4. Read the string from the database and convert it into an array using the unserialize() function.

The above is the detailed content of How to store array into database in php. For more information, please follow other related articles on the PHP Chinese website!