search database using JOIN SQL PHP

Question

search database using JOIN SQL PHP

Navigation

#1 by (0 votes)

0

   function listUser($conexion){
//funcion de busqueda
        $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;
    }


if($_SESSION['ticket_tipo'] == 1) //if que me muestra los resultados segun //el tipo de ticket con que ingreso
            echo "<tr>
                <td>".$resultado['personaNombre']."</td>
                <td>".$resultado['apellido']."</td>
                <td>".$resultado['email']."</td>
                <td>".$resultado['nombreUsuario']."</td>
                <td>".$tipo."</td>
                <td>".$activo."</td>


                <td>".$resultado['sede']."</td> <!-- PENDIENTE DEBE TRAER LA SEDE--> 



                <td>".$resultado['']."</td>    <!-- PENDIENTE DEBE TRAER EL DEPARTAMENTO ASOCIADO A LA SEDE-->


                <td><a title=\"Editar\" href=\"editarUsuario-3-".$resultado['idUsuario']."\"><i class=\"fa fa-pencil\" aria-hidden=\"true\"></i></a></td>
                <td><a title=\"Activar o Desactivar\" href=\"scripts/actDesUsuario-".$resultado['idUsuario']."\">".$actdesc."</a></td>
                <td><a title=\"Resetear\" href=\"scripts/resetUsuario-".$resultado['idUsuario']."\"><i class=\"fa fa-refresh\" aria-hidden=\"true\"></i></a></td>
              </tr>";

good again friends, I have a doubt about the join, I am slowly bringing data from the database that I have associated with my database but it is giving me problems to bring the field headquarters and department of the created users.

I used to create a user by assigning a department for a value set but I realized that it will bring me problems in the future because I can not filter the searches.

some help on php?

the show create table of the tables is the following

enter the description of the image here

database

sede
CREATE TABLE 'sede' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'nombre' varchar(255) NOT NULL,
 PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1


departamento
CREATE TABLE 'departamento' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'sede_id' int(11) NOT NULL,
 'nombre' varchar(255) DEFAULT NULL,
 'id_usuario_creador' int(11) DEFAULT NULL,
 'hora_creacion' time NOT NULL,
 'fecha_creacion' date NOT NULL,
 'id_usuario_modificador' int(11) NOT NULL,
 'hora_modificacion' time NOT NULL,
 'fecha_modificacion' date NOT NULL,
 PRIMARY KEY ('id'),
 KEY 'sede_id' ('sede_id'),
 CONSTRAINT 'departamento_ibfk_1' FOREIGN KEY ('sede_id') REFERENCES 'sede' ('id')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1


persona
CREATE TABLE 'persona' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'nombre' varchar(30) NOT NULL,
 'apellido' varchar(30) NOT NULL,
 'cedula' char(8) NOT NULL,
 'email' varchar(150) NOT NULL,
 PRIMARY KEY ('id'),
 UNIQUE KEY 'cedula' ('cedula'),
 UNIQUE KEY 'email' ('email')
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8


usuario
CREATE TABLE 'usuario' (
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'id_persona' int(11) NOT NULL,
 'id_departamento' int(11) NOT NULL,
 'nombre' varchar(31) NOT NULL,
 'clave' char(32) NOT NULL,
 'tipo' set('1','2','3','4','5') NOT NULL,
 'plataforma' tinyint(1) NOT NULL DEFAULT '0',
 'activo' set('0','1') NOT NULL DEFAULT '1',
 'primer_login' set('0','1') NOT NULL DEFAULT '0',
 'id_usuario_creador' int(11) NOT NULL,
 'hora_creacion' time NOT NULL,
 'fecha_creacion' date NOT NULL,
 'id_usuario_modificador' int(11) NOT NULL,
 'hora_modificacion' time NOT NULL,
 'fecha_modificacion' date NOT NULL,
 PRIMARY KEY ('id'),
 UNIQUE KEY 'id_persona' ('id_persona'),
 UNIQUE KEY 'nombre' ('nombre')
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

php join

asked by Juan Ortiz 23.03.2018 в 14:38

source

1 answer

TURN ON A LED WITH ARDUINO WITH A "1" AND SHUT OFF WITH A "0" How can I put a specific font to an input text?

score 0 · Answer 1

You can do as many JOINS as you need

Example:

SELECT 
  p.nombre,
  p.apellido,
  p.email,
  u.id_persona,
  u.nombre as usuario,
  d.nombre as departamento,
  d.id_sede,
  s.nombre as sede
FROM usuario as u
INNER JOIN persona as p ON u.id_persona = p.id
INNER JOIN departamento as d ON u.id_departamento = d.id
INNER JOIN sede as s ON d.id_sede = s.id
ORDER BY p.nombre ASC

Note: You can do without the reserved word as , its use is maintained by retrocompativilidad.

Try to avoid using * to select fields, and if you have fields with the same name in different tables assign them aliases so that there is no ambiguity.

NOTE: @JuanOrtiz Look at the show create that you put in the last edition, instead of having a column called id_sede in the departments table it is called sede_id this is the failure by the one that does not work for you the query as I put it and for the one that throws you the error Unknown column 'd.id_sede' . The names do not match.

You can see an example of the query here: link

Basic example to test in bd, eye when executing in bd in production contains sentences to delete tables.

DROP TABLE IF EXISTS persona;
DROP TABLE IF EXISTS usuario;
DROP TABLE IF EXISTS departamento;
DROP TABLE IF EXISTS sede;

CREATE TABLE persona( id integer,  nombre text, apellido text, email text);
CREATE TABLE usuario( id integer,  id_persona integer, id_departamento integer, nombre text);
CREATE TABLE departamento( id integer, id_sede integer, nombre text);
CREATE TABLE sede( id integer, nombre text);

INSERT INTO persona VALUES (1, 'pepe', 'husla', '[email protected]');
INSERT INTO usuario VALUES (1, 1, 1, 'userpepe');
INSERT INTO departamento VALUES (1, 1, 'ventas');
INSERT INTO sede VALUES (1, 'spain');


SELECT 
  p.nombre,
  p.apellido,
  p.email,
  u.id_persona,
  u.nombre as usuario,
  d.nombre as departamento,
  d.id_sede, 
  s.nombre as sede
FROM usuario as u
INNER JOIN persona as p ON u.id_persona = p.id
INNER JOIN departamento as d ON u.id_departamento = d.id
INNER JOIN sede as s ON d.id_sede = s.id
ORDER BY p.nombre ASC;