[php] 검색을 단순화해서 만들 수 있는 코드? 가능할까?

필드가 많아지고, 검색을 다 걸어야 하는 경우....

코드가 너무 길어진다. 실제로 내가 만든 검색을 위한 코드인데.... 어마어마하게 길다.

// 검색을 위해 모든 검색변수 공백제거
$search = str_replace(' ', '', $search);

	  if($search=="" && $findstr==="전체")
		  {
			 $sql="select * from mirae8440.work " . $whereattached . $a; 					
			 $sqlcon = "select * from mirae8440.work "  . $whereattached .  $b;   // 전체 레코드수를 파악하기 위함.
		   }
		 elseif($search!="" && $find!="all" && $findstr==="전체")
			{
				$sql="select * from mirae8440.work where ($find like '%$search%') " . $attached . $a;
				$sqlcon="select * from mirae8440.work where ($find like '%$search%')  "  . $attached . $b;
			 }     				 
		 elseif($findstr!=="전체") 
		     {
				// 공사유형이 전체가 아닐때
				// 검색어가 2개일경우
		    if($findstr=='덧방')
				   $searchstr = '';
			   else
				   $searchstr = '신규';
			   
			if(count($Twosearchword)>1)
			   {				   
					  if($check!='1') 
						  $attached = '';
					  
						  $search1 = $Twosearchword[0];
						  $sql ="select * from mirae8440.work where ( (replace(workplacename,' ','') like '%$search1%' ) or (firstordman like '%$search1%' )   or  (firstordmantel like '%$search1%' )  or (secondordman like '%$search1%' )  or (chargedman like '%$search1%' ) ";
						  $sql .="or (delicompany like '%$search1%' ) or (attachment like '%$search1%' )  or (hpi like '%$search1%' ) or (firstord like '%$search1%' ) or (secondord like '%$search1%' ) or (worker like '%$search1%' ) or (memo like '%$search1%' ) or (material1 like '%$search1%' ) or (material2 like '%$search1%' ) or (material3 like '%$search1%' ) or (material4 like '%$search1%' ) or (material5 like '%$search1%' ) or (material6 like '%$search1%' ))  and ( checkstep='$searchstr' )  " ;
						  $search2 = $Twosearchword[1];
						  $sql .= " and ( (replace(workplacename,' ','') like '%$search2%' ) or (firstordman like '%$search2%' )   or (secondordman like '%$search2%' )  or (secondordmantel like '%$search2%' )   or (chargedman like '%$search2%' ) or (chargedtel like '%$search2%' )";
						  $sql .= "or (delicompany like '%$search2%' ) or (attachment like '%$search2%' )  or (hpi like '%$search2%' ) or (firstord like '%$search2%' ) or (secondord like '%$search2%' ) or (worker like '%$search2%' ) or (memo like '%$search2%' ) or (material1 like '%$search2%' ) or (material2 like '%$search2%' ) or (material3 like '%$search2%' ) or (material4 like '%$search2%' ) or (material5 like '%$search2%' ) or (material6 like '%$search2%' ))   and ( checkstep='$searchstr' )  " . $attached . $a;
						  
						  $sqlcon ="select * from mirae8440.work where ( (replace(workplacename,' ','') like '%$search1%' ) or (firstordman like '%$search1%' )   or  (firstordmantel like '%$search1%' )  or (secondordman like '%$search1%' )  or (secondordmantel like '%$search1%' )  or (chargedman like '%$search1%' ) ";
						  $sqlcon .="or (delicompany like '%$search1%' ) or (attachment like '%$search1%' )  or (hpi like '%$search1%' ) or (firstord like '%$search1%' ) or (secondord like '%$search1%' ) or (worker like '%$search1%' ) or (memo like '%$search1%' ) or (material1 like '%$search1%' ) or (material2 like '%$search1%' ) or (material3 like '%$search1%' ) or (material4 like '%$search1%' ) or (material5 like '%$search1%' ) or (material6 like '%$search1%' ))   and ( checkstep='$searchstr' )  " ;							  
						  $sqlcon .= " and ( (replace(workplacename,' ','') like '%$search2%' ) or (firstordman like '%$search2%' )   or (secondordman like '%$search2%' )  or (secondordmantel like '%$search2%' )   or (chargedman like '%$search2%' ) or (chargedtel like '%$search2%' )";
						  $sqlcon .= "or (delicompany like '%$search2%' ) or (attachment like '%$search2%' )  or (hpi like '%$search2%' ) or (firstord like '%$search2%' ) or (secondord like '%$search2%' ) or (worker like '%$search2%' ) or (memo like '%$search2%' ) or (material1 like '%$search2%' ) or (material2 like '%$search2%' ) or (material3 like '%$search2%' ) or (material4 like '%$search2%' ) or (material5 like '%$search2%' ) or (material6 like '%$search2%' ))   and ( checkstep='$searchstr' )  " . $attached . $b;						  
					 
	   
				}   // end of Twosearchword searching
			  else {   // end of one word
					  if($check!='1') {		 
						  $sql ="select * from mirae8440.work where ((replace(workplacename,' ','') like '%$search%' ) or (firstordman like '%$search%' )  or (firstordmantel like '%$search%' )  or (secondordman like '%$search%' )   or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sql .="or (delicompany like '%$search%' ) or (attachment like '%$search%' )  or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' ) or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' ))   and ( checkstep='$searchstr' )   " . $a;
						  
						  $sqlcon ="select * from mirae8440.work where ((replace(workplacename,' ','') like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' )  or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sqlcon .="or (delicompany like '%$search%' )  or (attachment like '%$search%' )  or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' ))   and ( checkstep='$searchstr' )   " . $b;
					  }
					  
				 if($check=='1' || $output_check=='1' || $measure_check=='1' || $plan_output_check=='1' || $team_check=='1') {			  
						  $sql ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' )   or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sql .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstordman like '%$search%' )  or (firstordmantel like '%$search%' )  or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  )   and ( checkstep='$searchstr' ) "  . $attached . $a;
						  
						  $sqlcon ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordman like '%$search%' ) or (firstordmantel like '%$search%' )  or (secondordmantel like '%$search%' )   or (firstordman like '%$search%' )  or (secondordman like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sqlcon .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  )   and ( checkstep='$searchstr' ) "  . $attached . $b;
					  }				  
	   
				}   // end of Twosearchword searching
			  }	  	
			  
		 elseif($search!="" && $find=="all") { // 필드별 검색하기
		   // 검색어가 2개일경우
			if(count($Twosearchword)>1)
			   {				   
					  if($check!='1') 
						  $attached = '';
					  
						  $search1 = $Twosearchword[0];
						  $sql ="select * from mirae8440.work where ( (replace(workplacename,' ','') like '%$search1%' ) or (firstordman like '%$search1%' )   or  (firstordmantel like '%$search1%' )  or (secondordman like '%$search1%' )  or (secondordmantel like '%$search1%' )  or (chargedman like '%$search1%' ) ";
						  $sql .="or (delicompany like '%$search1%' ) or (attachment like '%$search1%' )  or (hpi like '%$search1%' ) or (firstord like '%$search1%' ) or (secondord like '%$search1%' ) or (worker like '%$search1%' ) or (memo like '%$search1%' ) or (material1 like '%$search1%' ) or (material2 like '%$search1%' ) or (material3 like '%$search1%' ) or (material4 like '%$search1%' ) or (material5 like '%$search1%' ) or (material6 like '%$search1%' ))  " ;
						  $search2 = $Twosearchword[1];
						  $sql .= " and ( (replace(workplacename,' ','') like '%$search2%' ) or (firstordman like '%$search2%' ) or (firstordmantel like '%$search2%' )   or (secondordman like '%$search2%' )  or (secondordmantel like '%$search2%' )   or (chargedman like '%$search2%' ) or (chargedtel like '%$search2%' )";
						  $sql .= "or (delicompany like '%$search2%' ) or (attachment like '%$search2%' )  or (hpi like '%$search2%' ) or (firstord like '%$search2%' ) or (secondord like '%$search2%' ) or (worker like '%$search2%' ) or (memo like '%$search2%' ) or (material1 like '%$search2%' ) or (material2 like '%$search2%' ) or (material3 like '%$search2%' ) or (material4 like '%$search2%' ) or (material5 like '%$search2%' ) or (material6 like '%$search2%' ))  " . $attached . $a;
						  
						  $sqlcon ="select * from mirae8440.work where ( (replace(workplacename,' ','') like '%$search1%' ) or (firstordman like '%$search1%' )   or  (firstordmantel like '%$search1%' )  or (secondordman like '%$search1%' )  or (chargedman like '%$search1%' ) ";
						  $sqlcon .="or (delicompany like '%$search1%' ) or (attachment like '%$search1%' )  or (hpi like '%$search1%' ) or (firstord like '%$search1%' ) or (secondord like '%$search1%' ) or (worker like '%$search1%' ) or (memo like '%$search1%' ) or (material1 like '%$search1%' ) or (material2 like '%$search1%' ) or (material3 like '%$search1%' ) or (material4 like '%$search1%' ) or (material5 like '%$search1%' ) or (material6 like '%$search1%' ))  " ;							  
						  $sqlcon .= " and ( (replace(workplacename,' ','') like '%$search2%' ) or (firstordman like '%$search2%' )   or (secondordman like '%$search2%' )  or (secondordmantel like '%$search2%' )   or (chargedman like '%$search2%' ) or (chargedtel like '%$search2%' )";
						  $sqlcon .= "or (delicompany like '%$search2%' ) or (attachment like '%$search2%' )  or (hpi like '%$search2%' ) or (firstord like '%$search2%' ) or (secondord like '%$search2%' ) or (worker like '%$search2%' ) or (memo like '%$search2%' ) or (material1 like '%$search2%' ) or (material2 like '%$search2%' ) or (material3 like '%$search2%' ) or (material4 like '%$search2%' ) or (material5 like '%$search2%' ) or (material6 like '%$search2%' ))  " . $attached . $b;						  					 	   
				}   // end of Twosearchword searching
			  else {   // end of one word
					  if($check!='1') {		 
						  $sql ="select * from mirae8440.work where (replace(workplacename,' ','') like '%$search%' ) or (firstordmantel like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' )  or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sql .="or (delicompany like '%$search%' ) or (attachment like '%$search%' )  or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' ) or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  " . $a;
						  
						  $sqlcon ="select * from mirae8440.work where (replace(workplacename,' ','') like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' ) or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sqlcon .="or (delicompany like '%$search%' )  or (attachment like '%$search%' )  or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  " . $b;
					  }
					  
				 if($check=='1' || $output_check=='1' || $measure_check=='1' || $plan_output_check=='1' || $team_check=='1') {			  
						  $sql ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordmantel like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' )  or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sql .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  ) "  . $attached . $a;
						  
						  $sqlcon ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordmantel like '%$search%' )   or (firstordman like '%$search%' )  or (secondordmantel like '%$search%' )   or (secondordman like '%$search%' )  or (chargedman like '%$search%' ) ";
						  $sqlcon .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  ) "  . $attached . $b;
					  }				  
	   
				}   // end of Twosearchword searching
			  }

