How to save array to database in php

In PHP development, saving array data to the database is a common task. This article will explain how to save an array into a MySQL database using PHP.

The first step is to create a database table

In the MySQL database, we need to first create a table to store array data.

We can use the following SQL statement to create a table named "mytable":

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

The table includes two columns: id (used to uniquely identify each array) and data (used to uniquely identify each array) for storing array data).

Second step, prepare array data

Now, we need to prepare an array as the data to be stored. In this example, we will use the following array:

$data = array(
  'name' => 'John',
  'age' => 30,
  'email' => 'john@example.com'
);

Step 3, connect to the MySQL database

Next, we need to connect to the MySQL database through PHP code. The following code can be used:

$host = 'localhost';
$username = 'root';
$password = 'password';
$dbname = 'mydatabase';

// 创建数据库连接
$conn = new mysqli($host, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
  die('Connection failed: ' . $conn->connect_error);
}

In the above code, we have used the mysqli function to connect to the MySQL database. If the connection fails, an error message will be output.

The fourth step is to convert the array into a JSON format string

Since the MySQL database can only store text data, we need to convert the array data into text format. Here we can convert the array into JSON format string.

Use the following code to convert the array into a JSON string:

$json_data = json_encode($data);

The fifth step, insert data into the MySQL table

Next, we can use the INSERT statement to Insert JSON string into MySQL table:

$sql = "INSERT INTO mytable(data) VALUES('$json_data')";

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

In the above code, we use INSERT statement to insert JSON string into mytable table. If the insertion is successful, "New record created successfully" will be output, otherwise an error message will be output.

Step six, read array data from the MySQL table

Now, we have saved the array data to the MySQL table. We can also retrieve data from the table and convert it into a PHP array using the following code:

$sql = "SELECT id, data FROM mytable";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    $array_data[] = json_decode($row['data'], true);
  }
} else {
  echo "0 results";
}

print_r($array_data);

In the above code, we are using the SELECT statement to retrieve the data from the mytable table and using a while loop to iterate through each row of data . Then, use the json_decode function to convert the JSON string to a PHP array and store it in the $array_data array.

Finally, we use the print_r function to output the $array_data array to check whether the data is read correctly.

Summary

Now, we know how to save a PHP array into a MySQL database. The following points need to be noted:

• To store array data in a MySQL table, it needs to be converted into a JSON format string.
• After reading the data from the MySQL table, you need to use the json_decode function to convert it back to a PHP array.
• If you store large amounts of array data in a MySQL table, it may cause performance problems. Therefore, when designing a database, optimization of the data structure should be considered.

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