I am doing a CRUD in PHP which has interested and courses. I want to make interested parties searchable by first or last name or both. When I search for both it works perfectly, when I want to search for one or the other it throws me the following error:
SyntaxError: Unexpected token 'S', "SQLSTATE[H"... is not valid JSON
The request to the database is as follows:
public static function buscarInteresado($data)
{
try {
if ($data['nombre'] != '' and $data['apellido'] != ''){
$sql = "SELECT * FROM public.interesado WHERE nombre = :nombre AND apellido = :apellido";
} elseif ($data['nombre'] != '' and $data['apellido'] == '') {
$sql = "SELECT * FROM public.interesado WHERE nombre = :nombre";
} elseif ($data['nombre'] == '' and $data['apellido'] != '') {
$sql = "SELECT * FROM public.interesado WHERE apellido = :apellido";
}
$stmt = Connection::getConnection()->prepare($sql);
$stmt->bindParam(':nombre', $data['nombre']);
$stmt->bindParam(':apellido', $data['apellido']);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
} catch (PDOException $th) {
echo $th->getMessage();
}
}
The controller is the following:
<?php
require_once "../models/interesado.model.php";
$arrayName = array(
'nombre' => $_POST['nombre'],
'apellido' => $_POST['apellido'],
);
echo json_encode(Interesado::buscarInteresado($arrayName));
And the code in JS is as follows:
this.buscar = () => {
if (document.getElementById("nombre_search").value != "" || document.getElementById("apellido_search").value != "") {
var form = new FormData();
form.append("nombre", document.getElementById("nombre_search").value);
form.append("apellido", document.getElementById("apellido_search").value);
fetch("../controllers/buscar_interesado.php", {
method: "POST",
body: form,
})
.then((res) => res.json())
.then((data) => {
this.tbody.innerHTML = "";
data.forEach((item) => {
this.tbody.innerHTML += `
html
`;
})})
.catch((error) => console.log(error));
} else {
this.listado();
}
}
nombre
I imagine that the frontend form, which you control, always submits the and fieldsapellido
. However, it is more cautious to use nullish coalesce , to handle the possibility that a field does not come and thus avoid the famous bugundefined array key
.It also wouldn't hurt to make sure you're working with strings and not other data types:
The important thing is to be sure that when entering
buscarInteresado
the array$data
it will have two properties, name and surname, of type string. No matter that they may be blank.The proposed solution works, but it doesn't seem efficient to fix a problem with code repetition. You could structure it instead like:
What is important about that solution?
First your original code doesn't have how to handle both empty data scenario. A fatal error would be triggered because it
$stmt
does not exist. The try/catch doesn't cover you in this case. In this solution I do handle that edge case.Second : the method should respond with the same data type or throw an exception. Your code can return an array, return nothing, or throw a fatal error. I return an empty array, an array of results, or even the error message in an array. The method does not have to be responsible for printing anything. That is done by the one who invokes it.
Third : Instead of using
else if
note that the content or lack of it in one field does not depend on the other. They are twoif
that can be fulfilled at the same time or separately.Fourth Starting from a base SQL statement, for each non-empty parameter a condition is added to it. The base ends with
WHERE 1=1
because it's harmless and in return I can start every addition withAND
without having to check whatif
I'm up to.Fifth :
PDOStatement::execute
accept as an argument an associative array where the keys are the placeholders declared in your query:I am adding the content of that array inside my
if
so that it only contains the non-empty fields. Bonus tip: you can omit the colon and use the key name as it is in$data
.This implementation can be further optimized by reusing the same conditional assignment for each query field:
And with that, tomorrow you can incorporate another search criteria with minimal modification. You just add it to
foreach
(Perhaps in that scenario it is better to validate the content of
$data
in the method instead of the global scope.) }}Edit : I had an
AND
extra in the SQL statementSOLUTION
I had to put the $stmt in each if, leaving the code like this: