Compare data from two different DBs

1

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']}'");
    
asked by anonymous 19.04.2018 / 16:22

0 answers