Display data in a row of a table if 2 data match

Question

Display data in a row of a table if 2 data match

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a record of entry and exit of each worker (each one has an ID) where each of them only enters their user code and records their schedules, but it happens that sometimes they are forgotten. How can I display the data in the same row of a table (AS SEEN IN THE PICTURE) according to the registration date?.

Here is the structure of the 4 tables (income labor, output labor, lunch income, lunch output), the fields I need to show are the date and time. I explain, if I need to obtain a worker's schedule report, I filter it for your ID and so I get the dates and times of the different days that you registered, but what happens if I forget to register your entry or exit one day, obviously will not there be data in those fields, how could I do it to show the information?

This is how I intend to show the information in the html table, in a row it shows the user's ID, the date of registration, and the hours of admission, departure from work and refreshments, but if he forgot to record a schedule then the corresponding cell should be blank (the image is red).

mysql php phpmyadmin

asked by Jair B. 20.09.2018 в 20:35

source

3 answers

Error "Access-Control-Allow-Origin is not allowed" with Vue.js How can I format a String to date with PHP

score 1 · Answer 1

Assuming the tables are called this way. entranceLabor, exitLabor, ingresaLmuerso and exitLunch we make a query in which will try all the associated records and in case the field is null it will default by default that 'No Resgistro' and thus would be the SQL

$sql="SELECT ingresoLabor.fecha, IFNULL(ingresoLabor.hora, 'No resgistro'), salidaLabor.fecha, IFNULL(salidaLabor.hora, 'No resgistro'), ingresoAlmuerso.fecha, IFNULL(ingresoAlmuerso.hora, 'No resgistro'), salidaAlmuerzo.fecha, IFNULL(salidaAlmuerzo.hora, 'No resgistro')
FROM ingresoLabor 
  INNER JOIN salidaLabor ON ingresoLabor.user_dni = salidaLabor.user_dni
  INNER JOIN ingresoAlmuerso ON ingresoLabor.user_dni = ingresoAlmuerso.user_dni 
  INNER JOIN salidaAlmuerzo  ON ingresoLabor.user_dni = salidaAlmuerzo.user_dni
WHERE ingresoLabor.fecha = '2018-09-24' AND ingresoLabor.user_dni = 71454870";

Although for reasons of agility I recommend you and if you are not obligated to use the 4 tables to do it in a single table that contains the fields What do you need, something like this:

Tabla_Registro:

id
user_dni
personal
fecha
hora_ini_lab
hora_fin_lab
hora_ini_alm
hora_fin_alm

So with this field the query would be much easier, notice that you skipped the month field, since with the date field you can get the month, the query would be like this:

$sql="SELECT user_dni AS Usuario DNI, fecha AS Fecha, IFNULL(hora_ini_lab, 'No resgistro') AS 'Inicio Labor', IFNULL(hora_fin_lab, 'No resgistro') AS 'Fin Labor',  IFNULL(hora_ini_alm, 'No resgistro') AS 'Inicio Almuerzo', IFNULL(hora_fin_alm, 'No resgistro') AS 'Fin Almuerzo'
FROM Tabla_Registro 
WHERE ingresoLabor.fecha = '2018-09-24' AND ingresoLabor.user_dni = 71454870";

I hope you serve, you tell me how it was ...

score 0 · Answer 2

Suppose that your tables are: ing_lab, sal_lab, ing_alm and sal_alm, the solution I propose is the following:

SELECT ing_lab.fecha, ing_lab.hora, sal_lab.fecha, sal_lab.hora, ing_alm.fecha, ing_alm.hora, sal_alm.feche, sal_alm.hora
FROM ing_lab INNER JOIN sal_lab ON ing_lab.user_dni=sal_lab.user_dni
INNER JOIN ing_alm ON ing_lab.user_dni = ing_alm.user_dni 
INNER JOIN sal_alm  ON ing_lab.user_dni = sal_alm.user_dni;

This way you only show the dates and times of each table, maybe you need to show the ID of the user in question, then you must include in the SELECT the user_dni so that it is also displayed.

score 0 · Answer 3

You will have to make an arrangement for each time you make a echo of the information, take the date and for each day between the previous one and the new one that is missing (with a for), create the boxes that you are missing.

I do not know if it's the best, but it's the only way I can think of it. Unless you prepare something so that every day at least the record of the day is saved once.

<?php
$fechaAntigua = "";
foreach ($datos as $valor) {

  $fecha = $valor->fechaBBDD;//Recuperamos la fecha que nos viene

  $comprobraFecha = $fecha.explde("/", $fecha); //Creamos array separado por "/".
  $comprobraFechaAntigua = $fecha.explde("/", $fechaAntigua); //Creamos array separado por "/".

  if($comprobraFecha[0] != ($comprobraFechaAntigua[0] + 1)){ //Comparamos el día

     //En el caso de que el día no sea el siguiente a la que hemos imprimido
     for($x=($comprobraFechaAntigua[0] + 1); $x < $comprobraFecha[0]; $x++){

        //Recorremos los días faltantes y los imprimimos en la tabla
        //Aquí se imprimiría la fila $echo "<tr></tr>". No lo pongo todo.

     }
  }
  //Aquí se imprimiría la fila correspondiente a lo que nos viene de la base de datos 
  //$echo "<tr></tr>". No lo pongo todo.

  $fechaAntigua = $fecha; //Aquí se guarda la anterior para poder comparar.
}

?>

This would be an example although it lacks some checks since it did not want to be very extensive.