使用PHP将TXT文件中的行插入SQL数据库

I have a file that contains data from a device, and I need to insert this data into a database. The text file looks like this:

我有一个包含设备数据的文件，我需要将这些数据插入数据库。文本文件如下所示：

10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
10/01/15-20:38:26 = 67.3,18.4,30.6,39.3,70.0,66.6
10/01/15-20:39:21 = 74.5,16.8,28.2,36.0,70.0,66.6
10/01/15-20:40:16 = 76.8,16.6,27.6,35.2,70.0,66.6
10/01/15-20:41:12 = 78.1,16.4,27.3,34.9,70.0,66.6

And my code currently looks like this:

我的代码目前看起来像这样：

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


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

<?PHP

    $file_handle = fopen("datalog.txt", "r");

    while (!feof($file_handle) ) {

    $line_of_text = fgets($file_handle);
    $parts = explode('  ', $line_of_text);

    print $parts[0] . $parts[1]. "<BR>";

}

$datainsert = "INSERT INTO reading (val1, val2, val3, val4, val5, val6, val7, val8, val9, val10) VALUES ($parts[0])"; 

if (mysqli_query($conn, $datainsert)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $datainsert . "<br>" . mysqli_error($conn);
}


?>

<?php
$conn->close();
?>

The error I get is this:

我得到的错误是这样的：

Error:

错误：

INSERT INTO reading (Box_ID, Time_Stamp, Temperature, Humidity, CO_Level, VOC_Reading_UOM, VOC_Reading_V1, VOC_Reading_V2, VOC_Reading_V3, VOC_Reading_V4) VALUES () Column count doesn't match value count at row 1

INSERT INTO读数（Box_ID，Time_Stamp，温度，湿度，CO_Level，VOC_Reading_UOM，VOC_Reading_V1，VOC_Reading_V2，VOC_Reading_V3，VOC_Reading_V4）VALUES（）列数与第1行的值计数不匹配

My database and site do connect without errors, and this page is the only one with problems. What is going wrong?

我的数据库和站点连接没有错误，这个页面是唯一有问题的页面。出了什么问题？

2 个解决方案

#1

You appear to be exploding by a string containing two spaces. In your text file, however, your 'fields' are separated by a single space. You should change your call to explode() to use a single space (or get fancy and look at preg_split()).

您似乎通过包含两个空格的字符串进行爆炸。但是，在文本文件中，“字段”由单个空格分隔。您应该将您的调用更改为explode（）以使用单个空格（或者看看preg_split（））。

#2

It looks to me you're trying to explode() your line string using a space as a delimiter. But that's not how it's put together according to your question.

在我看来，你试图使用空格作为分隔符来爆炸（）你的线串。但根据你的问题，这不是它的组合方式。

Here's a line string, with numbers showing how it's exploded with a space.

这是一个线串，数字显示它是如何用空格爆炸的。

 10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
 00000000000000000 1 22222222222222222222222222222

Perhaps you want a sequence of two explode() calls:

也许你想要一个两个explode（）调用的序列：

    $a = explode (' = ', $line_of_text);
    $vals = explode(',' $a[1]);

That will break up your line like so

这将打破你的界限

 10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
 00000000000000000   11111111111111111111111111111
                     0000 1111 2222 3333 4444 5555

At the end of both these explode() calls you'll have a $vals array with your six numbers in it. Then you can do your insert like this.

在这两个explode（）调用结束时，你将拥有一个带有六个数字的$ vals数组。然后你就可以像这样插入。

INSERT INTO reading
       (tstamp, val1, val2, val3, val4, val5, val6)
VALUES (STR_TO_DATE('%m/%d/%y %H:%i:%s',?), ?,?,?,?,?,?)

You can bind $a[0] -- the timestamp from your observation line -- to the first ? placeholder, and the six elements of $vals to the rest of the placeholders.

你可以将$ a [0] - 观察线的时间戳 - 绑定到第一个？占位符，以及$ vals的六个元素到其他占位符。

Edit OK, let's talk about placeholders. They go with bind_param() in MySQLi. Read up on that. Seriously.

编辑确定，让我们谈谈占位符。它们与MySQLi中的bind_param（）一起使用。阅读它。认真。

Your code might look something like this:

您的代码可能如下所示：

$linecount = 0; 

$file_handle = fopen("datalog.txt", "r");
while (!feof($file_handle) ) {
    $line_of_text = fgets($file_handle);
    $a = explode (' = ', $line_of_text);
    $vals = explode(',' $a[1]);

    if ($linecount == 0) {
        /* rig up your SQL statement, just once */
        $sql = "INSERT INTO reading (tstamp, val1, val2, val3, val4, val5, val6) VALUES (STR_TO_DATE('%m/%d/%y %H:%i:%s',?), ?,?,?,?,?,?)";
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param('dssssss', $a[0], $vals[0],$vals[1],$vals[2],$vals[3],$vals[4],$vals[5]);
    }
    if (!$stmt->execute()) {
         printf("Error: %s.\n", $stmt->error);
    }
    $linecount++;
}

$stmt->close();

Please note: I haven't had a chance to debug this code; if you use it that's left to you.

请注意：我没有机会调试此代码;如果你使用它留给你。

#1

#2