Dynamic search filter with prepared queries

Question

Dynamic search filter with prepared queries

Navigation

#1 by (2 votes)

1

I want to do a dynamic search filter, that is, to filter data in the database according to what the user wants to search. Currently, what I do is:

// creo la base de la consulta
$query = 'SELECT * FROM tabla WHERE 1 '; 

// voy añadiendo las condiciones a medida que el usuario lo indica
if(isset($_POST['filtro_1'])){
    $query .= 'AND Condicion_1 = "' . $_POST['filtro_1'] . '"';
}
if(isset($_POST['filtro_2'])){
    $query .= 'AND Condicion_2 = "' . $_POST['filtro_2'] . '"';
}

$result = $conexion -> prepare($query);
$result -> execute(array());

What I want to do is that the queries are prepared, but I do not know how to structure the code so that it adds one condition or another depending on what the user wants to look for.

Currently, the code I have is this and when I run it, it gives me this error:

Fatal error: Uncaught PDOException: SQLSTATE [HY093]: Invalid parameter number: no parameters were bound in C: \ xampp \ htdocs \ php \ index.php: 63 Stack trace: # 0 C: \ xampp \ htdocs \ php \ index.php (63): PDOStatement-> execute (Array) # 1 {main} thrown in C: \ xampp \ htdocs \ php \ index.php on line 63

<?php   


require("datos_conexion.php");
$user = '';
$name = 'marcos';
$user = 'gmarsi';

$data = 'SELECT User_User FROM users WHERE User_Punc > 0 ';
if ($user != '') {
    $data .= " AND User_User = :user ";
}
if ($name != '') {
    $data .= " AND User_Name = :name ";
}
$resultado = $base -> prepare($data);

if ($user != '') {
    $resultado -> bindValue(':user', $user);
}

if ($name != '') {
    $resultado -> bindValue(':name', $name);
}
$resultado -> execute(array());

while($row = $resultado -> fetch(PDO::FETCH_ASSOC)) {
    echo '<br>'.$row['User_User'];
}

?>

The connection file:

<?php



$base = new PDO("mysql:host = localhost; dbname=databaseName", 'userName', 'password');

$base ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$base -> exec("SET CHARACTER SET utf8");

?>

php

asked by gmarsi 31.08.2017 в 13:09

source

1 answer

RelatedObjectDoesNotExist User has not profile Laravel show articles ordered

score 2 · Accepted Answer

The problem is in this line:

$resultado -> execute(array());

You have already done bind the parameters and values that you wanted, you do not need to pass an array to execute , and especially an empty array (why you receive the error, because the number of parameters in the array is 0).

The problem is easily solved by removing it:

$resultado -> execute();

Another thing would be that instead of doing bindValue what you would do is put the values in an array, and then pass that array to execute ... but then again you run the risk of not being specified neither $user nor $name , in which case you would receive the error again.