JSON accessing two databases

Question

I have two darlings:

SELECT Data, NomeDis, AlunID
FROM FaltaDupla
ORDER BY Data, NomeDis, AlunID

SELECT lNum FROM Aula
WHERE lDisciplina = 
AND lData = 

Derived from a Delphi function. The first is from a Web Bank and while not finishing the second from a Local Bank is executed. How can I generate a JSON that uses both, and for that I will have to make a connection to the web bank and another connection to the local bank?

I tried that way, but I can only perform SELECT from the Web part:

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$opt = 
[
    PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES      => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);
GLOBAL $pdo;

$sql = $pdo->query("SELECT Data, NomeDis, AlunID FROM {$pfx}FaltaDupla ORDER BY Data, NomeDis, AlunID")->fetchall(PDO::FETCH_ASSOC);        
echo json_encode($sql);

Answer 1

You will need to create another connection for the local part (since apparently you already made the web one), as you did in the variable $pdo.

Then with PDO, you create a new query only instead of using $pdo->query you will have to use the local connection variable ($pdo you used for connection to the web bank). Let’s say the local connection will be $pdo2, you will use the query $pdo2->query(SEU SELECT AQUI);.

Let’s say you created a new connection to the site on the variable $pdo2 and for your select, from that connection, used the variable $sql2. Thus remaining:

$sql2 = $pdo2->query(SEU SELECT AQUI);

To use both in a single Json you do the following:

json_enconde(['web'=>$sql, 'local'=>$sql2]);

Here it was used in the parameter of the json_encode an array that has two key-values, "web" receives all data that comes from select of query web connection (which you defined in the variable $sql) and "local" receives all data coming from select of query local connection (which I set as example the variable $sql2).

Finally, your code would look something like this:

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$opt = 
[
    PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES      => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$pdo2 = new PDO(INFORMACOES DA CONEXAO LOCAL);

GLOBAL $pdo;
GLOBAL $pdo2;

$sql = $pdo->query("SELECT Data, NomeDis, AlunID FROM {$pfx}FaltaDupla ORDER BY Data, NomeDis, AlunID")->fetchall(PDO::FETCH_ASSOC);

$sql2 = $pdo2->query(SEU SELECT AQUI)->fetchall(PDO::FETCH_ASSOC);

echo json_enconde(['web'=>$sql, 'local'=>$sql2]);