Multiple conditions based on form input

[Anon]

Any suggestions for this would be greatly appreciated:

Basically writing a search for a phone book where there are 4 input fields (first name, last name, city, and state)

I am able to pass the fields but want it to select the data that matches the inputs

ex. if first name and last name only entered (system and department are emtpy) it would search for those records with that first name and last name. (and not search for fields with empty city and state also)

Another example:

only last name and state are entered- therefore only want those records where last name and state matches input.

I have to write numerous queries for this so it will ignore the empty inputs. Wasn't sure if there was an easier (faster) way. Been searching and haven't come up with much.

Thanks again

[phpjuggler]

hi,
i wrote a function called MagicSQL. It generates SQL-Queries on the fly. Perhaps it may help you:
You call it this way:
$query = magicSQL("yourtablename","field1;fieldx","wherfield1;wherefieldx","AND","orderfield","none","none","none","none");

Greetings
juggler
function magicSQL($selectTable,
$selectFields,
$whereField,
$values,
$op="AND",
$orderField,
$selectChildTable="none",
$selectAliasFields="none",
$selectRelField="none",
$selectRelDataType = "none",
$selectGroupField = "none"
)
{

	// create the query			
	$magicQuery = "SELECT ";

	// get Fields to select
	$test = explode(";",$selectFields);
	// are there alias Fields or not ?
	// is the count of the fields the same as the alias field count ?

	if( ! strcmp($selectAliasFields,"none") == 0 )
	{
		//there are, perhaps
		$aFieldNames = explode(";",$selectAliasFields);

		if ( count($aFieldNames) == 0)
		{
			 $selectAliasFields = "none";
		}elseif( ! (count($aFieldNames) == count($test)) )
		{

			//debug 
			$selectFieldNames =  "none";


		}



	}

	// concatenate the fields to select with as to the query


	$counter = 0;
	while( each($test) )
	{
		if( $selectFieldNames == "none" )
		{
			$magicQuery .= $test[$counter].",";
		}else
		{
				$magicQuery .= $test[$counter] . " AS " . $aFieldNames[$counter] ." ".",";
		}
		$counter++;	 

	}

	//Terminate last comma from query
	$magicQuery =  substr_replace($magicQuery," ",strlen($magicQuery)-1);


	//from wich table ?
	// is there a child table ?
	if( ($selectChildTable == "none") || ($selectRelDataType == "none" ) )
	{
		$magicQuery .= "FROM ".$selectTable;
	}else
	{
		$magicQuery .= "FROM ".$selectTable. "," .$selectChildTable;
	}
	//put the where field to our query
	//if there is a child table create relation
	if(  ($selectChildTable == "none") || ($selectRelDataType == "none" ))
	{
		$magicQuery .= " WHERE ";
	}else
	{
		//check DataType of relation	
		if( $selectRelDataType == "integer" )
		{ 
			//is integer
			if($op == "AND")
			{
				$magicQuery .= " WHERE ". $selectChildTable . "." .  $selectRelField . "=" . $selectTable. "." . $selectRelField. " AND ";
			}elseif($op == "OR" )
			{

				$magicQuery .= " WHERE ". $selectChildTable . "." .  $selectRelField . "=" . $selectTable. "." . $selectRelField. " OR ";

			}


		}elseif( $selectRelDataType == "character" )
		{
			//is text
			if($op == "AND")
			{
				$magicQuery .= " WHERE ". $selectChildTable . "." .  $selectRelField . "=" . $selectTable. "." . $selectRelField. " AND ";
			}elseif($op == "OR" )
			{

				$magicQuery .= " WHERE ". $selectChildTable . "." .  $selectRelField . "=" . $selectTable. "." . $selectRelField. " OR ";

			}

		}else
		{


			$magicQuery .= " WHERE ";

		}	
	}
	// get Fields to select
	$test = explode(" ",$values);
	// there are no args given
	if($test[0]  == "bullshit")
	{

		unset($test);

	}			
	// concatenate the fields to select to the query
	$counter = 0;
	$counter2 = 0;
	while( @each($test) )
	{


		if( ! empty($test[$counter]) )
		{			
			if( $op == "AND")
			{
				$magicQuery .= $whereField . " LIKE('%$test[$counter]%') " . "AND ";
			}
			elseif( $op == "OR" )
			{
				$magicQuery .= $whereField . " LIKE('%$test[$counter]%') " . "OR ";
			}
			$counter2++;
		}
		$counter++;	 

	}
	// no parameters ?
	// give standart

	if( $counter2 > 0)
	{
		$magicQuery =  substr_replace($magicQuery," ",strlen($magicQuery)-4);

		if( ($selectGroupField == "none") && ($orderField == "none") )
		{ 

			//leave as it is


		}else
		{
					//give standard order and group statement
			    if( $selectGroupField == "none")
			    {										
					$magicQuery .= " ORDER  BY " . $orderField;
				}	
				elseif( $orderField == "none" )
				{

						$magicQuery .= " GROUP  BY " . $selectGroupField;

				}else
				{
						// All of them are filled
						$magicQuery .= " GROUP  BY " . $selectGroupField;
						$magicQuery .= " ORDER  BY " . $orderField;
				}
		}

	}
	else
	{
	//Add OrderField
	//	$magicQuery =  substr_replace($magicQuery," ",strlen($magicQuery)-4);
		$magicQuery = "novalues";
	}


	return  $magicQuery;

}