머리가 아주 빠질 지경이다.....

조건에 따른 문장을 계속 만들다 보니.. 어느덧...

이제 코드를 좀 단순화 하는 과정의 노력을 해야겠다.

위의 코드를 일부 단순화했더니... 코드가 많이 줄었다.

여러 조건문을 사용하여 SQL 쿼리를 구성하는 방식을 변경하여 코드를 리팩터링할 것입니다.
각 조건에 따라 SQL의 다른 부분을 작성하는 대신, 모든 조건에 공통적인 부분을 하나의 함수에서 처리하도록 할 것입니다.동적으로 WHERE 절을 구성하는 데 필요한 조건들을 배열로 수집하고, 이를 사용하여 SQL을 구성할 것입니다.

function buildSQL($base, $conditions, $suffix = '') {
    $whereClause = implode(' AND ', $conditions);
    if ($whereClause) {
        $whereClause = ' WHERE ' . $whereClause;
    }
    return $base . $whereClause . $suffix;
}

$searchConditions = [];
if ($search != "") {
    $fields = [
        'workplacename', 'firstordman', 'firstordmantel', 'secondordman', 'secondordmantel',
        'chargedman', 'delicompany', 'attachment', 'hpi', 'firstord', 'secondord',
        'worker', 'memo', 'material1', 'material2', 'material3', 'material4', 'material5', 'material6'
    ];
    
    foreach ($fields as $field) {
        $searchConditions[] = "(replace($field, ' ', '') LIKE '%$search%')";
    }
}

