对文件的总和行求和，并使用总计进行查询.PHP MSSQL. - SUM lines of a file for total and make query with total. PHP MSSQL

>我有这样的文件

FG  09097612 DN 6575 HL    879797
BHC 09097613 DN 6576 HL    879798
FG  09097614 DN 6577 IOPPP 879799
FG  09097614 DN 6577 IOPPP 879800

我用 mysql 导入它

$lines = file("upload/import.txt");
$dbhandle = odbc_connect("Driver={SQL Server Native Client 11.0};
Server=$myServer;Database=$myDB;", $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
$query = "INSERT INTO TEST (nation) VALUES 'n";
$row = array(); // query for each line
foreach($lines as $lineNum => $line ) {
    $nation = trim(substr($line, 0, 4)); // get first four characters as nation and remove spaces
    $prize = trim(substr($line, 4, 8)); // get 5th-12th characters as prize and remove spaces
    $player = trim(substr($line, 12, 2)); // get 13th-14th characters as player and remove spaces
    $row []= "(".$nation.")";
}
$query .= implode(",'n",$row).";";
$result = odbc_exec($dbhandle, $query);

我现在需要的是检查文件中是否有相同国家的行，在本例中为"FG"，并将每个"FG"的$prize相加，仅保存奖品总数，而不是 FG 的所有行？

如果您需要在 php 中执行此操作，我会将行保存在由国家键控的数组中，每次检查该键是否已经存在，如果是，只需增加奖励。

像这样的东西：-

<?php
$lines = file("upload/import.txt");
$dbhandle = odbc_connect("Driver={SQL Server Native Client 11.0};
Server=$myServer;Database=$myDB;", $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
$query = "INSERT INTO TEST (nation, prize) VALUES 'n";
$row = array(); // query for each line
foreach($lines as $lineNum => $line ) 
{
    $nation = trim(substr($line, 0, 4)); // get first four characters as nation and remove spaces
    if (array_key_exists($nation, $row))
    {
        $row[$nation]['prize'] += $prize;
    }
    else
    {
        $prize = trim(substr($line, 4, 8)); // get 5th-12th characters as prize and remove spaces
        $player = trim(substr($line, 12, 2)); // get 13th-14th characters as player and remove spaces
        $row[$nation]= array('nation'=>$nation, 'prize'=>$prize);
    }
}
array_walk($row, function($v, $k){return "(".$v['nation'].", ".$v['prize'].")";});
$query .= implode(",'n",$row).";";
$result = odbc_exec($dbhandle, $query);
?>

但是，如果这是在MySQL中完成的，我会很想将国家作为数据库的唯一键，并在插入查询的末尾添加ON DUPLICATE KEY SET prize=prize + VALUES（prize）。

你可以编写非常复杂的php代码来做到这一点。或者，您可以改变策略。首先，让我指出，您可以使用load data infile从文件中读取数据。

新策略是将数据读入临时表，然后将其复制到最终表中。基本上：

read data into test_staging table (using php or `load data infile`)
insert into test(nation, prize)
    select nation, sum(prize)
    from test_staging
    group by nation;
drop table test_staging;

因此，如果您在 PHP 中需要它，我建议您使用这样的命名数组：

$insertLines = array();
foreach($lines as $lineNum => $line ) {
    $nation = trim(substr($line, 0, 4)); // get first four characters as nation and remove spaces
    $prize = trim(substr($line, 4, 8)); // get 5th-12th characters as prize and remove spaces
    insertLines[$nation] += $prize;
}
foreach($insertLines as $lineNation => $linePrice ) {
    // The creation of the insert into the database goes here.
}

并请检查长度。我认为 plazer 在 14 到 15 ？

$player = trim(substr($line, 13, 2));