Here is a slick little function that I wrote the other day at work to create a filter on a table.

Pseudo code:

Requirements: The argument names must match the table column names.

Results: Returns a query of the filter table data.

Determine which arguments were passed into the function and add those into the query where statement.

ColdFusion CFML Version:

Code:

<cffunction name="filter" access="public" output="true" returntype="query">
	<cfargument name="column_name_1" required="no">
	<cfargument name="column_name_2" required="no">
	<cfargument name="column_name_3" required="no">
	
	<cfquery name="qfiles" >
		SELECT *	FROM tablename WHERE 1=1
			<cfloop array="#StructKeyArray( arguments )#" index="index">					
				<cfif isDefined( "Arguments.#index#" ) and Len( "#arguments[index]#" ) gt 2 >
					<cfset v="#arguments[index]#">
					AND #index# like <cfqueryparam value = "#v#" >
				</cfif>
			</cfloop>
	</cfquery>
	<cfreturn qfiles> 
</cffunction>

PHP Version:

Pseudo code:

Requirements: The argument is an array of column names to filter on.

Results: Returns a query of the filter table data.

Determine which arguments were passed into the function and add those into the query where statement.

function filter( $arguments ) {
    $keys = array( 'user_id', 'user_type', 'username' );
         
    $sql = 'SELECT * FROM table_name WHERE 1=1 ';
    foreach( $arguments as $key=>$value ) {
        if( isset( $arguments[$key] ) ) {
            if( strpos( $arguments[$key], "%" ) ) {
                $sql .= " AND $key like '$value' ";
            } else {
                $sql .= " AND $key = '$value' ";
            }
        }
    }
    return $sql.";";
}

Conclusion

Because there are so many different database connectors in php I returned the SQL instead of the query results. The same could be done in ColdFusion, but this way seemed a little less wordy. I will probably not use the PHP version very much since it is already implemented through the CodeIgnitor framework.