How to get JSON data from external API using PHP and MySQL?

How to get JSON data from external API using PHP and MySQL?

In modern application development, obtaining and processing data provided by external APIs (Application Programming Interface) has become more and more common. API is a technology used to exchange data between different software, which allows developers to access and utilize the functionality of other applications or services. Among them, JSON (JavaScript Object Notation) is a commonly used data format used to transmit structured data.

In this article, we will learn how to use PHP and MySQL to obtain and process JSON data from external APIs. Specifically, we will use PHP's built-in functions and MySQL's database to implement this functionality.

1. Create database table

First, we need to create a table in the MySQL database to store the JSON data obtained from the external API. You can use the following SQL statement to create a table named "json_data":

CREATE TABLE json_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This table has three fields: id is an auto-incremented primary key, data is a text type field used to store JSON data, created_at Is a timestamp field used to record the time of data insertion.

2. Get JSON data

Next, we will use PHP to write code to get the JSON data returned by the external API. Suppose we want to get data from an API named "example_api", we can use the following code:

$api_url = "https://example_api.com/data";  // API的URL
$json_data = file_get_contents($api_url);  // 从API获取JSON数据

In this example, we use PHP's file_get_contents function to get data from the specified URL. Normally, the API will return a JSON-formatted string, which we can store in the variable $json_data.

3. Parsing and processing JSON data

Once we get the JSON data, we need to parse and process it. PHP provides some built-in functions to manipulate JSON data, such as json_decode and json_encode.

$data = json_decode($json_data, true);  // 将JSON字符串解析为PHP数组

// 处理JSON数据
foreach ($data as $item) {
    $value1 = $item['field1'];
    $value2 = $item['field2'];
    
    // 将数据插入数据库表
    $sql = "INSERT INTO json_data (data) VALUES ('$json_data')";
    $result = mysqli_query($conn, $sql);
}

In this example, we use the json_decode function to parse a JSON string into a PHP array. Then, we can use PHP arrays to access fields and values ​​in JSON.

Pay attention to the part where we insert data into the database table. We used a simple SQL insert statement to insert JSON data into the "json_data" table.

4. Connecting to MySQL database

To insert data into the MySQL database, we need to first establish a connection to the database. This can be achieved using PHP's mysqli function.

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

In this example, we put the database connection information in variables and use the mysqli_connect function to establish a connection to the database. If the connection fails, an error message will be output.

5. Full sample code

Here is a complete sample code that shows how to get JSON data from an external API and store it in a database using PHP and MySQL:

$api_url = "https://example_api.com/data";  // API的URL
$json_data = file_get_contents($api_url);  // 从API获取JSON数据

$data = json_decode($json_data, true);  // 将JSON字符串解析为PHP数组

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// 处理JSON数据并将其插入数据库表
foreach ($data as $item) {
    $value1 = $item['field1'];
    $value2 = $item['field2'];
    
    $sql = "INSERT INTO json_data (data) VALUES ('$json_data')";
    $result = mysqli_query($conn, $sql);
}

// 关闭数据库连接
mysqli_close($conn);

Please note that this is just a sample code and may need to be modified and adjusted according to the actual situation. For example, you may need to add parameters to the URL based on actual API requirements, use API keys for authentication, etc.

Summary

This article introduces the basic steps on how to use PHP and MySQL to obtain JSON data from an external API. By parsing JSON data and storing it in a MySQL database, we can easily obtain and process structured data provided by external APIs. Hope this article helps you!

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