从PHP数组中获取特定的CSV列或第n个值

I'm using PHP to open and parse a very small (around 1kb) CSV file to generate an HTML table. I'm new to PHP and this is largely experimental. In addition to generating the HTML table, I'm trying to generate an array from a particular set of columns in that csv (city and country information), then remove duplicate values. The CSV is structured like this:

我正在使用PHP打开并解析一个非常小的（大约1kb）CSV文件来生成HTML表。我是PHP的新手，这在很大程度上是实验性的。除了生成HTML表之外，我还尝试从该csv（城市和国家/地区信息）中的特定列集生成数组，然后删除重复值。 CSV的结构如下：

Last Name, First Name, City, Country, Language
Smith, Joe, Shanghai, China, English
Jackson, Stacey, Madrid, Spain, Spanish
Jones, Bob, London, United Kingdom, English
Seward, Elisa, Madrid, Spain, English
Harrison, Tim, Berlin, Germany, German

The idea here is that in addition to a table with all the data, I'll also have a list of all the cities/countries listed in the table:

这里的想法是，除了包含所有数据的表格之外，我还将列出表格中列出的所有城市/国家/地区：

• Shanghai, China
• 上海，中国
• Madrid, Spain
• 西班牙马德里
• London, United Kingdom
• 伦敦，英国
• Berlin, Germany
• 柏林，德国

Thanks to the fgetcsv() documentation and other questions on Stack Overflow, reading the file and building the table is straightforward:

感谢fgetcsv（）文档和Stack Overflow上的其他问题，阅读文件和构建表格非常简单：

<?php
    $handle = fopen("namelist.csv", "r");
    $data = fgetcsv($handle, 1000, ",");
    echo('<table>');
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        echo("<tr>\r\n");
        foreach ($data as $index=>$val) {
            echo("\t<td>");
            echo htmlentities($val, ENT_QUOTES);
            echo("</td>\r\n");
        }
    echo("</tr>\r\n");
    }
    echo("</table>");
    fclose($handle);
?>

But I've been unable to figure out how to grab city, country data and remove duplicates. Does anyone have suggestions?

但我一直无法弄清楚如何获取城市，国家数据和删除重复项。有没有人有建议？

6 个解决方案

#1

In the CSV handle loop, first, the city and country fields are joined with a comma and the $city_countries array is checked. If no dupes the city country string is written to the $city_countries array. The TR tags are echo'ed, and the $data array is looped to write out the TD tags and column values.

在CSV句柄循环中，首先，使用逗号连接city和country字段，并检查$ city_countries数组。如果没有dupes，则将city country字符串写入$ city_countries数组。 TR标签被回显，并且$ data数组被循环以写出TD标签和列值。

$handle = fopen("namelist.csv", "r");
$data = fgetcsv($handle, 1000, ",");
$city_countries = array();
echo('<table>');
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $city_country = $data[2] . ', ' . $data[3];
    if ( !in_array($city_country, $city_countries) ) {
        array_push($city_countries, $city_country);
    }
    echo("<tr>\r\n");
    foreach ($data as $index=>$val) {
        echo("\t<td>");
        echo htmlentities($val, ENT_QUOTES);
        echo("</td>\r\n");
    }
    echo("</tr>\r\n");
}
echo("</table>");
fclose($handle);

print '<pre>'; print_r($city_countries); print '</pre>';

This is the input file I'm using:

这是我正在使用的输入文件：

Last Name, First Name, City, Country, Language
Smith, Joe, Shanghai, China, English
Jackson, Stacey, Madrid, Spain, Spanish
Jackson, Steve, Madrid, Spain, Spanish
Jones, Bob, London, United Kingdom, English
Seward, Elisa, Madrid, Spain, English
Harrison, Tim, Berlin, Germany, German
Jones, Bill, London, United Kingdom, English
Jackson, Ralph, Madrid, Spain, Spanish

And this is the output I'm getting:

这是我得到的输出：

Smith    Joe     Shanghai China          English
Jackson  Stacey  Madrid   Spain          Spanish
Jackson  Steve   Madrid   Spain          Spanish
Jones    Bob     London   United Kingdom English
Seward   Elisa   Madrid   Spain          English
Harrison Tim     Berlin   Germany        German
Jones    Bill    London   United Kingdom English
Jackson  Ralph   Madrid   Spain          Spanish

Array
(
    [0] =>  Shanghai,  China
    [1] =>  Madrid,  Spain
    [2] =>  London,  United Kingdom
    [3] =>  Berlin,  Germany
)

#2

Here a simple way which removes duplicate cities without actually having to filter for them.

这是一种简单的方法，可以删除重复的城市，而无需实际过滤它们。

$fHandle = fopen("namelist.csv", "r");
$aData = fgetcsv($handle, 1000, ",");
while (($aData = fgetcsv($fHandle, 1000, ",")) !== FALSE) {
    $aLocations[$aData[3]] = $aData[4];
}

