How to create a dynamic SQL query?

Question

That's right, so I have a set of dropdowns on my page. Depending on whether the value is selected, I want to add it to the SQL query string in PHP. Example:

select1: options("*" "op1", "op2)
select2: options("*" "op1", "op2)
select3: options("*" "op1", "op2)

'*' refers to everything. those. data should not be filtered with this query. Now, how can I build a query for this quickly and easily? I currently have something like this:

$query='';
$found=0;
$op1=$_POST['select1'];
$op2=$_POST['select2'];
$op3=$_POST['select3'];

if($op1!='*')
{
$found=1;
$op1="'".$op1."'";
$query="WHERE op1=$op1 ";

}

if($op2!='*')
{$op2="'".$op2."'";
if($found==1)
    {
    $query=$query. "AND op2=$op2 ";
    }
else{
    $found=1;
    $query="WHERE op2=$op2 ";
    }
}

if($op3!='*')
{$op3="'".$op3."'";
if($found==1)
    {
    $query=$query. "AND op3=$op3 ";
    }
else{
    $found=1;
    $query="WHERE op3=$op3 ";
    }
}

Now, obviously, this is very unpleasant to implement. Is there a simpler method?

Thank.

Answer 1

I would use the MyQueryBuilder class with the following methods, probably.

AddSelectionColumn(String ColumnName, string Alias)
AddTableSource(String TableName, String Alias)
AddTableJoin(String Table1, String Alias1, String Table2, String Alias2, String Col1, String Col2, JoinType Join)
AddFilterCondition(String ColumnName, String Alias, String Condition)

This may give better control over the code ...

Answer 2

CGI perl , /:

-, where 1 = 1, "" :

my $whereClause      =<<ENDWHERESQL;
where
   1 = 1
ENDWHERESQL

if ( $op1 ne "*" )    { $whereClause .= "     and op1 = '".safeSQL($op1)."'\n"; }
if ( $op2 ne "*" )    { $whereClause .= "     and op2 = '".safeSQL($op2)."'\n"; }
if ( $op3 ne "*" )    { $whereClause .= "     and op3 = '".safeSQL($op3)."'\n"; }

SQL-, , , - "? op1 = ( sql)" URL-, :

#******************************************************************************
# Function: safeSQL()
#   Author: Ron Savage
#     Date: 04/22/2009
# 
# Description:
# This removes update,create,drop,deletes from SQL.
#******************************************************************************
sub safeSQL
   {
   my $cmd;
   my ( $inText,$commandList ) = @_;

   if (!defined($commandList)) { $commandList = "create,delete,select,update,dele,drop,exec,insert"; }

   foreach $cmd (split(/\,/,$commandList))
      {
      $inText =~ s/ $cmd |^$cmd /** no_${cmd}_allowed! **/gi;
      }

   return($inText);
   }