PHP Mysql Seach Result

Question

PHP Mysql Seach Result

Navigation

#1 by (0 votes)

0

Good afternoon

I have been trying to resolve this for two days without success and would 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')
)

Partners:

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
);

In short, the Contributor requires a Key Vigilante Watchers_id Watchers, Collaborators_id and Chaves_idChaves .

So far so good, but when I do the 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 the Locacoes get perfectly however the name Tracker, NameColumn, and NameColabers come up all repeated should 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 would also like to hear it.

Thank you in advance.

Cump

php mysql busca

asked by anonymous 29.01.2017 / 18:04

1 answer

How to fetch values in a PHP file with jQuery - AJAX? What information can I get from the user through Java EE?

score 0 · Answer 1

I am not able to test your code, but I believe the problem lies in the way you are doing the merge of the tables. When using JOIN in related tables by foreign key. You must 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 could not test the code, but that should work.

Advice. Use simpler names in foreign keys. For example: colaboradores_id instead of Colaboradores_idColaboradores . So even to easily understand the function of the field and write the queries will be easier.