Filter results in PHP / MySQL


I'm doing a message page in PHP and MySQL, I have a table called usuarios with the fields id , user , edad , pais and ciudad .

I would like the user to be able to search for other users according to certain parameters, such as age; For this I have the following form:

<form action="search.php" method="post">
        <label>Search By: </label> <br>
    <input type="text" value="" name="usernam" placeholder="Username"><br>
    <input type="number" value="" name="age" placeholder="Age" min="10" max="150"><br>
    <input type="text" value="" name="country" placeholder="country"><br>
    <input type="text" value="" name="city" placeholder="city"> <br><br>
    <input id="" type="submit" value="Search" name="search"></form>

And then the following PHP code:

if(isset($_POST['search'])) {
   $usernam = $_POST['usernam'];
   $age = $_POST['age'];
   $country = $_POST['country'];
   $city = $_POST['city'];

I still have to do the SQL query to get the data, but I do not know how I could get that if the user does not enter all parameters (for example, enter only the country and city fields) the query ignores the fields that are blank, which in this example would be usuario and edad .

How could I make this query?

asked by kalia 16.08.2017 в 16:05

3 answers


Using PDO, you could implement your search in the following way:

if(isset($_POST['search'])) {
  /* Consulta raíz */
  $sql = 'SELECT * FROM usuarios';
  /* Aquí almacenaremos los WHERE que se unirán con un "AND" */
  $where = [];
  /* Por ahora no tenemos ningún campo de búsqueda para PDO */
  $campos = [];
  /* Si queremos una búsqueda de usuario preparamos todo para hacerla */
  if (!empty($_POST['usernam'])) {
    /* Agregamos al WHERE la comparación */
    array_push($where, 'INSTR(LOWER(:usernam), LOWER(user)) > 0');
    /* Preparamos los datos para la variable preparada */
    $campos[':usernam'] = [
      'valor' => $_POST['usernam'],
      'tipo' => \PDO::PARAM_STR,
  /* Hacemos lo mismo con el resto de campos del formulario */
  if (!empty($_POST['age'])) {
    array_push($where, 'edad = :age');
    $campos[':age'] = [
      'valor' => $_POST['age'],
      'tipo' => \PDO::PARAM_INT,
  if (!empty($_POST['country'])) {
    array_push($where, 'MATCH (pais) AGAINST (:country IN NATURAL LANGUAGE MODE)');
    $campos[':country'] = [
      'valor' => $_POST['country'],
      'tipo' => \PDO::PARAM_STR,
  if (!empty($_POST['city'])) {
    array_push($where, 'MATCH (ciudad) AGAINST (:city IN NATURAL LANGUAGE MODE)');
    $campos[':city'] = [
      'valor' => $_POST['city'],
      'tipo' => \PDO::PARAM_STR,
  /* Si tenemos cláusulas WHERE las unimos */
  if (!empty($where)) {
    $sql .= ' WHERE ' . implode(' AND ', $where);
  $consulta = $pdo->prepare($sql);
  /* Si no hay elementos en $campos no se ejecutará este bucle */
  foreach($campos as $clave => $valores) {
    $consulta->bindParam($clave, $valores['valor'], $valores['tipo']);
  $resultado = $consulta->execute();

In the code there are comments that explain the operation step by step.

I have used searches fulltext in some fields (to give you an example of use), a numerical comparison and also the function INSTR together with LOWER so that the comparison is not case-sensitive.

To make use of searches fulltext optimized you have to modify the scheme of the table by executing:

ALTER TABLE usuarios ADD FULLTEXT (pais, ciudad)

Searches fulltext have many advantages, as it considers "whip" and "whip" as equal words (transliterate when comparing, or rather, normalizes when indexing) and is insensitive to uppercase / lowercase.

answered by 16.08.2017 / 16:53

For these cases you would have to use the operator SQL LIKE :

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used together with the operator LIKE :

% - El signo de porcentaje representa cero, uno o varios caracteres
_ - El subrayado representa un solo carácter

Then your query would be something like this:

"Select * from usuarios where user like '%'$usernam'%' and edad like '%'$age'%' and pais like '%'$country'%'and ciudad like '%'$city'%'"

This will give you back results with the parameters you have set, it does not matter if only one parameter has been set or all

answered by 16.08.2017 в 16:14

One option would be to validate if the variables have a value and according to these results go arming the query:
Here is an example of how it could be:

   //evitamos que el usuario envie espacios en blanco
    $usernam = trim($_POST['usernam']);
    $age = trim($_POST['age']);
    $country = trim($_POST['country']);
    $city = trim($_POST['city']);
    if ($usernam =="" AND $age =="" AND $country =="" AND $city =="" AND) {
        $sql="SELECT * FROM nombreTabla";
        ......//demas codigo
        $sql='SELECT * FROM nombreTabla WHERE';
        if ($usernam != "") {
            $sql=' user = "$usernam" AND';
        }if ($age != "") {
            $sql=' edad = $age AND';
        }if ($country != "") {
            $sql=' pais = "$country" AND';
        }if ($city != "") {
            $sql=' ciudad = "$city" AND';
        $sqlFinal=trim($sql, 'AND');//quitamos el ultim AND para evitar error
        .....//demas codigo

you can adapt it to your convenience.


This form is very practical but you can take the risk of Sql Inyection

answered by 16.08.2017 в 16:41