I have 3 tables:
- "Clients" with id, client_code, c_id (client's CRM code), name, and cif.
- "Machines", with the fields id, id_maq (CRM code), install_date, duration, tariff_id, serial_num, customer_code, customer_id (crm code), cost_id and expiration.
- "Counter" is based on the following columns (upload via CSV): id, model, num_series, date_counter, page_color, page_bn, billed.
This table is related to the other 2 so much to show the data of the counters of the machines individually as by group of companies (all the machines that a company has) to later fill the invoice in the crm without having to type line to line.
The normal thing in the "Accountant" table is that at least 2 times a month through CSV the data of these machines is dumped at least a month and as an example the first time the data is dumped, the following happens:
num_serie:A,B,D
fecha_contador:25/12/2017,24/12/2017,15/12/2017
page_color:25.000,23.000,4.000
page_bn:100.000,150.000,3.000
facturado:1,1,1
The second time the data is dumped, we have:
num_serie:A,B,C,D
fecha_contador:12/01/2018,12/01/2018,08/01/2018,10/01/2018
page_color:26.000,27.000,5.000,5.500
page_bn:110.000,155.000,3.200,3.500
facturado:0,0,1,0
The values of the "billed" column allow me to know the previous (1) and current (0) data to perform the calculations for billing.
So the previous color would show for each machine (25,000,23,000,4,000,5000) and the current color should show < strong> (26,000,27,000, no data, 5,500) and what it shows is (26,000,27,000,5,500, ---)
Seeing the data, when you made the query grouped by machines, A, B, C and D will show me the previous data that are those of December of 2017 for A, B and D and of 2018 for C (1,1, 1.1) but in the current ones of January of 2018 the machines A and B will show me the correct current data but C and D no. C should show me the label "No data" and D its corresponding value, but what happens is that C takes the value of D and D shows me his data in white. It shows me the values (0,0,0) and it should be (0,0, no data, 0).
As you can see starts with 3 machines and their corresponding values, then data is dumped where a new machine appears. At the end I add a new image, where you can see the fields to be filled in (the data inside the red circle are erroneous since they should say "without data").
I show you the code:
<td>
<?php
//Actual Color
$sql="SELECT * FROM(SELECT distinct m.num_serie,cl.cod_cliente,nombre,MAX(page_color) as pc,facturado,fecha_contador FROM contador as co,maquinas as m INNER JOIN cliente as cl ON (m.cod_cliente = cl.cod_cliente) where cl.cod_cliente='".$_GET['empresa']."' and co.num_serie=m.num_serie and facturado=0 group by num_serie asc) cont group by num_serie asc";
$resultadosc = mysqli_query($conexio,$sql);
if ($row = mysqli_fetch_array($resultadosc)){
do {
?>
<input type="text" name="Q_color_actual[]" id="contacto1" style="border:none; width:80px; height:40px;" value="<?php echo $row['pc']; ?>"/>
<?php } while ($row = mysqli_fetch_array($resultadosc));
} else { echo 'Sin Datos'; } ?>
</td>
I have tried with while if else and follow the same result, if you see in the image the data in red correspond to the last value and not to that, which is where the text "Without data" should appear, since the row it does not exist for the value 0.
Some solution to this problem would be appreciated. I hope now you understand
New image
