php - 如何在codeigniter中使用if条件、And条件和where条件编写选择查询

Question

嗨，CI 开发人员，我是 Ci 新手，所以请大家帮助我开发一个我已经使用核心 php 开发的项目。所以现在我有兴趣学习 codeigniter，因此尝试使用 ci 框架开发相同的婚姻项目。在核心 php 中，我编写了一个搜索查询，现在我想将其编写为 CI 格式，所以请帮助我
这是我的 HTML 页面

<form action="<?php echo base_url();?>searchresult/users" method="post">
<div id="tab1" class="tab_content" >
**strong text** 
<table width="100%" border="0" cellspacing="0" cellpadding="0"> 
<tr>
  <td>Looking for</td> 
  <td> <input type="radio" name="look" id="look" value="female" checked="checked"/> 
     <span style="color:#000000;">Bride</span> 
     <input type="radio" name="look" id="look" value="male" />
     <span style="color:#000000;"> Groom</span>
  </td> 
</tr> 
<tr> 
  <td>Age</td> 
  <td> <span style="color:#000000;">From </span> 
    <select name="age_from" id="age" class="inp_age" > 
       <option>21</option> <option>22</option> 
       <option>23</option> <option>24</option> 
       <option>25</option> <option>26</option>
      </select>
   <span style="color:#000000;">&nbsp; To</span>
   <select name="age_to" id="age" class="inp_age" > 
    <option>18</option>
    <option>19</option> 
    <option >20</option>
    <option selected="selected">70</option> 
   </select>
  </td>
</tr>
 <tr> 
   <td>Sect</td> 
   <td><select name="sect" id="sect" onchange="return SelectSect(this.value);" class="inp_search" >
 <!-- <select name="cont" id="cont" onchange="return SelectState(this.value);" class="inp">--> 
 <option value="">Any sect</option>
 <option value="<?php echo $sects['sect_id'];?>"><?php echo $sects['sect_name'];?></option> 
</select>
 </td>
</tr>
 <tr>
    <td>Sub Sect</td> 
    <td>
      <select name="subsect" id="subsect" onchange="return SelectSub(this.value);" class="inp_search" > 
       <option value="">Any subsect</option> 
    </select>
   </td> 
 </tr> 
 <tr> 
     <td>Country</td>
     <td> 
         <select name="country" id="country" onchange="return SelectState(this.value);" class="inp_search"> 
          <option value="">Any Country</option> 
          <option value="<?php echo $country['country_id'];?>" style="width:10px"><?php echo $country['Country_name'];?></option>
        </select>
      </td>
  </tr> 
  <tr> 
     <td>State</td> 
     <td>
         <select name="state" id="state" onchange="return SelectCity(this.value);" class="inp_search" > 
          <option value="">Any state</option>
         </select>
     </td>
  </tr> 
   <tr>
      <td>City</td> 
      <td>
         <select name="city" id="city" class="inp_search" > 
            <option value="">Any city</option>
       </select>
      </td> 
   </tr> 
    <tr>
        <td>Qualification</td>
        <td>
            <select name="qualification" id="qualification" class="inp_search" > 
              <option value="">Any qualification</option> 
              <option value="<?php echo $qualifications['qualification_id'];?>"><?php echo $qualifications['qualification'];?></option> 
            </select>
        </td> 
   </tr>
   <tr>
        <td>&nbsp;</td> 
        <td> <div style="padding-left:100px;">
                   <input type="submit" name="submit" class="button green search_btn" value="Search" />
            </div>
       </td> 
   </tr> 
</table>
</div>
</form>

这是我的 Controller 页面

 <?php 
  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
    class Searchresult extends CI_Controller { 
       public function __construct(){ 
         parent::__construct(); 
         $this->load->model('searchresultss','','TRUE');
       } 
       public function users() {
         $this->load->view('includes/kheader');
         $data['showdata'] = $this->searchresultss->login();
         $this->load->view('searchresult',$data);
         $this->load->view('includes/khelp'); 
         $this->load->view('includes/kfooter'); }
     } 

这是我的模型页面

  <?php 
  Class Searchresultss extends CI_Model {  
      function login() { 
        $this->db->select('*'); 
        $this -> db -> from('users'); 
        $query = $this->db->get()->result();
        return $query; 
       }
  } 
  **this i smy core php query**
    $per_page =3; 
    $members=mysql_query("SELECT * FROM users WHERE if('$se_ct'!='',sect = '$se_ct' AND
    if('$subsect' !='',subsect = '$subsect',subsect like '%%'),sect like '%%' AND  
    subsect like '%%') AND IF( '$cou_ntry' !='', country = '$cou_ntry' AND if('$sta_te' 
    !='', state = '$sta_te' AND if('$ci_ty' !='',city = '$ci_ty',city like '%%'),state 
    LIKE '%%' AND city LIKE '%%'), country LIKE '%%' AND state LIKE '%%' AND city LIKE 
    '%%' ) AND age >= '$age_from' AND age <= '$age_to' AND IF('$qualification' 
    !='',qualification = '$qualification', qualification LIKE '%%' ) AND gender = 
    '$look' And status='1' "); 
   $count =mysql_num_rows($members);
   $pages =ceil($count/$per_page); 

我也需要 $count 和 $pages 结果，所以大家帮助我为上述详细信息编写 Controller 和模型

Answer 1

您可以执行以下操作来直接查询:

function login( $per_page = 3 ) { // it will need all the variables too included in your query
    $query  = "SELECT * FROM users WHERE if('$se_ct'!='',sect = '$se_ct' AND if('$subsect' !='',subsect = '$subsect',subsect like '%%'),sect like '%%' AND subsect like '%%') AND IF( '$cou_ntry' !='', country = '$cou_ntry' AND if('$sta_te' !='', state = '$sta_te' AND if('$ci_ty' !='',city = '$ci_ty',city like '%%'),state LIKE '%%' AND city LIKE '%%'), country LIKE '%%' AND state LIKE '%%' AND city LIKE '%%' ) AND age >= '$age_from' AND age <= '$age_to' AND IF('$qualification' !='',qualification = '$qualification', qualification LIKE '%%' ) AND gender = '$look' And status='1' ";
    $data               = array();
    $query              = $this->db->query( $query );
    $data['results']    = $query->result();
    $data['count']      = $query->num_rows();
    $data['pages']      = ceil($count/$per_page);
    return $data; 
}