Mega Code Archive
Dynamic WHERE CLAUSE depending on number of FORM FIELDS
<![CDATA[
<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>
]]>