Dynamically create PDO query statement and bind param from user input

Question

I'm trying to create a function that takes in user input array from post, and dynamically generate a query statement and bind params. So far I was able to create the query statement using a reference list, but was having trouble with the bind params because I need to write a big chunk of switch block to make it work. I'm using CodeIgniter.

/* user input array sample
   array ("title" => "abc",
          "year"  => ["start"=>1999, "end"=>2010]
   )
*/


class Listing extends CI_model {

  private ref_list = [
    'title'    => 'title LIKE :title',
    'year'     => "(year BETWEEN :start AND :end)" 
  ]

  private clause_list = [] // to be generated
  private param_list = [] // to be generated



  function query_create ( array $post_input ) {

    foreach ($post_input as $post_key => $post_value ) {

      // for clause list
      foreach ($this->ref_list as $ref_key => $ref_value ){
        if ($post_key == $ref_key ) {
          $this->clause_list[] = $ref_value;          
        }
      }

      // for param list
      switch($post_key) {
        case "title":
          $value = $post_value;

          $this->param_list[] = [":title", "%{$value}%", PDO::PARAM_STR];
          break;

        case "year":
          $start = $post_value['start'];
          $value = $post_value['end'];

          $this->param_list[] = [":start", "%{$start}%", PDO::PARAM_INT];
          $this->param_list[] = [":end", "%{$end}%", PDO::PARAM_INT];
          break;
      }

    }


    /* loop through clause list array to create query statement */

    $select_string = "SELECT * FROM song ";
    $clause_string = "WHERE ".implode ("AND", $this->clause_list);
    $query_stmt = $select_string . $clause_string


    /* bind param in PDO */

    if (is_array($this->param_list) && !empty($this->param_list)){
        foreach ($this->param_list as $index) {
            $bind   = $index[0];                
            $value  = &$index[1];
            $param  = $index[2];

            $query->bindParam ($bind, $value, $param);
        }
    }


  }

}

As you can see, the switch block can get huge when more inputs are posted, is there any way I can do to shorten it?

Take a look at this question: http://stackoverflow.com/questions/26519890/mysqli-filter-results-from-form-post/26520095#26520095 — Barmar, Dec 26 '14 at 05:16
I'm looking for a way to shorten that big switch statement, or to get rid of it. Something like a reference array for the bind param, and then a loop to assign those values if post key is match. So in the future, I only need to add new bind param values to the reference instead of adding chuck of repeated code to the switch statements. — reddy, Dec 26 '14 at 06:05
Unless there's a common pattern to the way you translate all the parameters to SQL, there's not likely to be a way to shorten it. — Barmar, Dec 26 '14 at 07:22

Dynamically create PDO query statement and bind param from user input

0 Answers0