How to store php array in database

In PHP, array is a very common data structure that can store multiple related data together in the form of key-value pairs to make it easier to operate and manage these data. In actual projects, we often need to store arrays in the database, which can not only facilitate data backup and management, but also facilitate data query and analysis. This article will explain how to store arrays in a database in PHP.

1. Create database table

First, we need to create a table in the database to store array data. Assuming that the array data we want to store is a list of students, including student number, name, age, gender and other information, then we can create the following table structure:

CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
stu_id varchar(20) NOT NULL,
name varchar(50) NOT NULL,
age int(11) NOT NULL,
gender varchar(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above table structure contains 5 fields, where id is the auto-incrementing primary key, stu_id is the student number, name is the name, age is the age, and gender is the gender.

2. Convert the array to JSON format

Before storing the array in the database, we need to convert it to JSON format first to facilitate storage and subsequent reading. operate. You can use PHP's built-in json_encode() function to convert the array to JSON format. The sample code is as follows:

$studentList = array(

array('stu_id' => '1001', 'name' => '张三', 'age' => 18, 'gender' => '男'),
array('stu_id' => '1002', 'name' => '李四', 'age' => 19, 'gender' => '女'),
array('stu_id' => '1003', 'name' => '王五', 'age' => 20, 'gender' => '男'),

);
$jsonStr = json_encode($studentList );

The above code defines a two-dimensional array named $studentList, containing the information of 3 students, and then uses the json_encode() function to convert it into a JSON format string.

3. Storing JSON data into the database

Now that we have obtained the data in JSON format, we can store it in the database. You can use PHP's built-in mysqli extension to operate the database and insert JSON strings into the student table. The sample code is as follows:

$dbHost = 'localhost'; //Database host
$dbUser = 'root' ; //Database user name
$dbPwd = '123456'; //Database password
$dbName = 'test'; //Database name

//Connect to database
$conn = mysqli_connect($dbHost, $dbUser, $dbPwd, $dbName);
if (!$conn) {

die('数据库连接失败：' . mysqli_error());

}

//Insert JSON data into the database
$sql = "INSERT INTO student (stu_id, name, age, gender) VALUES (' $jsonStr')";
if (mysqli_query($conn, $sql)) {

echo '数据插入成功！';

} else {

echo '数据插入失败：' . mysqli_error($conn);

}

The above code uses mysqli_connect( ) function connects to the database and then executes an INSERT statement to insert the JSON string into the student table. Among them, '$jsonStr' is a variable representing student data stored in JSON format.

4. Read JSON data from the database and convert it into an array

After storing the JSON format data into the database, we can read from the database through the SELECT statement This data is converted into a PHP array using the json_decode() function. The sample code is as follows:

//Read JSON data from the database
$sql = "SELECT * FROM student WHERE id=1";
$result = mysqli_query($conn, $sql);

//Convert JSON format data into a PHP array
$studentArr = array();
if (mysqli_num_rows($result)) {

$row = mysqli_fetch_assoc($result);
$jsonStr = $row['stu_data'];
$studentArr = json_decode($jsonStr, true);

}

The above code uses the SELECT statement to read the student data with id 1 from the student table, and then uses the mysqli_fetch_assoc() function to The result set is converted into an associative array, the field value named 'stu_data' is taken out (student data in JSON format is stored), and finally the json_decode() function is used to convert it into a PHP array. Among them, setting the second parameter to true means converting the JSON object into a PHP array.

Summary

In PHP, storing arrays into a database does not require additional plug-ins or extensions. Just convert the array to JSON format and store it in the database field. At the same time, when reading data, it is also necessary to convert the JSON string into a PHP array to facilitate subsequent processing and operations.

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