PHP Mysql Search result

Question

Good afternoon

I’ve been trying to fix this for two days without success and I’d like your personal help :)

The tables are as follows::

Chaves` (
  idChaves int(10) AUTO_INCREMENT,
  nomeChave varchar,
  estadoChave int(1) ,
  situacao varchar(10) ,
  PRIMARY KEY (`idChaves`)
)

Watchers:

TABLE `Vigilantes` (
  `idVigilantes` int AUTO_INCREMENT,
  `PORVigilante` int ,
  `nomeVigilantes` varchar,
  PRIMARY KEY (`idVigilantes`)
) 

Collaborators:

CREATE TABLE `Colaboradores` (
  `idColaboradores` intAUTO_INCREMENT,
  `nomeColaboradores` varchar,
  `apelido` varchar,
  `POR`,
  PRIMARY KEY (`idColaboradores`)

Locations:

TABLE `Locacoes` (
  `idLocacoes` intAUTO_INCREMENT,
  `horaSaida` timestamp ,
  `horaEntrada` timestamp ,
  `Vigilantes_idVigilantes` int,
  `Colaboradores_idColaboradores` int,
  `Chaves_idChaves` intL,
  PRIMARY KEY (`idLocacoes`),
  KEY `fk_Locacoes_Vigilantes1_idx` (`Vigilantes_idVigilantes`),
  KEY `fk_Locacoes_Colaboradores1_idx` (`Colaboradores_idColaboradores`),
  KEY `fk_Locacoes_Chaves1_idx` (`Chaves_idChaves`),
  CONSTRAINT `fk_Locacoes_Chaves1` FOREIGN KEY (`Chaves_idChaves`) REFERENCES `Chaves` (`idChaves`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Locacoes_Colaboradores1` FOREIGN KEY (`Colaboradores_idColaboradores`) REFERENCES `Colaboradores` (`idColaboradores`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Locacoes_Vigilantes1` FOREIGN KEY (`Vigilantes_idVigilantes`) REFERENCES `Vigilantes` (`idVigilantes`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

Summarizing for the Collaborator request a Key at the Vigilant use the table Locations that leads to the Vigilantes_idvigilantes, Collaborator_idcollaborators and the Keys_idchaves.

So far so good, but when I search via PHP with the following code:

$rs = $connection->prepare("SELECT  * from  Locacoes INNER JOIN Colaboradores INNER JOIN Vigilantes INNER JOIN Chaves  WHERE DAY(horaSaida) =  DAY(now()) GROUP BY idLocacoes");

if($rs->execute())
{
     while($registro = $rs->fetch(PDO::FETCH_OBJ))
    {

        echo "<TR>";

        echo "<TD>" . $registro->idLocacoes . "</TD>";
        echo "<TD>" . $registro->horaSaida." </TD>";
        echo "<TD>" . $registro->horaEntrada . "</TD>";
        echo "<TD>" . $registro->Colaboradores_idColaboradores.
        echo "<TD>" . $registro->Vigilantes_idVigilantes . "</TD>";
        echo "<TD>" . $registro->Chaves_idChaves . "</TD>";
        echo "<TD>" . $registro->nomeVigilante."</TD>";
        echo "<TD>" . $registro->nomeChave."</TD>";
        echo "<TD>" .$registro->nomeColaboradores."</TD>";

The results of Locations I get perfectly but the nameVigilante, nameChave and nameColaboradores come all repeated must be because the function (PDO::FETCH_OBJ) returns me only the first name of the column.

I have this provisionally at the following address

If you have another solution I’d also like to hear.

Thank you very much in advance.

Cump

Answer 1

I’m not in a position to test your code, but I believe the problem lies in the way you’re merging the tables. When using the JOIN in related tables by foreign key. It is necessary to specify a condition(ON):

...
$rs = $connection->prepare("SELECT  * from  Locacoes 
INNER JOIN Colaboradores ON Colaboradores.idColaboradores = Locacoes.Colaboradores_idColaboradores
INNER JOIN Vigilantes ON Vigilantes.idVigilantes = Locacoes.Vigilantes_idVigilantes
INNER JOIN Chaves  ON Chaves.idChaves =  Locacoes.Chaves_idChaves
WHERE DAY(horaSaida) =  DAY(now()) GROUP BY idLocacoes");
...

I couldn’t test the code, but that way it should work.

A piece of advice. Use simpler names in foreign keys. For example: colaboradores_id instead of Colaboradores_idColaboradores. So you can still easily understand the function of the field and write the queries will be easier.