php mysql 从固定宽度的文本文件插入表

Question

我正在尝试编写一个 cron 作业来运行一个 php 脚本，该脚本将从具有固定列的平面(文本)文件中插入数据。

我的文件“data.txt”看起来像这样:

first_column                   second_column        third_column   

例如。第一列的宽度为 30 个字符 + 1 个用于与下一列分隔的空格，第二列为 20 个字符 + 1 个用于分隔的空格，第三列为 15 个字符(包括空格)。我的表“TEST”有 3 列:第一、第二和第三。

问题是，如何先修剪列数据，然后将每一行插入表中？

<?php
// initial database stuff
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$file = file('/home/user/files/data.txt'); # read file into array
$count = count($file);
if($count > 0) # file is not empty
{
    $query = "INSERT into TEST(first,second,third) values";
    $i = 1;
    foreach($file as $row)
    {
        $query .= "('TRIM(SUBSTR($row,1,30))','TRIM(SUBSTR($row,32,20))','TRIM(SUBSTR($row,34,49))')";
        $query .= $i < $count ? ',':'';
        $i++;
    }
mysql_query($query) or die(mysql_error());
}
echo "File data successfully imported to database!!";
?>

Answer 1

它可能是这样的:

<?php
// initial database stuff
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db) or die ("Unable to select database!");
$file = file('/home/user/files/data.txt'); # read file into array
$count = count($file);
// Edited to add loop back in... Silly me.
if($count > 0) # file is not empty

{
    foreach ($file as $row){
        $first=trim(substr($row,0,30));
        $second=trim(substr($row,31,20));
        $third=trim(substr($row,33,49));

        $query = "INSERT into TEST(first,second,third) values".
                 "($first,$second,$third)";
        mysql_query($query) or die(mysql_error());
    }
}
echo "File data successfully imported to database!!";
?>

考虑使用 PDO 和准备好的语句，使其更加干净(并且更安全)。