Php > Databases

Dynamic WHERE CLAUSE depending on number of FORM FIELDS

Dynamic WHERE CLAUSE depending on number of FORM FIELDS

<HTML> 
<BODY BACKGROUND="/images/bkjaune.gif"> 
<? 

     function Error_Handler( $msg, $cnx ) 
     { 
             echo "$msg \n"; 
                     // in case of persistent connexion, it is important to close it before exiting. 
             odbc_close( $cnx); 
             exit(); 
     } 



             // create an ODBC connection, returned in $cnx 
             $cnx = odbc_connect( 'INSTANCE' , 'user', 'pass' ); 

$sqlquery=" 

SELECT 
       pof_a, 
       pof_nm, 
       pof_chnm, 
       pof_mr, 
       pof_dept, 
       pof_labo, 
       pof_adr1, 
       pof_adr2, 
       pof_pobox, 
       pof_pc1, 
       pof_tnm, 
       cy_cynm, 
       pof_org 
FROM 
       ampere.fi_pof,ampere.fi_cy 

WHERE 

       pof_dpc = 'RBG4' 
AND 
       substr(pof_a,4,4) = '4003' 

AND 
       cy_code = pof_cy 
AND 
       cy_lge = 'EN' 
"; 

if ( $GCODE ){ 
$wherecl1 =  " AND pof_a in (select ept_a from ampere.fi_ept where ept_a = pof_a and ept_ept = '$GCODE')"; 
} 

if ($MCODE){ 
$wherecl2 = " AND pof_a in (select ept_a from ampere.fi_ept where ept_a = pof_a and ept_ept = '$MCODE')"; 
} 

if ($LCODE){ 
$wherecl3 = " AND pof_a in (select ept_a from ampere.fi_ept where ept_a = pof_a and ept_ept = '$LCODE')"; 
} 

if ($GRPCY){ 
$wherecl4 = " AND pof_cy in (select lib_lib1 from ampere.fi_lib where lib_table = 'GRPCY' and lib_lge = 'XX' and 
lib_code = '$GRPCY')"; 
} 

if ($CYCODE){ 
$wherecl5 = " AND pof_cy = '$CYCODE'"; 
} 

if ($YEAR){ 
$wherecl6 = " AND TO_CHAR(pof_dcrea) > '01-$MONTH-$YEAR'"; 
} 

if ($USED){ 
$wherecl7 = " AND pof_a in (select ept_a from ampere.fi_ept where ept_a = pof_a and ept_ept = '$USED')"; 
} 

if ($SURNAME){ 
$wherecl8 = " AND pof_nm like upper('%$SURNAME%')"; 
} 

if ($EXPNUMBER){ 
$wherecl9 = " AND pof_a like '%$EXPNUMBER%'"; 
} 


     
$sql = $sqlquery . $wherecl1 . $wherecl2 . $wherecl3 . $wherecl4 . $wherecl5 . $wherecl6 . $wherecl7 . $wherecl8 . 
$wherecl9 ; 

//print $sql; 


             if( ! $cnx ) { 
                     Error_handler( "Error in odbc_connect" , $cnx ); 
             } 

             // send a simple odbc query . returns an odbc cursor 
             $cur= odbc_exec( $cnx, $sql); 



             if( ! $cur ) { 
                     Error_handler( "Error in odbc_exec( no cursor returned ) " , $cnx ); 
             } 


echo "<CENTER><H2>Evaluation Expert(s) matching your criteria</H2></CENTER>\n"; 
echo "<CENTER><H5>SURNAME=($SURNAME) EXPNUMBER=($EXPNUMBER) GCODE=($GCODE) 
MCODE=($MCODE) 
LCODE=($LCODE) CYCODE=($CYCODE) GRPCY=($GRPCY) USED=($USED) </H5></CENTER>\n"; 
echo "<CENTER><TABLE BORDER=1>\n"; 

             $nbrow=0; 

                     // fetch the succesive result rows 
             while( odbc_fetch_row( $cur ) ) 
             { 
                       $nbrow++; 
                       $pof_a= odbc_result( $cur, 1 ); 
                         $pof_nm= odbc_result( $cur, 2 ); 
                         $pof_chnm= odbc_result( $cur, 3 ); 
                         $pof_mr= odbc_result( $cur, 4 ); 
                         $pof_debt= odbc_result( $cur, 5 ); 
                         $pof_labo= odbc_result( $cur, 6 ); 
                 $pof_adr1= odbc_result( $cur, 7 ); 
            $pof_adr2= odbc_result( $cur, 8 ); 
            $pof_pobox= odbc_result( $cur, 9 ); 
            $pof_pc1= odbc_result( $cur, 10 ); 
            $pof_tnm= odbc_result( $cur, 11 ); 
            $cy_cynm= odbc_result( $cur, 12 ); 
            $pof_org= odbc_result( $cur, 13 ); 
             

echo "<tr><td width=140 units=pixels><h4><A HREF=\"/expdet.php3?$pof_a\">$pof_a</A></h4></td>\n"; 
echo "<td width=400 units=pixels><H5>$pof_mr  $pof_nm $pof_chnm<BR>$pof_org<BR>$pof_debt<BR> $pof_labo <BR> $pof_adr1    
$pof_adr2 $pof_pobox <BR> $pof_pc1 $pof_tnm <BR> $cy_cynm</H5></td></tr>\n"; 
} 

echo "<tr><td colspan=2>$nbrow entries </td></tr></TABLE></CENTER>\n";              
// close the connection. important if persistent connection are "On" 
odbc_close( $cnx); 

     ?> 

</BODY> 
</HTML>