echo '<table>';
foreach ($aLocations as $sCity => $sCountry) {
    echo '<tr><td>'.$sCity.'</td><td>'.$sCountry.'</td></tr>';
}
echo '</table>';

#3

Try this: (Haven't got access to PHP at the moment, will look for tiny bugs in about an hour)

试试这个:(目前还没有访问PHP，会在大约一小时内找到小错误）

<?php
    $handle = fopen("namelist.csv", "r");
    $data = fgetcsv($handle, 1000, ",");
    $csv = array();
    $csv[] = array();
    $csv[] = array();
    $csv[] = array();
    $csv[] = array();
    $csv[] = array();
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        foreach ($data as $index=>$val) {
            $column=0;
            $csv[column][] = htmlentities($val, ENT_QUOTES);
            $column++;
        }
    }
    fclose($handle);
    //Now, csv[0] has all Last Names, csv[1] has all First Names, csv[2] all Cities, csv[3] all Countries and csv[4] all Languages
    //To filter duplicates..
    $cities = array_unique($csv[2]);
    $countries = array_unique($csv[3]);
?>

This creates an array that holds 5 arrays (one for each column). Those arrays are then populated with each row of the CSV. Afterwards, the city and country columns are purged of repeat values. As stated above, this code SHOULD work but I haven't been able to test it, if it doesn't leave me a comment and I'll be sure to bug fix it later this afternoon.

这将创建一个包含5个数组的数组（每列一个）。然后用CSV的每一行填充这些数组。之后，城市和国家/地区列将清除重复值。如上所述，此代码应该可以工作，但我无法测试它，如果它没有给我留言，我肯定会在今天下午晚些时候修复它。

#4

$data[2] contains city and applying array_unique(...) after pushing all city values to an array will remove duplicates.

$ data [2]包含city并在将所有城市值推送到数组后应用array_unique（...）将删除重复项。

$cities = array();

while (($data = fgetcsv($handle, 1000, ",")) !== false) {
    $cities[] = $data[2];
}

$cities = array_unique($cities);

print_r($cities);

Refer to php manual, which has a bunch of sample codes as well.

请参阅php手册，其中包含一堆示例代码。

#5

When dealing with CSV's that have a header line, I prefer to match the data columns to a named key so I don't need to keep track of which index a specific column relates to. This allows you to reference $var['ColumnName'] instead of $var[2]:

在处理具有标题行的CSV时，我更喜欢将数据列与命名键匹配，因此我不需要跟踪特定列与哪个索引相关。这允许您引用$ var ['ColumnName']而不是$ var [2]：

<?php
$csvDelim = ',';
$csvEnclosure = '';

$csvArr = file('./namelist.csv', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

//create array of the csv headers
$csvHeaders = str_getcsv(trim(array_shift($csvArr)), $csvDelim, $csvEnclosure);
$csvHeaders = array_map("trim", $csvHeaders);

//get the csv data and make a multi-dim array of keys/values
$dataArr = array();
foreach($csvArr as $csvLine) 
{
    $lineData = str_getcsv(trim($csvLine), $csvDelim, $csvEnclosure);
    $lineData = array_map("trim", $lineData);
    $dataArr[] = array_combine($csvHeaders, $lineData);
}

//get unique city/country values
$locations = array();
foreach($dataArr as $da)
    $locations[] = $da['City'].', '.$da['Country'];

$locations = array_unique($locations);

//output data in table
echo '<table>';
echo '<tr>';
foreach($csvHeaders as $headerValue)
    echo '<th>'.$headerValue.'</th>';
echo '</tr>';

foreach($dataArr as $dataLine)
{
    echo '<tr>';
    foreach($dataLine as $dataValue)
    {
        echo '<td>'.htmlentities($dataValue, ENT_QUOTES).'</td>';
    }
    echo '</tr>';
}
echo '</table>';
?>

#6

i read the post above.

我看了上面的帖子。

It isnt working correctly - so here the updated working code of James Hunt.

它没有正常工作 - 所以这里是James Hunt的更新工作代码。

<?php

$handle = fopen("test.csv", "r");
$data = fgetcsv($handle, 1000, ";");

$csv = array();
$csv[] = array();
$csv[] = array();
$csv[] = array();
$csv[] = array();
$csv[] = array();

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

    $column=0;

    foreach ($data as $index=>$val) {

        $csv[$column][] = htmlentities($val, ENT_QUOTES);
        $column++;
    }

}

fclose($handle);
//Now, csv[0] has all Last Names, csv[1] has all First Names, csv[2] all Cities, csv[3] all Countries and csv[4] all Languages
//To filter duplicates..
$cities = array_unique($csv[2]);
$countries = array_unique($csv[3]);

var_dump($cities); //will output all column values of $csv[2]

?>

so stay tuned - greetz Robert!

敬请关注 - 问候罗伯特！