1

My PHP script is getting a classic PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /var/www/html/sctcc/models/contacts_cat.php on line 124.

I've been debugging, and the query echoes as: SELECT cms_contact.cms_id FROM cms_contact WHERE cms_contact.soft_delete = 0 && (first_name LIKE '%:f_search%' || last_name LIKE '%:l_search%') && cms_cat.cat_id = :category ORDER BY cms_contact.last_name, cms_contact.first_name LIMIT 50 OFFSET 0

A print_r of the parameter array reads as: Array ( [f_search] => Morgan [l_search] => Morgan [category] => 3 )

function get_contacts_search($limit = 50, $offset = 0, $search = null, $search_by = null, $category = null, $show_deleted = 0){
   //Reference: http://stackoverflow.com/questions/10015364/pagination-sql-query-syntax
   //Use MySQL Keywords LIMIT & OFFSET to pageinate a query.
   $sql = 'SELECT
                cms_contact.cms_id
                FROM cms_contact';
  $where = " WHERE cms_contact.soft_delete = $show_deleted";
  $order_by = " ORDER BY cms_contact.last_name, cms_contact.first_name LIMIT $limit OFFSET $offset";
  //$limit_offset = ' LIMIT :limit OFFSET :offset';
  $parameters = array();
  //$parameters['limit'] = $limit;
  //$parameters['offset'] = $offset;
  //$parameters['show_deleted'] = $show_deleted;

  if(isset($search)){
    if($search_by == 'name'){
      $where .= " && (first_name LIKE '%:f_search%' || last_name LIKE '%:l_search%')";
      $parameters['f_search' ] = $search;
      $parameters['l_search'] = $search;
    }elseif($search_by == 'company'){
      $where .= " && company LIKE '%:c_search%'";
      $parameters['c_search' ] = $search;
    }else{
      $where .= " && (first_name LIKE '%:f_search%' || last_name LIKE '%:l_search%')";
      $parameters['f_search' ] = $search;
      $parameters['l_search'] = $search;
    }
  }
  if(isset($category)){
    $where .= " && cms_cat.cat_id = :category";
    $parameters['category'] = $category;
  }
  echo "<div class='greenmessage'>". $sql.$where.$order_by ."</div>";
  $data;
  try{
    $db = connect();
    $query = $db->prepare($sql.$where.$order_by);
    print_r($parameters);
    //echo "<div class='greenmessage'><pre>".print_r($parameters)."</pre></div>";
    $result_set = $query->execute($parameters);

    for($i = 0; $row = $query->fetch(); $i++){
    //echo "CMS ID: {$row['cms_id']}\n";
      $data[$i] = get_contact_categories($row['cms_id']);
      //current(get_contact_with_categories_by_id($row['cms_id']));
      // echo "<pre style='display:block; background-color:#ccffff; border:5px solid blue;'>";
      // print_r(get_contact_with_categories_by_id($row['cms_id']));
      // echo "</pre>";
      // echo "</br>End Contact #{$row['cms_id']}</br>";
    }
    $db = null;
  }catch(PDOException $e){
    log_or_echo($e);
  }
  return $data;
  }

Any clue what's wrong here?

(If someone can suggest a way to bind LIMIT, OFFSET, and show_delete, also appreciated - it seems it didn't like me trying to do that, which is why we have variables without user control for pagination. Something about syntax.)

dluxcru
  • 397
  • 1
  • 5
  • 16
  • I don't know if this fixed the issue, but when preparing a `like` condition, you add the `%` to the variable with the value, not the query (Don't even use the quotes in the query) – FirstOne Jul 28 '16 at 21:03
  • For the binding to LIMIT: [How bindValue in LIMIT?](http://stackoverflow.com/questions/2269840/how-bindvalue-in-limit) – FirstOne Jul 28 '16 at 21:05
  • 5
    [mysql PDO how to bind LIKE](http://stackoverflow.com/questions/2722136/mysql-pdo-how-to-bind-like) – FirstOne Jul 28 '16 at 21:09

0 Answers0