Working with Incomplete Data in Perl DBI

Question

I have a table which looks like this:

tagname   username    photos

john    John Walker    -

john       -         123.jpg

note: "-" denotes a blank here i.e., nothing.

When I'm doing the following statement, I'm getting blank results.

"SELECT * FROM tableA where tagname=?"

Though, following statement is giving the right result as "123.jpg"

"SELECT photos FROM tableA WHERE tagname=?"

But, following statement is is giving blank again. Doing two separate SQL statements is also not working.

"SELECT username & photos FROM tableA WHERE tagname=?"

I want to club all the username that match a tagname, into @ArrayA and all photos, that match the same tagname into @ArrayB.

Sample code is as follows.

my $tag = 'john';

my $sth = $dbh->prepare(qq(SELECT * FROM tableA WHERE tagname=? ));
$sth->execute($tag) or die "Could not connect to database: $DBI::errstr";

my @ArrayA = undef;
while (my $ABC = $sth->fetchrow_array) {
    push (@ArrayA, $ABC);
    }

print "Content-type: text/html\n\n";
print @ArrayA;

Answer 1

fetchrow_array returns an array.

Change your while loop to:

while (my @row = $sth->fetchrow_array) {
    push @ArrayA, \@row;
}

To view the content of @ArrayA use this:

use Data::Dumper;
print Dumper(\@ArrayA);

Answer 2

You are getting blank field as result since one of the field is NULL. The result you will get for the query for each row will be just 0. You can do concatenation to achieve your requirement.

SELECT CONCAT( username , photos ) AS photo_name FROM tableA WHERE tagname=?

I think, the query

SELECT username & photos FROM tableA where tagname=?

is a wrong choice for your requirement.

Now you can write your program as follows :

my $tag = 'john';
my $sth = $dbh->prepare(qq{SELECT CONCAT( username , photos ) AS photo_name FROM tableA WHERE tagname=?});
$sth->execute($tag) or die "Could not connect to database: $DBI::errstr";

my @ArrayA = undef;
while (my $ABC = $sth->fetchrow_hashref()) {
     push (@ArrayA, $ABC->{photo_name});
}
print "Content-type: text/html\n\n";
print @ArrayA;