CODEWITHSUNDEEP
phpmysqlpdo
Tito
Tito

Reputation: 852

Structuring my MySQL search query for use with PDO

I'm attempting to write a parameterized query with PDO that accepts a number of inputs and acts as a search on a specific table.

There are a number of columns I wish to search on but each of which could be optional. The query simplified might look like:

SELECT * FROM item 
WHERE name LIKE '%?%'
AND col2 IN (?, ?, .......)
AND col3 IN (?, ?, .......)
...

and with the IN clause, there could be any number (0 or more) of terms for each column.

Since the IN clause won't work with 0 values, and in PDO I'd have to iterate over each array passed in for each IN clause - I'm wondering if there's a better way to structure this as it seems like a big mess.

Upvotes: 0

Views: 47

Answers (1)

Thorsten
Thorsten

Reputation: 3122

You can use "call_user_func_array" to make it dynamic. This is what I use:

public function selectMSData($handlename, $sql, $type='', $params = array())
{
if ( ! is_string($sql) || ! is_array($params) ) {
  die('wrong param types');
}

$this->dbconnect($handlename); //connect to db and save connection with handle-name
$result = array();
$aRows = 0;
if(sizeof($params)==0) {
  //simple query without runtime parameters
  $msres = $this->dbhandle[$handlename]->query($sql);
  if($msres === false) {
    //log error
  } else {
    while($mres = $msres->fetch_array(MYSQLI_ASSOC)) {
      $aRows++;
      $result[] = $mres;
    }
  }
} else {
  //prepared statement using runtime parameters
  $stmt = $this->dbhandle[$handlename]->prepare($sql);
  if(!$stmt) {
    //log error
  }
  $valArr = array();
  $valArr[] = $type;
  foreach($params as $pkey => $pval) {
    $valArr[] = &$params[$pkey];
  }
  call_user_func_array(array(&$stmt, 'bind_param'), $valArr);

  if(!$stmt->execute()) {
    //log error
  };
  $stmt->store_result(); //fetch is super-slow for text-fields if you don't buffer the result!!

  $meta = $stmt->result_metadata();
  while ($field = $meta->fetch_field()) {
    $resfields[] = &$row[$field->name];
  }

  call_user_func_array(array($stmt, 'bind_result'), $resfields);

  while ($stmt->fetch()) {
        foreach($row as $key => $val) {
            $c[$key] = $val;
        }
        $result[] = $c;
        $aRows++;
  }
  $stmt->close();
}
$this->result = $result;
return $aRows;
}

and you call it like this:

$db->selectMSData('my_db_name', 'SELECT * FROM example WHERE a=? AND b=? LIMIT 1', 'ss', array($a, $b));

Upvotes: 1

Related Questions