Search code examples
phppdofilteringwhere-in

How is my Ajax PHP filter not filtering records with PDO?

So I am trying to filter records using checkboxes with Ajax and PHP. I am trying to filter records with PDO, since it is the safest option. I am wondering why the filter system only filters when one checkbox is selected and not more.

This is my code:

        if(isset($_POST['merk'])) 
        { 
            // Pretty sure you have the following format 1,2,3,4 and not '1','2','3','4'
            $merk = implode(',', $_POST['merk']);
            if (preg_replace('/[^A-Za-z0-9\-]/', '', $merk)) {
                // Notice that placeholders should not be quoted
                $sql .= ' AND merk IN(:merk)';
            }
        }

        if(isset($_POST['brandstof'])) 
        {
            $brandstof = implode(',', $_POST['brandstof']);
            if (preg_replace('/[^A-Za-z0-9\-]/', '', $brandstof)) {
                $sql .= ' AND brandstof IN(:brandstof)';
            }
        }

        if(isset($_POST['carrosserie'])) 
        {
            $carrosserie = implode(',', $_POST['carrosserie']);
            if (preg_replace('/[^A-Za-z0-9\-]/', '', $carrosserie)) {
                $sql .= ' AND carrosserie IN(:carrosserie)';
            }
        }

If the checkbox merk is selected that code will execute. Is there something wrong with the query maybe?

//We prepare our SELECT statement.
$statement = $pdo->prepare($sql);
        
if(isset($_POST['merk']))
{
  $statement->bindParam(":merk", $merk);
  // $params[] .= $merk;
}

if(isset($_POST['brandstof']))
{
  $statement->bindParam(":brandstof", $brandstof);
  // $params[] .= $brandstof;
}

if(isset($_POST['carrosserie']))
{
  $statement->bindParam(":carrosserie", $carrosserie);
  // $params[] .= $carrosserie;
}
    
$statement->execute();

This code filters one checkbox input, but stops filtering when two checkboxes are selected. See example below.

When one checkbox is selected

When second checkbox is selected

So my POST Data looks like this: Array ( [0] => BMW [1] => Skoda )

Solution

  • You are using strange quotes e.g "','" when parsing the input. Also, you should not quote the actual placeholder in the statement. After talking in chat I realised that the problem is the IN clauses and bindParams() not supporting arrays.

    From the documentation for PDOStatement::execute:

    input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

    Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.

    In order to fix this you will need to change your code to something like this:

    // Enable error reporting to spot any warnings and errors.
ini_set('display_errors', true);
error_reporting(E_ALL);

$params = array();

// Creates an IN clause with placeholders and stores parameters
function createInParam(array $data, $name, $query, array &$params) {
  $query .= " AND $name IN (";
  foreach ($data as $key => $value) {
    $params[$name . '_' . $key] = $value;
    $query .= ":${name}_${key},";
  }
  return rtrim($query, ',') . ')';
}

// Bind all parameters to statement
function bindParams($statement, array $params) {
  foreach ($params as $key => $value) {
    $statement->bindParam(":$key", $params[$key]);
  }
}

if (isset($_POST['merk'])) { 
  $merk = implode(',', $_POST['merk']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $merk)) {
    $data = explode(',', $merk);
    $sql = createInParam($data, 'merk', $sql, $params);
  }
}

if (isset($_POST['brandstof'])) {
  $brandstof = implode(',', $_POST['brandstof']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $brandstof)) {
    $data = explode(',', $brandstof);
    $sql = createInParam($data, 'brandstof', $sql, $params);
  }
}
    
if (isset($_POST['carrosserie'])) {
  $carrosserie = implode(',', $_POST['carrosserie']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $carrosserie)) {
    $data = explode(',', $carrosserie);
    $sql = createInParam($data, 'carrosserie', $sql, $params);
  }
}

// Then bind all parameters and execute
bindParams($statement, $params);
if ($statement->execute()) {
  // Success
}

    Another way of doing the same thing would be:

    $params = array();


if (isset($_POST['merk'])) { 
  $merk = implode(',', $_POST['merk']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $merk)) {
    $inClause = implode(',', array_fill(0, count($_POST['merk']), '?'));
    $sql .= " AND merk IN ($inClause)";
    $params = array_merge($params, explode(',', $merk));
  }
}

if (isset($_POST['brandstof'])) {
  $brandstof = implode(',', $_POST['brandstof']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $brandstof)) {
    $inClause = implode(',', array_fill(0, count($_POST['brandstof']), '?'));
    $sql .= " AND brandstof IN ($inClause)";
    $params = array_merge($params, explode(',', $brandstof));
  }
}
    
if (isset($_POST['carrosserie'])) {
  $carrosserie = implode(',', $_POST['carrosserie']);
  if (preg_replace('/[^A-Za-z0-9\-]/', '', $carrosserie)) {
    $inClause = implode(',', array_fill(0, count($_POST['carrosserie']), '?'));
    $sql .= " AND carrosserie IN ($inClause)";
    $params = array_merge($params, explode(',', $carrosserie));
  }
}

if ($statement->execute($params)) {
  // Success
}