exclude data in my sql search

Question

exclude data in my sql search

Navigation

#1 by (1 votes)

1

I have a function that searches me in my database work with mysql

function listUser($conexion){

        $consulta = (mysqli_query($conexion, "SELECT *, p.nombre as personaNombre, p.id as idPersona, d.nombre as nombreDepartamento, u.id as idUsuario, u.nombre as nombreUsuario
                                              FROM persona as p
                                              JOIN usuario as u
                                              on p.id = u.id_persona
                                              JOIN departamento as d
                                              on u.id_departamento = d.id
                                              ORDER BY p.nombre ASC")) or die("Error listando Usuarios: ".mysqli_error($conexion));

        return $consulta;
    }

She is returning all the values made with the union of all the tables but I need to exclude the p.id = u.id_person of the user

here I display the list of names on the list

<?php if(!isset($_POST['usuario'])): ?>
            <div class="input-field col s6">
             <select name="destinatario" id="destinatario">
                <option value="0" selected>Seleccione</option>
                <?php while($resultado  = $consulta2->fetch_array(MYSQLI_ASSOC)): ?>
                  <option value="<?=$resultado['idUsuario']?>">
                    <?php echo $resultado['personaNombre'] ?>
                    <?php echo $resultado['apellido'] ?>
                    <?php if ($resultado['id_cede'] = 11)
                        $resultado['id_cede'] = "Sistemas";
                     echo $resultado['id_cede'] ?>
                  </option>
                <?php endwhile; ?>
              </select>
              <label for="destinatario">Destinatario</label>
            </div>

<?php endif; ?>

and here I show the name of my current user

<div class="col s6">
              <strong><?=$resultado['personaNombre']?> <?=$resultado['apellido']?></strong>
            </div>

sql php

asked by Juan Ortiz 06.11.2018 в 01:13

source

1 answer

Execute GRANT statements to a remote database in Postgres Send a variable of Javascript to Node.JS

score 1 · Answer 1

You can condition your query by passing to the function as parameter the id of the user to be excluded and using the WHERE in the sql ej:

function listUser($conexion, $excludeId = null){

    $whereCondition = '';
    if ($excludeId) {
        $whereCondition = " WHERE p.id != $excludeId";
    }

    $consulta = (mysqli_query($conexion, "SELECT *, p.nombre as personaNombre, p.id as idPersona, d.nombre as nombreDepartamento, u.id as idUsuario, u.nombre as nombreUsuario
                                          FROM persona as p
                                          JOIN usuario as u
                                          on p.id = u.id_persona
                                          JOIN departamento as d
                                          on u.id_departamento = d.id
                                          $whereCondition -- agrega ésta condición
                                          ORDER BY p.nombre ASC")) or die("Error listando Usuarios: ".mysqli_error($conexion));

    return $consulta;
}

P.D .: Modified so that the $excludeId is optional in case you need to use the same function in another place to list all the users

I leave the version designed so that you always exclude a user id (the if removed from the previous code):

function listUser($conexion, $excludeId){

$consulta = (mysqli_query($conexion, "SELECT *, p.nombre as personaNombre, p.id as idPersona, d.nombre as nombreDepartamento, u.id as idUsuario, u.nombre as nombreUsuario
                                      FROM persona as p
                                      JOIN usuario as u
                                      on p.id = u.id_persona
                                      JOIN departamento as d
                                      on u.id_departamento = d.id
                                      WHERE p.id != $excludeId -- agrega ésta condición
                                      ORDER BY p.nombre ASC")) or die("Error listando Usuarios: ".mysqli_error($conexion));

    return $consulta;
}