title: CodeIgniter Search function
body: <p>I can't get this right.. </p>
  
  <p>In <strong>view file</strong> (view_searchlog.php):</p>
  
  <pre><code>&lt;div class="form-group"&gt;
           &lt;form class="form-inline" action="&lt;?php echo base_url(); ?&gt;index.php/search/accsearch" method="post"&gt;
          &lt;select class="form-control" name="field"&gt;
              &lt;option selected="selected" disabled="disabled" value=""&gt;Filter By&lt;/option&gt;
              &lt;option value="subjName"&gt;Subject&lt;/option&gt;
              &lt;option value="homeaddress"&gt;Location&lt;/option&gt;
              &lt;option value="lname"&gt;Last Name&lt;/option&gt;
          &lt;/select&gt;
          &lt;input class="form-control" type="text" name="search" value="" placeholder="Find.."&gt;
          &lt;input class="btn btn-default" type="submit" name="filter" value="Search"&gt;
      &lt;/form&gt;
          &lt;/div&gt;
      &lt;/div&gt;
          &lt;/div&gt;
  &lt;div class="col-xs-12 col-sm-6"&gt;
      &lt;h2 class="sub-header"&gt;Search Results&lt;/h2&gt;
      &lt;/div&gt;
  
      &lt;div class="row"&gt;
    &lt;div class="col-sm-12 col-md-8"&gt;&lt;hr/&gt;
        &lt;?php echo $this-&gt;session-&gt;flashdata('nores'); ?&gt;
                 &lt;?php foreach ($users as $user): ?&gt;
      &lt;div class="thumbnail"&gt;
        &lt;img src="..." alt="..."&gt;
        &lt;div class="caption"&gt;
          &lt;h3&gt;&lt;?=$user-&gt;name;?&gt;&lt;/h3&gt;
          &lt;p&gt;&lt;?=$user-&gt;homeaddress;?&gt;&lt;/p&gt;
          &lt;p&gt;&lt;?=$user-&gt;mobileNum;?&gt;&lt;/p&gt;
          &lt;p&gt;&lt;a href="#" class="btn btn-primary" role="button"&gt;Request&lt;/a&gt; &lt;a href="#" class="btn btn-default" role="button"&gt;View Profile&lt;/a&gt;&lt;/p&gt;
        &lt;/div&gt;
      &lt;/div&gt;
                &lt;?php endforeach; ?&gt;
    &lt;/div&gt;
  &lt;/div&gt;
  </code></pre>
  
  <p>In <strong>db</strong>, I have <strong>tables</strong>: <em>users</em>, <em>account</em>, and <em>subjects</em> where:</p>
  
  <p><strong>subjects</strong></p>
  
  <pre><code>subjID | subjName | accID
  ------ | -------- | -----
  1      | Math     | 1
  </code></pre>
  
  <p><strong>account</strong></p>
  
  <pre><code> accID | subjID   | userID
  ------ | -------- | -----
  1      | 1        | 1
  </code></pre>
  
  <p><strong>users</strong></p>
  
  <pre><code>userID | userName | accID
  ------ | -------- | -----
  1      | foo      | 1
  </code></pre>
  
  <p>I have the ff code for my search <strong>controller</strong>:</p>
  
  <pre><code>if (isset($filter) &amp;&amp; !empty($search)) {
              $data['users'] = $this-&gt;model_search-&gt;searchTutor($field, $search);
          }
          elseif (($filter == 'subjName') &amp;&amp; !empty($search)) {
              $data['users'] = $this-&gt;model_search-&gt;searchBySubj($field, $search);
          }
          elseif (isset($filter) &amp;&amp; empty($search)) {
              $data['users'] = $this-&gt;model_search-&gt;getlist($field);
          }
          elseif (isset($filter) &amp;&amp; ($search == 0)) {
              $this-&gt;session-&gt;set_flashdata('nores','&lt;div class="alert text-center"&gt;No result matched your search.&lt;/div&gt;');
              redirect('search/index');
          }
  </code></pre>
  
  <p>Then I try to search for a Subject Name that is not in db</p>
  
  <p>So it's supposed to enter 2nd elseif <code>-&gt;searchBySubj();</code> function</p>
  
  <p>In search <strong>model</strong>:</p>
  
  <pre><code>public function searchTutor($field, $search)
      {
          $this-&gt;db-&gt;like($field, $search);
          $this-&gt;db-&gt;order_by($field);
          $query = $this-&gt;db-&gt;get('users');
          return $query-&gt;result();
      }
  
      public function searchBySubj($field, $search)
      {
          $this-&gt;db-&gt;query("SELECT id as Userid, accID as AccID, subjID as subid, subjName as sname FROM users userT INNER JOIN account accT ON id = accID INNER JOIN subjects ON accID = subjID;");
          $query = $this-&gt;db-&gt;get('users', 'ASC');
          return $query-&gt;result();
      }
  </code></pre>
  
  <p>But this appears:</p>
  
  <pre><code>A Database Error Occurred
  Error Number: 1054
  Unknown column 'subjName' in 'where clause'
  SELECT * FROM `users` WHERE `subjName` LIKE '%mathematics%' ESCAPE '!' ORDER BY `subjName`
  </code></pre>
  
  <p>The 3rd elseif runs when I try to search for a location and it gives me all the list</p>
  
  <p>Then I tried to search a location not in db but the last condition does not display the flashdata</p>
  
  <p>I put <code>&lt;?php echo $this-&gt;session-&gt;flashdata('nores'); ?&gt;</code> in view (above)</p>
  
  <p>Overall, I just want to search by Subject Name with tables that I have. Also, display flashdata if no record matches.</p>
  
  <p>How should these be properly done? What causes these errors?</p>
  
  <p>What am I missing?</p>
  
  <p>excuse my bad coding here, please help me with this one..</p>