php: using google csv, remove duplicate values populated in column

Question

I am a newbie to php and have been searching tirelessly for a solution to this problem (i'll bet its a super simple solve too *sigh).

I am importing a .csv feed from a google doc. It is pulling in 2 columns, one for "name" and the other "location". I would like to remove duplicate "locations". since i am using fgetcsv, my understanding is that it is already sorting the data into an array. Ideally, it would omit the "location" duplicates so that the "names" look as though they are listed under the "location" they correspond to.

Here is what i have:

    $url = "https://docs.google.com/spreadsheet/pub?key=0AsMT_AMlRR9TdE44QmlGd1FwTmhRRkFHMzFTeTZhS3c&output=csv";
    $handle = fopen($url, "r");
    while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
    echo "<li>\n";
    echo $data[1];
    echo "<br/>\n";
    echo $data[2];
    echo "</li>\n";
    }
    fclose($handle);

ideally i would be able to use something like this:

    $url = "https://docs.google.com/spreadsheet/pub?key=0AsMT_AMlRR9TdE44QmlGd1FwTmhRRkFHMzFTeTZhS3c&output=csv";
    $handle = fopen($url, "r");
    while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
    echo "<li>\n";
    echo array_unique($data[1]);
    echo "<br/>\n";
    echo $data[2];
    echo "</li>\n";
    }
    fclose($handle);

Many thanks in advance for any help! :o)

Array ( [0] => timestamp [1] => location [2] => info ) Is that what you are looking for? Thanks for responding Baba. :o) — christa, May 05 '12 at 20:41
Here's a Demo I created for use: `https://docs.google.com/spreadsheet/pub?key=0AsMT_AMlRR9TdE44QmlGd1FwTmhRRkFHMzFTeTZhS3c&output=csv` — christa, May 05 '12 at 20:53
`Google Docs has encountered a server error. If reloading the page doesn't help, please contact us.` — Baba, May 05 '12 at 20:57
updated my initial post with the csv link, it's working for me. hopefully this isn't a google "share" setting issue. thanks again. — christa, May 05 '12 at 21:03

Anthony · Answer 1 · 2012-05-05T20:53:02.260

0

This may work, assuming that the items in the array are grouped by location. It stores the last data item (location) and compares whether each item has that location. If it does, it prints it, otherwise it creates a new list item with the new location, and then prints the name underneath (I haven't tested it though):

$url = "the-url-to-my-csv-feed";
$handle = fopen($url, "r");
$lastdata = '';
while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
    if ($lastdata == '') {
        echo "<li><strong>" . $data[1] . "</strong>\n";
        echo "<br/>\n";
        $lastdata = $data[1];
    }

    if ($lastdata != $data[1]) {
        echo "</li>\n";
        echo "<li><strong>" . $data[1] . "</strong>\n";
        echo "<br/>\n";
        $lastdata == $data[1];
    }
    echo $data[2] . "<br/>\n";
}
fclose($handle);

edited May 05 '12 at 20:53

answered May 05 '12 at 20:45

Anthony

99
6

Hi Anthony, this is still repeating the location names – christa May 05 '12 at 21:21
I've created a similar thing using this code in codepad: http://codepad.viper-7.com/y95E9J and it seems to work there, I'm not sure what the problem could be. Can you check that the format of your array ($data) is correct (as in $data[1] actually refers to the locations and not the names). Also, shouldn't it be zero based? ($data[0]) – Anthony May 06 '12 at 22:26

score 0 · Answer 2 · answered May 05 '12 at 21:15

<? //PHP 5.4+
$url = 'url to your csv feed';

//Group people by same location first,
//not assuming csv is already sorted.
$namesByLocations = [];
//Because we're using \SplFileObject, when the reference goes out
//of scope at the end of the loop, the file pointer is never
//left open. This is true even if an exception is thrown
//in the middle of looping.
foreach(
    \call_user_function(static function() use ($url){
        $file = new \SplFileObject($url);
        $file->setFlags(\SplFileObject::READ_CSV);
        return $file;
    })
    as $array
){
    //$array[1] is assumed to be location string
    //$array[2] is assumed to be a name that is there.
    $namesByLocations[$array[1]][] = $array[2];
}


foreach($namesByLocations as $location => $names){
    //Protect against injection flaws,
    //escape to destination's context. (html this time)
    echo '<strong>' . \htmlspecialchars($location) . '</strong>';
    echo '<ul>';
    foreach($names as $name){
        echo '<li>' . \htmlspecialchars($name) . '</li>';
    }
    echo '</ul>';
}
?>

You're likely running into Google server settings, as other commenters mentioned against the original question. To test in the meantime, try a copy of the csv that you may have on disk somewhere. — Cory Carson, May 05 '12 at 21:26

php: using google csv, remove duplicate values populated in column

2 Answers2