Make function a Conditional Mysql query

Question

I have a simple function on CodeIgniter 3 that fetches some data from the database using a simple query with a refine variable :

function  search($term){

    $this->db->like('sReference',$term)
         ->or_like('nReference', $term)
         ->or_like('sSearch', $term)
         ->or_like('sSort', $term);

    $query = $this->db->get('tReference');
    $ret['rows'] = $query->result();
    $ret['number'] = $query->num_rows();

return $ret;
}

Also I need the same query for another search method but just adding two lines like so :

function  search($term){
//Affichage de sproduits
$this->db->like('sReference',$term)
->or_like('nReference', $term)
->or_like('sSearch', $term)
->or_like('sSort', $term)
->join('tManufacturer','nManufacturer=tReference.nManufacturer')
->where('nStatus',$status);

$query = $this->db->get('tReference');
$ret['rows'] = $query->result();
$ret['number'] = $query->num_rows();

return $ret;
}

My Question is : Is there any way to make a condition on it (knowing that I use a different search forms for both of the queries), or I must make two separate queries?

And thanks to all of you?

RiggsFolly · Accepted Answer · 2015-08-05 00:48:25Z

2

Yes of course. Add 2 parameters to the funtion, you can make it very flexible if you like.

function  search($term, $join=array(), $where=array()){
    //Affichage de sproduits
    $this->db->select('*')
             ->from('tReference');
             ->like('sReference',$term)
             ->or_like('nReference', $term)
             ->or_like('sSearch', $term)
             ->or_like('sSort', $term);
    if ( is_array($join) && count($join) == 2 ) {
        $this->db->join($join[0], $join[1]);
    }
    if ( is_array($where) && count($where) ==  2 ) {
        $this->db->where($where[0],$where[1]);
    }

    $query = $this->db->get();
    $ret = array();
    foreach ($query->result() as $row)
    {
        $ret['rows'][] = $row;
    }
    $ret['number'] = $query->num_rows();

    return $ret;
}

Now call it like this

$result = search('smith');   // just the ->like's

or if you want the join to be set

$result = search('smith', 
                 array('tManufacturer',
                       'nManufacturer=tReference.nManufacturer')
                );

or if you want the join and where set

$status = 'something';
$result = search('smith', 
                 array('tManufacturer',
                       'nManufacturer=tReference.nManufacturer'),
                 array('nStatus', $status)
                );

or if you just want the where clause and not the join

$status = 'something';
$result = search('smith', NULL, array('nStatus', $status) );

edited Aug 5, 2015 at 0:48

answered Aug 5, 2015 at 0:38

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saif Lacrimosa Over a year ago

Thank you so much @RiggsFooly it's working, I do really appreciate it

RiggsFolly Over a year ago

See latest mod. I missed a bit where if your result returns more that one row you were not collecting all the results in the returned $ret array

Saif Lacrimosa Over a year ago

@RiggsFooly Yes I've implemented in my code, I appreciate the idea from you, it's brilliant!

Collectives™ on Stack Overflow

Make function a Conditional Mysql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related