How to import and export data using PHP and SQLite

How to Import and Export Data using PHP and SQLite

Importing and exporting data is one of the common tasks when developing a website or application. Using PHP and SQLite, we can easily import data from external files into SQLite database and export data from database to external files. This article will introduce how to use PHP and SQLite to import and export data, and provide corresponding code examples.

1. Data import

First, we need to prepare an external file containing the data to be imported. This file can be in CSV format, with each line representing one record and each field separated by commas. Next, we will use PHP's file() function to read the file contents into an array. We then parse the data row by row and insert the data into the database using SQLite's INSERT statement.

The following is a sample code that demonstrates how to import data from a CSV file into a SQLite database:

<?php

// 设置SQLite数据库文件路径
$dbFile = 'database.db';

// 打开数据库连接
$db = new SQLite3($dbFile);

// 读取CSV文件内容
$csvFile = 'data.csv';
$lines = file($csvFile, FILE_IGNORE_NEW_LINES);

// 逐行解析数据并插入数据库
foreach ($lines as $line) {
    $data = explode(',', $line);
    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('$data[0]', '$data[1]', '$data[2]')";
    $db->exec($sql);
}

// 关闭数据库连接
$db->close();

echo '数据导入完成';

?>

Note that table_name in the above sample code needs to be replaced For your database table name, column1, column2, column3 need to be replaced with the field names in your table.

2. Data Export

To export data from a SQLite database to an external file, we need to execute a query statement to obtain the data to be exported, and save the results to a file middle. Using PHP's SQLite3 library, you can easily perform query operations and save the results to a file.

The following is a sample code that demonstrates how to export data from a SQLite database to a CSV file:

<?php

// 设置SQLite数据库文件路径
$dbFile = 'database.db';

// 打开数据库连接
$db = new SQLite3($dbFile);

// 查询数据库中的数据
$sql = "SELECT * FROM table_name";
$result = $db->query($sql);

// 创建CSV文件并写入数据
$csvFile = 'export_data.csv';
$handle = fopen($csvFile, 'w');

// 写入表头
$header = array('Column1', 'Column2', 'Column3');
fputcsv($handle, $header);

// 写入数据
while ($row = $result->fetchArray()) {
    fputcsv($handle, $row);
}

// 关闭文件句柄和数据库连接
fclose($handle);
$db->close();

echo '数据导出完成';

?>

Also note that table_name in the above sample code Need to be replaced with your database table name.

The above is a simple example of data import and export using PHP and SQLite. You can modify and extend it according to actual needs. Hope this article is helpful to you!

The above is the detailed content of How to import and export data using PHP and SQLite. For more information, please follow other related articles on the PHP Chinese website!