comment on

Hello Fellow Monks, Its been a while since Ive 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, Im a little rusty and will need your expert advice.

Its 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

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


No such thing as a small change
	PerlMonks