How to use JSON data with PHP and MySQL?

How to use JSON data in PHP and MySQL?

Introduction:
JSON (JavaScript Object Notation) is a lightweight data exchange format that is widely used for front-end and back-end data transmission. As a popular server-side programming language, PHP, combined with the MySQL database, provides flexible and powerful development tools. This article will introduce how to use JSON data in PHP and MySQL, and provide corresponding code examples.

1. Storing JSON data in MySQL
MySQL supports the JSON data type starting from version 5.7.8. When creating a table, you can use the JSON data type to specify the column data type as JSON. For example:

CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
data JSON,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

in the example dataThe data type of the column is defined as JSON. This way, JSON-formatted data can be stored directly into the column when inserting or updating data.

2. Convert MySQL data to JSON format
In PHP, you can use the json_encode() function to convert MySQL query results into JSON format data to facilitate data processing on the front end. Transmission and processing. The following is a sample code:

// Create a database connection
$conn = mysqli_connect("localhost", "username", "password", "database");

// Execute query
$result = mysqli_query($conn, "SELECT * FROM users");

// Convert the result to an associative array
$rows = array( );
while($row = mysqli_fetch_assoc($result)){

$rows[] = $row;

}

// Convert the result to JSON data
$json_data = json_encode($rows) ;

// Output JSON data
echo $json_data;

// Close the database connection
mysqli_close($conn);
?>

In the above code, first create a database connection through the mysqli_connect() function, and execute the query statement to obtain the results. Then, use the mysqli_fetch_assoc() function to convert the query results into an associative array. Next, use the json_encode() function to convert the associative array into JSON format data. Finally, the JSON data is output through the echo statement.

3. Insert JSON data into the MySQL database
In some cases, it is necessary to insert the JSON data passed by the front end into the MySQL database. In PHP, you can use the json_decode() function to decode JSON data into a PHP array or object. The following is a sample code:

// Create a database connection
$conn = mysqli_connect("localhost", "username", "password", "database");

// Get the JSON data of the POST request
$json_data = file_get_contents('php://input');

// Decode the JSON data into an associative array
$data = json_decode($json_data, true);

//Insert data
$sql = "INSERT INTO users (name, data) VALUES ('" . $data['name'] . "', '" . json_encode($data['data']) . "')";
mysqli_query($conn, $sql);

// Output the message of successful insertion
echo "data Insertion successful!";

//Close the database connection
mysqli_close($conn);
?>

In the above code, first passmysqli_connect()Function creates a database connection. Then, use the file_get_contents() function to get the JSON data of the POST request. Next, use the json_decode() function to decode the JSON data into an associative array. Finally, the decoded data is inserted into the MySQL database.

Conclusion:
Through the introduction of this article, we have learned how to use JSON data in PHP and MySQL. By converting MySQL data into JSON format, we can flexibly process back-end data for display and interaction on the front-end. At the same time, we also learned how to insert the JSON data passed from the front end into the MySQL database to save and manage the data. Using PHP and MySQL combined with JSON data, you can easily build powerful web applications.

The above is the detailed content of How to use JSON data with PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!