Show query (Problem Different Id’s)

Asked

Viewed 220 times

-3

I have a query a little big but then when I ask to show it comes with mixed id’s.

I have something wrong in the query? 

 SELECT *  FROM tb_detalhe_trabalhador1 inner join tb_empresa on 
tb_detalhe_trabalhador1.id = tb_empresa.id 
Inner Join tb_detalhe_trabalhador2 on tb_detalhe_trabalhador2.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador3 on tb_detalhe_trabalhador3.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador4 on tb_detalhe_trabalhador4.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador5 on tb_detalhe_trabalhador5.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador6 on tb_detalhe_trabalhador6.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador7 on tb_detalhe_trabalhador7.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador8 on tb_detalhe_trabalhador8.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador9 on tb_detalhe_trabalhador9.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador10 on tb_detalhe_trabalhador10.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador11 on tb_detalhe_trabalhador11.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador12 on tb_detalhe_trabalhador12.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador13 on tb_detalhe_trabalhador13.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador14 on tb_detalhe_trabalhador14.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador15 on tb_detalhe_trabalhador15.id = tb_empresa.id
Inner Join tb_detalhe_trabalhador16 on tb_detalhe_trabalhador16.id = tb_empresa.id
Inner Join tb_equipamentos on tb_equipamentos.id = tb_empresa.id
WHERE AlvaraValidade or AcidenteValidade or SeguroValidade or FinancasValidade or 
SocialValidade or RemuneracaoValidade or InstaladorValidade or MontadorValidade or
 MedicaValidade or ProjectistaValidade or GasValidade or RedesValidade or
 SoldadorValidade or MecanicoValidade or ClasSoldadorValidade or MaquinaValidade1 or
 MaquinaValidade2 or MaquinaValidade3 or MaquinaTopoValidade BETWEEN CURDATE() AND
 CURDATE() + INTERVAL 10 DAY";

  //Faço isto primeiro
  if (($row[11] != '0000-00-00' && estaParaExpirar($row[11], "10")) 
    or ($row[12] != '0000-00-00' && estaParaExpirar($row[12], "10"))
    or ($row[13] != '0000-00-00' && estaParaExpirar($row[13], "10")) 
    or ($row[14] != '0000-00-00' && estaParaExpirar($row[14], "10")) 
    or ($row[15] != '0000-00-00' && estaParaExpirar($row[15], "10")) 
    or ($row[16] != '0000-00-00' && estaParaExpirar($row[16], "10")) 
    or ($row[17] != '0000-00-00' && estaParaExpirar($row[17], "10"))) {     
 $Nome1 = '<p>Nome: '.$row[10].'</p>' ;}
 if ($row[11] != '' && ($row[11] != NULL && ($row[11] != '0000-00-00' ))) {
 $MedicaValidade = estaParaExpirar($row[11], "10") ? '<p>
 A data da Ficha de aptidao  Medica vai expirar no dia '.$row[11].'</p>' : '';}

 //Depois no corpo do email faço so isto
 // corpo da mensagem
  $PHPMailer->Body = "<body>
  <br>
 ".$Trabalhador."
 ".$Nome1."
 ".$MedicaValidade."
php query

  • Assuming the consult is returning what you want in yours SELECT, specifies the source of the fields, i.e.: SELECT * FROM tb_detalhe_trabalhador1 should be something like: SELECT tb_detalhe_trabalhador1.* FROM tb_detalhe_trabalhador1 assuming the desired fields are those of the table tb_detalhe_trabalhador1.

  • I have. I just have to not get as extensive. But when I do to show the data it shows me data from Id 1, 4, 3.. I just want it to show the data of Id1 after id2 and so on. Because I want the fields of all tables

  • Hmmm... are you referring to sorting the results? End the query with: ORDER BY tb_detalhe_trabalhador1.id.

  • Just for one or I have to do it for all the tables?

  • The ORDER BY is the sorting of the results after the query, you should apply to one of the selected fields. In your case, the field tb_detalhe_trabalhador1.id seems to fit your purpose.

  • But it’s not working. I put that code in and keep mixing the id’s. This code will serve to select the data that will be sent by email and in the email comes several id’s. I want each email to have the data of your id

  • 9

    If they hire 17 the HR needs to warn you to change the query , normalize this table.

  • but there won’t be more. Only asked to put 16.

  • You have created a table for each employee?

  • which are the columns of the tables?

Show 5 more comments

3 answers

4

I imagine your joins are wrong:

tb_details_worker1.id = tb_company.id
tb_equipment.id = tb_company.id

tb_company

id company name
1 Samsumg
2 HP
3 Dell

tb_equipment

id equipment
1 computer
2 laptop
3 scanner

tb_details_worker 1

id name
1 John
2 Mary
3 Peter

You should check which are the primary keys and relate them correctly. tb_details_worker.id_company = company.id
tb_equipamentos.id_empresa = company.id

Try doing the INNER JOINS and the WHERE bit by bit, preferably table by table to make sure the results are matching the expected.

  • but I tested the query in mysql and it works correctly. It shows all the data as query. When sending the email is that the ids stay in some places are changed

2

First, normalize your database. Even though the company or department will only have 16 employees, it is a very bad practice to keep 16 tables in your database, because if another programmer has to tinker with your system or create another program that uses your bank, it will surely get lost, and the performance of the database itself will be compromised by the excess of tables.

OBS.: Normalization is the pre-organization of the data and the separation and removal of duplicate data. Wikipedia and Microsoft(English)

After you have normalized the database, recreate its query according to the new tables created in the standardization. In addition to being much more readable, it increases the performance of your application.

And then finally use the clause ORDER BY to organize query results.

2

In accordance with Mr. @Motta, the correct is to make A table to record the details of each worker so being the same rule for the table where records the equipment, and company ( assuming that there you register the affiliates, matrix etc).

After normalization, make an inquiry conditioning tb_worker, tb_equipment, tb_company through their respective Pk and/or Fk keys and the last filter between to pick between the desired period.

  • I had to see how I show the data. is possible in null fields it will fetch the id of another filled id?

  • yes using Join or Union it looks for null results.

  • Imagining ". $Exempl0." ". $Exemplo2." one of them is null . Example. id2, (Empty = NULL) it can fetch data from id 3 because id is empty?

  • When results exist NULL is used Left Join

Browser other questions tagged php query

You are not signed in. Login or sign up in order to post.