English
English
Data import is a common requirement in website or application development. With PHP and SQLite, you can import data from external CSV files into an SQLite database. The process involves reading the CSV file, parsing each line, and inserting the data into the database using SQLite’s INSERT statements.
<?php
// Set the path to the SQLite database file
$dbFile = 'database.db';
<p>// Open a database connection<br>
$db = new SQLite3($dbFile);</p>
<p>// Read the CSV file contents<br>
$csvFile = 'data.csv';<br>
$lines = file($csvFile, FILE_IGNORE_NEW_LINES);</p>
<p>// Parse each line and insert into the database<br>
foreach ($lines as $line) {<br>
$data = explode(',', $line);<br>
$sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('$data[0]', '$data[1]', '$data[2]')";<br>
$db->exec($sql);<br>
}</p>
<p>// Close the database connection<br>
$db->close();</p>
<p>echo 'Data import completed';<br>
?><br>
Please replace table_name with your actual database table name, and adjust column1, column2, and column3 according to your table structure.
Exporting data from the database to an external file facilitates backup and migration. Using PHP’s SQLite3 class, you can execute queries and write the results to a CSV file. The following example demonstrates how to export data from an SQLite database into a CSV file.
<?php
// Set the path to the SQLite database file
$dbFile = 'database.db';
<p>// Open a database connection<br>
$db = new SQLite3($dbFile);</p>
<p>// Query data from the database<br>
$sql = "SELECT * FROM table_name";<br>
$result = $db->query($sql);</p>
<p>// Create a CSV file and write data<br>
$csvFile = 'export_data.csv';<br>
$handle = fopen($csvFile, 'w');</p>
<p>// Write the header row<br>
$header = array('Column1', 'Column2', 'Column3');<br>
fputcsv($handle, $header);</p>
<p>// Write data rows<br>
while ($row = $result->fetchArray()) {<br>
fputcsv($handle, $row);<br>
}</p>
<p>// Close the file handle and database connection<br>
fclose($handle);<br>
$db->close();</p>
<p>echo 'Data export completed';<br>
?><br>
Make sure to replace table_name and the header field names to match your actual database schema.
This article introduced basic methods for importing and exporting data using PHP and SQLite, accompanied by clear code examples for easy reference and modification. Mastering these techniques will allow you to efficiently manage SQLite database data to meet your project’s import and export needs.
