在访问时使用 PHP 从 MySQL 数据库中的 txt 文件导入数据 [英] Import data from a txt file in a MySQL database with PHP on visit

问题描述

我正在使用 PHPMyAdmin 来运行我的 MySQL 数据库.

I'm using PHPMyAdmin to run my MySQL database.

假设我们有这个 txt 文件people.txt"、一个 MySQL 数据库和一个 PHP 页面，其中显示了数据库中的数据.假设文本文件中的数据使用以下语法存储:

Suppose we have this txt file "people.txt", a MySQL database and a PHP page in which are showed the data from the database. Suppose that data in the text file are stored with this syntax:

2015/16/01|Alex|Paris  
2015/13/01|Johnny|Berlin  
2015/11/01|Mary|Oslo

您可以注意到每个字段都用 |

You can notice that each field is separated with a |

有没有办法使用 PHP 脚本导入这些数据?我想向你展示一个不同的脚本，当页面被访问时，将数据发送到数据库:

Is there any way to import these data using a PHP script? I want to show you a different script that, when the page is visited, send data to the database:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO `People` (data, name, city)
VALUES ('2015/10/01', 'Will', 'Rome')";

if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id;
    echo "OK!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

我想模拟这个脚本，以便在每次访问页面时检查 txt 文件.有什么帮助吗?

I want to emulate this script in order to let check, each time the page is visited, the txt file. Any help?

我试图合并显示我的数据的 PHP 脚本和从 txt 文件导入它们的脚本，但它似乎无法正常工作..

I tried to merge the PHP script that shows my data and the one that import them from the txt file but it doesn't seem to work properly..

<?php
$con=mysqli_connect("localhost","username","","my_db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed: " . mysqli_connect_error();
}

$sql = "
    LOAD DATA INFILE 'people.txt'
    INTO TABLE `People`
    FIELDS TERMINATED BY '|'
";

$result = mysqli_query($con,"SELECT * FROM `People`");

echo "<table class='people'>
<tr class='titles'>
<th>Data</th>
<th>Name</th>
<th>City</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Data'] . "</td>";
echo "<td>" . $row['Name'] . "</td>";
echo "<td>" . $row['City'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

推荐答案

使用 "LOAD DATA INFILE" 语句，每次访问页面时只将数据加载到表中.

Use the "LOAD DATA INFILE" statement to just load the data into the table every time the page is visited.

$sql = "
    LOAD DATA INFILE 'people.txt'
    INTO TABLE `People`
    FIELDS TERMINATED BY '|'
";

要查看的 SQL 的一部分是 REPLACE 或 IGNORE 选项，它决定了如果脚本尝试插入与现有唯一值重复的行时会发生什么键，如果你的桌子上有的话.

One part of the SQL to look into are the REPLACE or IGNORE option, which determines what will happen if the script tries to insert a row that duplicate an existing unique key, if your table has any.

此外，如果您的输入文件的字段顺序与数据库表的顺序不同，那么您可以在 SQL 的末尾提供一个列列表，例如 (data, name, city).

Also, if your input file has fields in a different order than your database table, then you can provide a list of columns at the end of the SQL, like (data, name, city).

除此之外，我认为您应该能够将您发布的代码中的 $sql 变量替换为类似上述 SQL 的内容，然后运行(如您的原始代码中那样):

Other than those things, I think you should simply be able to replace the $sql variable in your posted code with something like the above SQL, then run (as in your original code):

if ($conn->query($sql) === TRUE) {
    echo "OK!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
} 

这篇关于在访问时使用 PHP 从 MySQL 数据库中的 txt 文件导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！