PERL CGI Beginners - removing dupes from fetchrow

Author

removing dupes from fetchrow_array()?

Sara

2005-03-23, 8:55 pm

Following is the code used in my CGI script.

my $query = $dbh -> prepare("SELECT * FROM invoices WHERE ID = '$ID'");
$query -> execute();
while (my @row = $query -> fetchrow_array()){
print "$row[1] - $row[2] - $row[3]<br>";
}

What If I want to remove dupes from @row? like if $row[2] is similar in multiple records, only one entry should be showed, the duplicates should not appear in the print.

I am aware of grep, but unable to implement it here in While loop.

@row = grep {++$count{$_} < 2} @row;

I am also aware that Unique keys can be added within mySQL database, but that's un-do-able due to some reasons. I want to remove dupes within the script.

Any ideas?

Chris Devers

2005-03-23, 8:55 pm

On Thu, 24 Mar 2005, Sara wrote:

> Following is the code used in my CGI script.
>
> my $query = $dbh -> prepare("SELECT * FROM invoices WHERE ID = '$ID'");
> $query -> execute();
> while (my @row = $query -> fetchrow_array()){
> print "$row[1] - $row[2] - $row[3]<br>";
> }
>
> What If I want to remove dupes from @row?

So add a UNIQUE filter to your SQL statement. The way to phrase this
will be different depending on your database engine, but most or all SQL
dialects support the UNIQUE command for SELECTing only unique elements.

> I am also aware that Unique keys can be added within mySQL database,
> but that's un-do-able due to some reasons. I want to remove dupes
> within the script.

I think these reasons are worth examining -- it's usually worth the
effort to get your SQL statements to return just the data you want,
rather than return too much and throw some of it away in your code.

--
Chris Devers

Bob Showalter

2005-03-23, 8:55 pm

Sara wrote:
> Following is the code used in my CGI script.
>
> my $query = $dbh -> prepare("SELECT * FROM invoices WHERE ID =
> '$ID'"); $query -> execute();
> while (my @row = $query -> fetchrow_array()){
> print "$row[1] - $row[2] - $row[3]<br>";
> }
>
> What If I want to remove dupes from @row? like if $row[2] is similar
> in multiple records, only one entry should be showed, the duplicates
> should not appear in the print.

Like Chris said, typically you want to use SELECT DISTINT or GROUP BY in
your query. The rule of thumb is to avoid sending unecessary data from the
server to the client.

But the general Perl construct I would use to filter dups is something like:

my %found;
while (...more data...) {
$key = ...some expression...
next if $found{$key}++;
...process the data for the first occurence...
}

Steven Schubiger

2005-03-23, 8:55 pm

On 24 Mar, Sara wrote:

> What If I want to remove dupes from @row? like if $row[2] is similar in multiple records,
> only one entry should be showed, the duplicates should not appear in the print.
> Any ideas?

#! /usr/bin/perl

use strict;
use warnings;

{
local $" = "\n";

my (@dupes, %have);

@dupes = qw(hello hello goodbye goodbye);

for (my $i = $#dupes; $i >= 0; $i--) {
splice(@dupes, $i, 1) if $have{$dupes[$i]};
$have{$dupes[$i]} = 1;
};

print "@dupes\n";
}

Sponsored Links