How to save query results using PHP

PHP is a widely used open source server-side scripting language, which is mainly used in the field of web development. When developing web applications, we usually need to interact with the database to obtain data. After we complete the data query, we need to save the query results for subsequent use. This article will introduce how to save query results using PHP.

1. Use PHP to save the query results into an array

In PHP, we can use the mysqli_fetch_array() function to save the query results into an array. The following is a sample code:

// 连接数据库
$conn = mysqli_connect("localhost", "root", "password", "mydb");

// 执行查询语句
$result = mysqli_query($conn, "SELECT * FROM mytable");

// 将查询结果保存至数组中
$rows = array();
while($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

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

// 打印数组内容
print_r($rows);

In the above code, we first use the mysqli_connect() function to connect to the database. After that, use the mysqli_query() function to execute the query statement and save the query results to the $result variable. Next, we use the while loop and the mysqli_fetch_array() function to save the query results row by row into the $rows array. After the loop ends, we use the mysqli_close() function to close the database connection. Finally, use the print_r() function to print the contents of the $rows array.

2. Use PHP to save the query results to a JSON file

In addition to saving the query results to an array, we can also save the query results to a JSON file. The following is a sample code:

// 连接数据库
$conn = mysqli_connect("localhost", "root", "password", "mydb");

// 执行查询语句
$result = mysqli_query($conn, "SELECT * FROM mytable");

// 将查询结果保存至数组中
$rows = array();
while($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

// 将数组转换为JSON字符串
$json_str = json_encode($rows, JSON_UNESCAPED_UNICODE);

// 将JSON字符串保存至文件中
file_put_contents('result.json', $json_str);

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

In the above code, we first use the mysqli_connect() function to connect to the database. After that, use the mysqli_query() function to execute the query statement and save the query results to the $result variable. Next, we use the while loop and the mysqli_fetch_array() function to save the query results row by row into the $rows array. After the loop ends, we use the json_encode() function to convert the $rows array into a JSON string. Note: We use the JSON_UNESCAPED_UNICODE parameter to preserve the originality of Chinese characters. Finally, we use the file_put_contents() function to save the JSON string to a file. The file name is result.json. Finally, use the mysqli_close() function to close the database connection.

3. Use PHP to save the query results to a CSV file

In addition to saving to a JSON file, we can also save the query results to a CSV file. The following is a sample code:

// 连接数据库
$conn = mysqli_connect("localhost", "root", "password", "mydb");

// 执行查询语句
$result = mysqli_query($conn, "SELECT * FROM mytable");

// 将查询结果保存至数组中
$rows = array();
while($row = mysqli_fetch_array($result)) {
    $rows[] = $row;
}

// 打开CSV文件句柄
$fp = fopen('result.csv', 'w');

// 写入CSV文件标题
fputcsv($fp, array('id', 'name', 'age'));

// 逐行写入CSV文件内容
foreach ($rows as $row) {
    fputcsv($fp, $row);
}

// 关闭CSV文件句柄
fclose($fp);

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

In the above code, we first use the mysqli_connect() function to connect to the database. After that, use the mysqli_query() function to execute the query statement and save the query results to the $result variable. Next, we use the while loop and the mysqli_fetch_array() function to save the query results row by row into the $rows array. After the loop ends, we use the fopen() function to open the CSV file, where the first parameter is the file name and the second parameter is the file opening method. We use the w parameter to open the file for writing. Next, we use the fputcsv() function to write the CSV file title, in which we use the array() function to define an array containing 'id', 'name', and 'age'. Note: These titles should match the names of the fields in the query results. Next, we use a foreach loop and the fputcsv() function to write the CSV file content row by row, using the $row variable as a parameter in each row. After the loop ends, we close the CSV file handle using the fclose() function. Finally, use the mysqli_close() function to close the database connection.

Summary:

This article introduces how to use PHP to save query results. We can save query results into an array, save query results into a JSON file, and save query results into a CSV file. The above methods are all commonly used ways to save query results, and can be selected and used according to actual needs.

The above is the detailed content of How to save query results using PHP. For more information, please follow other related articles on the PHP Chinese website!