Good morning,
I have the following scenario: BD1 from third parties where I can only perform queries. I created a web page to display the data from BD1, in case it would be a table where I have a field with a combobox that I have some options to select.
Problem: I need to save the state of the combobox according to the ID of the table so that every time it loads it shows the information that has been selected.
Attempt: I created a local BD2, to record the information in this case it writes the ID of the BD1 and the selected option of combobox, I thought to do an INNER JOIN between the banks but I did not find anything that could help me and also I am not sure if this would be the best solution.
Codes:
$q_db2 = $conn2->query("SELECT monitor.infos.iStatus, monitor.infos.iCliente FROM monitor.infos");
$data = array();
while($rows2 = $q_db2->fetch_assoc()){
$q_db1 = $conn->query("SELECT OS.OS, OS.DATA, OS.CONTRA, OS.TIPO, OS.EQUIPE, OS.TECNICO, OS.TECNICO_AUX1, OS.DTAGEN, OS.UF, EQ.NOME AS EQNOME, TP.NOME AS TPNOME, TC.NOME AS TCNOME, TCX.NOME AS TCXNOME
FROM dados.tva1710 AS OS
INNER JOIN dados.tva1800 AS EQ
ON EQ.CODIGO = OS.EQUIPE
INNER JOIN dados.at_os AS TP
ON TP.CODIGO = OS.TIPO
INNER JOIN dados.tva1900 AS TC
ON TC.CODIGO = OS.TECNICO
INNER JOIN dados.tva1900 AS TCX
ON TCX.CODIGO = OS.TECNICO_AUX1
WHERE OS.DTAGEN != '0000-00-00' AND OS.DTAGEN > '2018-01-01' AND OS.UF = 'SP'");
while($rows = $q_db1->fetch_assoc()){
$arrayData = array();
$arrayData[] = utf8_encode($rows['OS']);
$arrayData[] = utf8_encode($rows2['iCliente']);
$arrayData[] = utf8_encode($rows['CONTRA']);
$arrayData[] = utf8_encode($rows['TPNOME']);
$arrayData[] = utf8_encode($rows['EQNOME']);
$arrayData[] = utf8_encode($rows['TCNOME']);
$arrayData[] = utf8_encode($rows['TCXNOME']);
$arrayData[] = utf8_encode($rows['DTAGEN']);
$arrayData[] = utf8_encode($rows2['iStatus']);
$data[] = $arrayData;
}
}
$results = array(
"sEcho" => 1,
"iTotalRecords" => count($data),
"iTotalDisplayRecords" => count($data),
"aaData" => $data);
echo json_encode($results, JSON_UNESCAPED_UNICODE) ;
exit;
I tried to create a where matching the ID fields of BD1 with the ID field of BD2 but it only returned me the data that was recorded, so it did not work either.
WHERE OS.DTAGEN != '0000-00-00' AND OS.DTAGEN > '2018-01-01' AND OS.UF = 'SP' AND OS.OS = '{$rows2['iCliente']}'");