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.

Is your model query is working correctly? – Pradeep Aug 13 '18 at 12:18 — Pradeep, Aug 13 '18 at 12:18

score 1 · Accepted Answer · answered Aug 13 '18 at 11:08

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.

answered Aug 13 '18 at 11:08

Ray A

1,283
2
10
22

Thanks, Ray that definitely makes sense, do you still suggest using on | off for a value that can only ever be true and false a switch for example? I suppose it won't make any difference to the database query performance. – user1503606 Aug 13 '18 at 11:16
yes you can, as long as you are sure your values are true or false. – Ray A Aug 13 '18 at 11:18
Thanks, Ray just want to see if anyone else pitches in then ill accept your answer. – user1503606 Aug 13 '18 at 11:37
you're welcome bro, and hopefully someone will share some ideas with you, – Ray A Aug 13 '18 at 11:37

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

1 Answers1