The first query work when I remove the placeholder :p
and replace with ?
and at the $stmt->execute(array(':s' => $s));
replace with $stmt->execute([$s]);
while the code after if(!empty($search) {
is not working, look below
$mydb = new Mydb; //a possessed id $s = 1; $q = 'SELECT name, street FROM mydata WHERE possessed_id = :p '; $search = $_POST["search"]["value"]; if(!empty($search) { $q .= 'AND (name LIKE :n OR street LIKE :s)'; } $stmt = $mydb -> prepare($q); $stmt->bindValue(':n', '%'.$search.'%', PDO::PARAM_STR); $stmt->bindValue(':s', '%'.$search.'%', PDO::PARAM_STR); $stmt->execute(array(':s' => $s));
the goal is to display all data in a table and allow search
Advertisement
Answer
When $search
is empty, you have the parameter :p
in your query but you try to bind the :n
& :s
parameters.
When $search
is not empty, you have the 3 parameters (:p
, :n
& :s
) but you still don’t bind :p
.
In any case you have a different number of declared and bound parameters.
You should take care of :p
and only bind the other parameters when they exist in the query:
$q = 'SELECT name, street FROM mydata WHERE possessed_id = :p '; $search = $_POST["search"]["value"]; // Default parameters $parameters = [ 'p' => $s ]; // Add ":n" & ":s" if('' !== $search) { $q .= 'AND (name LIKE :n OR street LIKE :s)'; $parameters['n'] = '%' . $search . '%'; $parameters['s'] = '%' . $search . '%'; } $stmt = $mydb->prepare($q); // Send the parameters $stmt->execute($parameters);
I assumed a corrected version of your parameters and their value and used only 1 way to bind the parameters to the query with an array during the execute()
call.
I also removed the
empty()
call (here‘s why).