Can"t use an undefined value as an ARRAY reference at search.cgi line 139.

Hi all,

I have what seems to me to be a strange problem....

I have the script below:


if ($type eq 'alpha') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley
where name like '$search%'
ORDER BY name LIMIT %d,%d",
$start - 1, # number of records to skip
$per_page + 1); # number of records to select
} elsif ($type eq '') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley
where keywords like '%$search%'
ORDER BY name LIMIT %d,%d",
$start - 1, # number of records to skip
$per_page + 1); # number of records to select
}
my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

# Display results as HTML table

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2) {
# get data values in row $i
my @cells = @{$tbl_ref->[$i]}; # get data values in row $i
my @cells2 = @{$tbl_ref->[$i+1]}; # get data values in row $i+1
# map values to HTML-encoded values, or to   if null/empty
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells;
@cells2 = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells2;
# add cells to table
my @cells = "$cells[0]
$cells[1]
$cells[2]

$cells[3]
";
my @cells2 = "$cells2[0]
$cells2[1]
$cells2[2]

$cells2[3]
";
push (@rows, Tr (td (\@cells),(td (\@cells2))));
}


It works great. However if I change it to:

if ($search = '#') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley where REGEXP '^[0-9]'
ORDER BY name LIMIT %d,%d",
$start - 1,
$per_page + 1);
} elsif ($type eq 'alpha') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley
where name like '$search%'
ORDER BY name LIMIT %d,%d",
$start - 1, # number of records to skip
$per_page + 1); # number of records to select
} elsif ($type eq '') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley
where keywords like '%$search%'
ORDER BY name LIMIT %d,%d",
$start - 1, # number of records to skip
$per_page + 1); # number of records to select
}
my $tbl_ref = $dbh->selectall_arrayref ($query);

$dbh->disconnect ( );

# Line 139 is the FOR below......

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2) {
# get data values in row $i
my @cells = @{$tbl_ref->[$i]}; # get data values in row $i
my @cells2 = @{$tbl_ref->[$i+1]}; # get data values in row $i+1
# map values to HTML-encoded values, or to   if null/empty
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells;
@cells2 = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells2;
# add cells to table
my @cells = "$cells[0]
$cells[1]
$cells[2]

$cells[3]
";
my @cells2 = "$cells2[0]
$cells2[1]
$cells2[2]

$cells2[3]
";
push (@rows, Tr (td (\@cells),(td (\@cells2))));
}

I get the error 'Can't use an undefined value as an ARRAY reference at
search.cgi line 139.'

My questions, why would adding that code at the beginning cause that
error and how can I fix it?

Any ideas/suggestions greatly appreciated.
Jim

Re: Can"t use an undefined value as an ARRAY reference at search.cgiline 139.

JimJx wrote:
> Hi all,
>
> I have what seems to me to be a strange problem....
>
> I have the script below:

Which isn't terribly helpful, because you don't say what $type,
$start, etc. is so we have no idea what SQL is being tried.
[...]
> My questions, why would adding that code at the beginning cause that
> error and how can I fix it?
>
> Any ideas/suggestions greatly appreciated.

Look at your SQL and the results.

use Data::Dumper;

#... your code
print "SQL=$query\n";
my $tbl_ref = $dbh->selectall_arrayref ($query);
print 'Results:', Dumper( $tbl_ref );

Something isn't what you think it is.

Re: Can"t use an undefined value as an ARRAY reference at search.cgi line 139.

On Sep 11, 2:15 pm, JimJx wrote:

> It works great. However if I change it to:
>
> if ($search = '#') {

You realize of course that this line *assigns* $search to be equal to
'#', and then returns true, meaning that this if statement (and none
of the else clauses) will ALWAYS be executed, right?

You meant to have an 'eq' here, just like all the other if conditions
had, not an '='.

> my $tbl_ref = $dbh->selectall_arrayref ($query);
>
> $dbh->disconnect ( );
>
> # Line 139 is the FOR below......
>
> for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2) {

> I get the error 'Can't use an undefined value as an ARRAY
> reference at search.cgi line 139.'

You are not checking your DBI calls for errors. You are assuming they
all work fine. Perl doesn't tell you there's a problem unless you ask
it to. I recommend turning on RaiseError in your connect statement

my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1} );

Or you can turn it on after the fact:
$dbh->{RaiseError} = 1;

Or you can manually check the results of every single database call:
my $tbl_ref = $dbh->selectall_arrayref($query);
die $dbh->errstr if $dbh->err();


Paul Lalli