How to store JSON data to MySQL database in PHP?

How does PHP store JSON data into a MySQL database?

When developing web applications, we often need to store data collected from the front end into the database. JSON (JavaScript Object Notation) is a commonly used data exchange format that is popular for its simplicity and ease of parsing. This article explains how to store JSON data into a MySQL database using PHP and provides code examples.

First, we need to create a MySQL database table to store JSON data. Suppose we want to store the user's name and age information, we can create a table named users, containing two fields name and age. The table can be created using the following SQL statement:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);

Next, we can use PHP to parse the JSON data and store it into the database. First, we need to receive JSON data from the frontend. Assume that the JSON data we receive is in the following format:

{
  "name": "Alice",
  "age": 25
}

You can use PHP’s file_get_contents function to read the received JSON data into a string variable:

$jsonData = file_get_contents('php://input');

Then we need to parse the JSON data into a PHP array. JSON data can be parsed into an associative array using the json_decode function:

$data = json_decode($jsonData, true);

After parsing, the $data variable will contain the parsed associative array with the key name JSON The field name in the data.

Next, we can use PHP to store the parsed data into the MySQL database. First, we need to establish a connection to the database using the PDO extension. The following code example can be used:

$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'your_username';
$password = 'your_password';

$pdo = new PDO($dsn, $username, $password);

Please replace mydatabase with your actual database name and your_username and your_password with your actual Database username and password.

Next, we can use prepared statements to execute INSERT statements. This prevents SQL injection attacks and improves performance. The following is a sample code:

$stmt = $pdo->prepare("INSERT INTO users (name, age) VALUES (:name, :age)");
$stmt->bindParam(':name', $data['name']);
$stmt->bindParam(':age', $data['age']);
$stmt->execute();

In the above code, we first prepare the INSERT statement using the prepare method, and bind the parameters using the bindParam method. We can then execute the INSERT statement using the execute method.

Finally, we can determine whether the INSERT was successful by checking the number of rows affected. If the number of affected rows is greater than 0, it means the record was successfully inserted.

if ($stmt->rowCount() > 0) {
  echo "数据存储成功！";
} else {
  echo "数据存储失败！";
}

The above are the steps and code examples for using PHP to store JSON data into a MySQL database. By parsing JSON data and using prepared statements to execute INSERT statements, we can safely store JSON data into the database for subsequent querying and processing.

I hope this article will help you store JSON data in PHP to a MySQL database!

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