Can't use an undefined value as an ARRAY reference at search.cgi line 1

[JimJx]

Hi all,

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

I have the script below:

<code>
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 &nbsp; if null/empty
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
} @cells;
@cells2 = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
} @cells2;
# add cells to table
my @cells = "<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>
$cells[3]<br>";
my @cells2 = "<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>
$cells2[3]<br>";
push (@rows, Tr (td (\@cells),(td (\@cells2))));
}

</code>

It works great. However if I change it to:
<code>
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 &nbsp; if null/empty
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
} @cells;
@cells2 = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
} @cells2;
# add cells to table
my @cells = "<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>
$cells[3]<br>";
my @cells2 = "<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>
$cells2[3]<br>";
push (@rows, Tr (td (\@cells),(td (\@cells2))));
}

</code>
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

 

[brigmar]

I can see two things off the top of my head:
(1) Your if statement should be using 'eq' for comparison.
(2) Your SQL (mySQL??) syntax is incorrect for the REGEXP operator. You're not telling it what to match the pattern against. This would cause a syntax error, and therefore no rows returned. Had you checked for errors, this would have made itself apparent.

Assuming you are trying to match numbers against the 'phone' field, try this:
"SELECT name, address, city, phone FROM valley where phone REGEXP '^[0-9]' ORDER BY name LIMIT %d,%d

 

[KevinADC]

brigmar means this line:

if ($search = '#') {

you have the assingment operator '=' where you should be using the 'eq' operator.

------------------------------------------
- Kevin, perl coder unexceptional!

 

[JimJx]

Good catch on the eq, I completely blew that one....

And this is MySQL, so the regex command is REGEXP '^[[:digit:]]' to match any number.

Thanks for the help guys, greatly appreciated.
Jim

 

[brigmar]

JimJx,

I understand the character class, but you still need an expression to the left of 'REGEXP' to match your pattern against, no ?

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_regexp

(and I realize we're straying on to mySQL here)

 

[JimJx]

Yes, the complete code for that line is
FROM valley where name REGEXP '^[[:digit:]]'

So I have the complete query as

$query = sprintf (
   "SELECT name, address, city, phone
    FROM valley where name REGEXP '^[[:digit:]]'
    ORDER BY name LIMIT %d,%d",
    $start - 1,
    $per_page + 1);

It is working great....
Jim