title: Retrieve and combining similar items in mysql
body: <p>I have a major dilemna. I have a search that populates a search based upon a user search or a user clicking on a category. When a search is made or a category like business or leadership is clicked, sub categories appear. </p>
  
  <p>And I want the following behaviour to occur:
  When you click on a particular sub category, it decreases the amount of results. Like lets say result was 100, and you hit html, it would be 10, but lets say you checked on multiple sub categories, then the sub categories gets added up together so let say html had 10 courses, then css 30, and you hit on both than 40 would be display. </p>
  
  <p>At it current state, it does not combine them, rather it only considered the last checkbox checked, for instance if html is checked then css, it would look at css only when both should take into account.</p>
  
  <p>Below is the portion that covers the sub categories:</p>
  
  <pre><code>//sub categories
   $sub1Arr = array();
  if (!empty($category1)) $sub1Arr[] = $category1;
  if (count($sub1Arr)&gt;0) {
  
     $get_crs_mysqli_count .= " AND  (course_subc1 IN ('".implode("','", $sub1Arr)."')) ";
  
   }
  
    $sub2Arr = array();
  if (!empty($category2)) $sub2Arr[] = $category2;
  if (count($sub2Arr)&gt;0) {
  
    $get_crs_mysqli_count .= " AND  (course_subc2 IN ('".implode("','", $sub2Arr)."')) ";
   }
  
    $sub3Arr = array();
  if (!empty($category3)) $sub3Arr[] = $category3;
  if (count($sub3Arr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_subc3 IN ('".implode("','", $sub3Arr)."')) ";
   }
  
    $sub4Arr = array();
  if (!empty($category4)) $sub4Arr[] = $category4;
  if (count($sub4Arr)&gt;0) {
  
    $get_crs_mysqli_count .= " AND  (course_subc4 IN ('".implode("','", $sub4Arr)."')) ";
   }
     $sub5Arr = array();
  if (!empty($category5)) $sub5Arr[] = $category5;
  if (count($sub5Arr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_subc5 IN ('".implode("','", $sub5Arr)."')) ";
   }
  </code></pre>
  
  <p>Below is a larger portion of the code:</p>
  
  <pre><code>$get_crs_mysqli_count = "
      SELECT * 
      FROM courses 
      WHERE course_date1 &gt;= CURRENT_DATE() ";
      if(!empty($_SESSION['userSearch']))
  {
      $get_crs_mysqli_count .= " AND (course_title like '%" . $_SESSION['userSearch'] . "%') ";
  }
  elseif(!empty($_SESSION['userCategory']))
  {
     $get_crs_mysqli_count .= " AND ((course_cat1 like '%" . $_SESSION['userCategory'] . "%') OR (course_cat2 like '%" . $_SESSION['userCategory'] . "%')) ";
  
  }
  
  elseif(!empty($_SESSION['userDate']))
  {
     $get_crs_mysqli_count .= " AND ((course_date1 like '%" . $_SESSION['userDate'] . "%') OR (course_date2 like '%" . $_SESSION['userDate'] . "%')) ";
  
  }
  
  elseif(!empty($_SESSION['userProvider']))
  {
     $get_crs_mysqli_count .= " AND (course_provider like '%" . $_SESSION['userProvider'] . "%') ";
  
  }
  
  elseif(!empty($_SESSION['userCity']))
  {
      $get_crs_mysqli_count .= " AND (course_city like '%" . $_SESSION['userCity'] . "%') ";
  
  }
  
  elseif(!empty($_SESSION['userPrice']))
  {
     $get_crs_mysqli_count .= " AND (course_price like '%" . $_SESSION['userPrice'] . "%') ";
  }
  
  elseif(!empty($_SESSION['userSub']))
  {
      $get_crs_mysqli_count .= " AND ((course_subc1 like '%" . $_SESSION['userSub'] . "%') OR (course_subc2 like '%" . $_SESSION['userSub'] . "%') or  (course_subc3 like '%" . $_SESSION['userSub'] . "%') or (course_subc4 like '%" . $_SESSION['userSub'] . "%') or  (course_subc5 like '%" . $_SESSION['userSub'] . "%')) ";
  
  }
  
  
  
  $durationArr = array();
  if (!empty($duration1)) $durationArr[] = $duration1;
  if (!empty($duration2)) $durationArr[] = $duration2;
  if (!empty($duration3)) $durationArr[] = $duration3;
  if (!empty($duration4)) $durationArr[] = $duration4;
  if (!empty($duration5)) $durationArr[] = $duration5;
  if (count($durationArr)&gt;0) {
     $get_crs_mysqli_count .= " AND  (course_duration IN ('".implode("','", $durationArr)."')) ";
   }
  
   //From Price
    $fromPriceArr = array();
  if (!empty($fromPrice)) $fromPriceArr[] = $fromPrice;
  if (count($fromPriceArr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_priceFinal &gt;= ('".implode("','", $fromPriceArr)."')) ";
   }
  
  
  //To Price
    $toPriceArr = array();
  if (!empty($toPrice)) $toPriceArr[] = $toPrice;
  if (count($toPriceArr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_priceFinal &lt;= ('".implode("','", $toPriceArr)."')) ";
   }
  
  //start date
    $startArr = array();
  if (!empty($startDate)) $startArr[] = $startDate;
  if (count($startArr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_date1 &gt;= ('".implode("','", $startArr)."')) ";
   }
  
  //end date
    $endArr = array();
  if (!empty($endDate)) $endArr[] = $endDate;
  if (count($endArr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_date1 &lt;= ('".implode("','", $endArr)."')) ";
   }
  
  
  //sub categories
   $sub1Arr = array();
  if (!empty($category1)) $sub1Arr[] = $category1;
  if (count($sub1Arr)&gt;0) {
  
     $get_crs_mysqli_count .= " AND  (course_subc1 IN ('".implode("','", $sub1Arr)."')) ";
  
   }
  
    $sub2Arr = array();
  if (!empty($category2)) $sub2Arr[] = $category2;
  if (count($sub2Arr)&gt;0) {
  
    $get_crs_mysqli_count .= " AND  (course_subc2 IN ('".implode("','", $sub2Arr)."')) ";
   }
  
    $sub3Arr = array();
  if (!empty($category3)) $sub3Arr[] = $category3;
  if (count($sub3Arr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_subc3 IN ('".implode("','", $sub3Arr)."')) ";
   }
  
    $sub4Arr = array();
  if (!empty($category4)) $sub4Arr[] = $category4;
  if (count($sub4Arr)&gt;0) {
  
    $get_crs_mysqli_count .= " AND  (course_subc4 IN ('".implode("','", $sub4Arr)."')) ";
   }
     $sub5Arr = array();
  if (!empty($category5)) $sub5Arr[] = $category5;
  if (count($sub5Arr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_subc5 IN ('".implode("','", $sub5Arr)."')) ";
   }
  
  
  //city
    $cityArr = array();
  if (!empty($city)) $cityArr[] = $city;
  if (count($cityArr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_city IN ('".implode("','", $cityArr)."')) ";
   }
  
  //delivery
     $delivery1Arr = array();
  if (!empty($delivery1)) $delivery1Arr[] = $delivery1;
  if (count($delivery1Arr)&gt;0) {
  
     $get_crs_mysqli_count .= " AND  (course_delivery IN ('".implode("','", $delivery1Arr)."')) ";
   }
  
     $delivery2Arr = array();
  if (!empty($delivery2)) $delivery2Arr[] = $delivery2;
  if (count($delivery2Arr)&gt;0) {
  
      $get_crs_mysqli_count .= " AND  (course_delivery2 IN ('".implode("','", $delivery2Arr)."')) ";
   }
  
   //provider
  
  $providerArr = array();
  if (!empty($provider1)) $providerArr[] = $provider1;
  if (!empty($provider2)) $providerArr[] = $provider2;
  if (!empty($provider3)) $providerArr[] = $provider3;
  if (!empty($provider4)) $providerArr[] = $provider4;
  if (!empty($provider5)) $providerArr[] = $provider5;
  
  if (count($providerArr)&gt;0) {
      $get_crs_mysqli_count .= " AND  (course_provider IN ('".implode("','", $providerArr)."')) ";
   }
  
  
  $guaranteeArr = array();
  if (!empty($guarantee1)) $guaranteeArr[] = $guarantee1;
  if (!empty($guarantee2)) $guaranteeArr[] = $guarantee2;
  if (count($guaranteeArr)&gt;0) {
      $get_crs_mysqli_count.= " AND  (course_guarantee IN ('".implode("','", $guaranteeArr)."')) ";
   }
  
  
  $get_crs_mysqli_count .= "
      ORDER BY course_date1 ASC ";
  </code></pre>
  
  <p>Note it is vital that the sub categories work in collaboration with the other filters.</p>
  
  <p>Update:</p>
  
  <pre><code>//sub categories
   $sub1Arr = array();
    $sub2Arr = array();
  
   $sub3Arr = array();
  
   $sub4Arr = array();
  
   $sub5Arr = array();
  
  if (!empty($category1)) $sub1Arr[] = $category1;
  if (!empty($category2)) $sub2Arr[] = $category2;
  if (!empty($category3)) $sub3Arr[] = $category3;
  if (!empty($category4)) $sub4Arr[] = $category4;
  if (!empty($category5)) $sub5Arr[] = $category5;
  
  
  
  if (count($sub1Arr)&gt;0) {
  
     $get_crs_mysqli_count .= " AND  ((course_subc1 IN ('".implode("','", $sub1Arr)."')) OR  (course_subc2 IN ('".implode("','", $sub2Arr)."'))
     or (course_subc3 IN ('".implode("','", $sub3Arr)."')) or  (course_subc4 IN ('".implode("','", $sub4Arr)."'))  or (course_subc5 IN ('".implode("','", $sub5Arr)."')))";
  
   }
  </code></pre>