Hello Fellow Monks, It’s been a while since I’ve program, but a good friend of mine asked me to put together a small database to track blackberry inventory. I agreed to do it and with the best language of course. The only problem, I’m a little rusty and will need your expert advice.

It’s an Access DB, the script works fine with any search string except with status=DEPLOYED or emp_user_name=*, every other search string will return no more than 50 resulst, the ones that are failing will return around 400 results. I’m sure I’m going the wrong way about querying and displaying the records.

Any advice or help that you can provide is always appreciated! 
# vars for query
my ($sql_bb_activity_code,$sql_bb_model,$sql_bb_pin,$sql_bb_phone,$sql
+_bb_imei_esn_dec,$sql_bb_status,$sql_bb_region_code)=@_;

if ($emp_user_name eq "*")
{
    ($statement)="SELECT bb_id, emp_user_name, bb_activity_code, bb_mo
+del, bb_pin, bb_phone, bb_imei_esn_dec, bb_status, bb_region_code FRO
+M blackberry ORDER BY emp_user_name";
}
elsif (($emp_user_name or $bb_activity_code or $bb_model or $bb_pin or
+ $bb_phone or $bb_imei_esn_dec or $bb_status or $bb_region_code) and 
+($emp_user_name ne "*"))
{
    if ($bb_activity_code ne "") {
        $sql_bb_activity_code =" AND bb_activity_code LIKE '%$bb_activ
+ity_code%'";
    }
    if ($bb_model ne "") {
        $sql_bb_model =" AND bb_model='$bb_model'";
    }
    if ($bb_pin ne "") {
        $sql_bb_pin =" AND bb_pin LIKE '%$bb_pin%'";
    }
    if ($bb_phone ne "") {
        $sql_bb_phone =" AND bb_phone LIKE '%$bb_phone%'";
    }
    if ($bb_imei_esn_dec ne "") {
        $sql_bb_imei_esn_dec =" AND bb_imei_esn_dec LIKE '%$bb_imei_es
+n_dec%'";
    }
    if ($bb_status ne "") {
        $sql_bb_status =" AND bb_status='$bb_status'";
    }
    if ($bb_region_code ne "") {
        $sql_bb_region_code = " AND bb_region_code='$bb_region_code'";
    }

    ($statement)="SELECT bb_id, emp_user_name, bb_activity_code, bb_mo
+del, bb_pin, bb_phone, bb_imei_esn_dec, bb_status, bb_region_code FRO
+M blackberry WHERE emp_user_name LIKE '%$emp_user_name%' $sql_bb_acti
+vity_code $sql_bb_model $sql_bb_pin $sql_bb_phone $sql_bb_imei_esn_de
+c $sql_bb_status $sql_bb_region_code ORDER BY emp_user_name";
}
else
{
    exit;
}


##################################################
# Connect to the database and send sql statement #
##################################################

my ($db)=new Win32::ODBC("DSN=cbsc;UID=;PWD=;");
if ($db) {
    $connection_status= "Connection Established!";
} else {
    $connection_status= "Unable to connect to database!";
}

$db->Sql("$statement");

push @error,$db->Error();

# Print out any errors
if (@error) {
    foreach (@error) {
        print "<BR>$_";
    }
}


#################
# PRINT RESULTS #
#################

while ($db->FetchRow()) {
    my (%dbrow) = $db->DataHash();

    $count++;

    push @grab_results, "
                        <TR VALIGN=_TOP BGCOLOR=\"EEEEEE\">
                            <TD><FONT SIZE=2><A href=\"javascript:popw
+in('view_bb.pl?bb_id=$dbrow{'bb_id'}', '$dbrow{'bb_id'}')\">$dbrow{'b
+b_id'}</A>&nbsp\;</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'emp_user_name'}&n
+bsp\;</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_activity_code'
+}&nbsp\;</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_model'}&nbsp\;
+</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_pin'}&nbsp\;</
+FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_phone'}&nbsp\;
+</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_imei_esn_dec'}
+&nbsp\;</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_status'}&nbsp\
+;</FONT></TD>
                            <TD><FONT SIZE=2>$dbrow{'bb_region_code'}&
+nbsp\;</FONT></TD>
                        </TR>
                        ";

}

print<<HTML_HERE;

    <b>Total = $count</b>
    <TABLE BORDER="0" CELLSPACING="1" CELLPADDING="1" WIDTH="711">
     <TR VALIGN="_TOP" BGCOLOR="#CCCCCC">
     <TD><FONT SIZE="2">BB ID</FONT></TD>
     <TD><FONT SIZE="2">EMP&nbsp;Username</FONT></TD>
     <TD><FONT SIZE="2">Activity&nbsp;Code</FONT></TD>
     <TD><FONT SIZE="2">Model</FONT></TD>
     <TD><FONT SIZE="2">PIN</FONT></TD>
     <TD><FONT SIZE="2">Phone</FONT></TD>
     <TD><FONT SIZE="2">IMEI/ESN(dec)</FONT></TD>
     <TD><FONT SIZE="2">Status</FONT></TD>
     <TD><FONT SIZE="2">Region</FONT></TD>
    </TR>


HTML_HERE



foreach (@grab_results)
{
    print "$_";
}

print "</TABLE>";

# Print out any errors
if (@error) {
    foreach (@error) {
        print "<BR>$_";
    }
}


# Print out the sql query
print "<BR><font color=FFFFFF>SQL: $statement </font>";


### Close ODBC connection
$db->Close();

[download]

Edit g0n - added readmore tags

In reply to Search breaks based on search string by Kiko

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.