Codeigniter API Get Call With Param Options For WHERE Mysql Query PHP

Question

I am using CodeIgniter to build my api and I am trying to find the best way to allow multiple params to be sent to then run my Model where clause if they exist. I am running into some problem and would appreciate if someone could pass on some advise best practise extra I fell like my whole setup is just getting bloated.

My query could take the following params:

/v1/tags?status=1&parentId=1&order=desc&limit=10&offset=1

Here is my table.

id  int(11) NO  PRI     auto_increment            
parentId    int(11) NO              
name    varchar(250)    NO              
status  tinyint(4)  NO              
createdDate timestamp   NO      CURRENT_TIMESTAMP 

Here is my controller.

/**
 * READ TAGS
 */
public function tags_get() {


    // OPTIONALS:
    $parentId   = $this->get('parentId');
    $status     = $this->get('status');

    // DEFAULTS:
    $offset     = $this->get('offset');
    $order      = $this->get('order');
    $limit      = $this->get('limit');

    // WHERE QUERY:
    $where = [];

    // VALIDATE:
    if(isset($status)){

        if ($status != 'publish' && $status != 'future' && $status != 'draft' && $status != 'pending' && $status != 'private' && $status != 'trash') {

            $this->response(array(
                'status'  => FALSE,
                'message' => '(status) must be one of the following (publish|future|draft|pending|private|trash)'
            ), REST_Controller::HTTP_OK);

        }

        // ADD TO QUERY:
        $where['status'] = $status;

    }

    if(isset($parentId)){

        if (filter_var($parentId, FILTER_VALIDATE_INT) === false) {

            $this->response(array(
                'status'  => FALSE,
                'message' => '(parentId) must be int'
            ), REST_Controller::HTTP_BAD_REQUEST);

        }

        // ADD TO QUERY:
        $where['parentId'] = $parentId;

    }

    // IF NO PARAMS RETUNR ALL DATA
    $data = $this->user_model->get_tags($where, $order, $offset, $limit);
    if($data){

        $this->response([
            'status'  => TRUE,
            'message' => 'Success',
            'paging'  => $offset,
            'records' => count($data),
            'data'    => $data,
        ], REST_Controller::HTTP_OK);

    }else{

        $this->response([
            'status' => FALSE,
            'message' => 'Not found',
            'data' => []
        ], REST_Controller::HTTP_NOT_FOUND); 

    }

}  

And here is my Model

function get_tags($where = [], $order = 'desc', $offset = 0, $limit = 100){

    // MAIN QUERY:
    $this->db->select('*');    
    $this->db->from('tags');

    // OPTIONAL WHERE QUERIES:
    foreach ($where as $key => $value) {
        $this->db->where($key, $value);
    }

    // DEFUALTS:
    $this->db->order_by('createdDate', $order); 
    $this->db->limit($limit, $offset);
    $query = $this->db->get();
    return ($query->num_rows() > 0) ? $query->result_array() : FALSE;

}

Take the following Query.

/v1/tags?status=0

This fails should I be using YES | NO or ON | OFF as varchars in my database instead of booleans?

UPDATE: Based on Rays answer I will be changing status to accept the following values.

publish
future
draft
pending
private
trash

I have also update my controller see above.

Answer 1

To be honest, your approach is quite good, better you go with ON OFF choice as numbers might get complicated if you want to adapt new states,

Let's take this example,

For some reason, your Manager added 3 more stats to your system, lets say 0,1,2,3,4

which means

• 0 is off
• 1 is on
• 2 is pending
• 3 is damaged
• 4 is cancelled

you will not be able to remember status based on their number in the future, but if you use names instead, you can understand better.

In the end, for sake of stability, stick with known structure.