if ($findstr !== "전체") {
    $searchstr = ($findstr == '덧방') ? '' : '신규';
    $searchConditions[] = "(checkstep = '$searchstr')";
}

if($check=='1' || $output_check=='1' || $measure_check=='1' || $plan_output_check=='1' || $team_check=='1') {			  
      $sql ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordmantel like '%$search%' )  or (firstordman like '%$search%' )  or (secondordman like '%$search%' )  or (secondordmantel like '%$search%' )  or (chargedman like '%$search%' )  or (chargedmantel like '%$search%' ) ";
      $sql .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  ) "  . $attached . $a;

      $sqlcon ="select * from mirae8440.work where ((replace(workplacename,' ','')  like '%$search%' )  or (firstordmantel like '%$search%' )   or (firstordman like '%$search%' )  or (secondordmantel like '%$search%' )   or (secondordman like '%$search%' )  or (chargedman like '%$search%' ) or (chargedmantel like '%$search%' ) ";
      $sqlcon .="or (delicompany like '%$search%' ) or (hpi like '%$search%' ) or (firstord like '%$search%' ) or (secondord like '%$search%' ) or (worker like '%$search%' ) or (memo like '%$search%' )  or (material1 like '%$search%' ) or (material2 like '%$search%' ) or (material3 like '%$search%' ) or (material4 like '%$search%' ) or (material5 like '%$search%' ) or (material6 like '%$search%' )  ) "  . $attached . $b;
  }	

$sqlBase = "SELECT * FROM mirae8440.work";
$sql = buildSQL($sqlBase, $searchConditions, $a);
$sqlcon = buildSQL($sqlBase, $searchConditions, $b);

 

뭔가 더 체계적이고 멋진 코드가 되어간다.

이 코드는 기존의 코드를 크게 단순화하면서도 동일한 기능을 유지합니다.
주요 아이디어는 SQL 쿼리의 다양한 조건을 배열로 관리하고,
이 배열을 기반으로 최종 쿼리를 동적으로 구성하는 것입니다.

멋진 코드를 위해 노력하자.