Query with connection to more than one mysql php database

Question

I have 3 databases and I’m searching for information on the three, but in my first query I’ve come across an error:

It is as if the query is searching in another database that is not what I chose,follow below my query,the error message and my connection file.

Consultation:

 $query_pesquisa = mysql_query(" SELECT 
    o.numero AS CHAMADO,
    p.problema AS PROBLEMA,
    o.descricao AS DESCRICAO,
    o.contato AS CONTATO,
    o.telefone AS TELEFONE,
    o.data_abertura AS DATA_DE_ABERTURA,
    TIMESTAMPDIFF (HOUR, str_to_date(data_abertura,'%Y-%m-%d'), CURDATE()) as HORAS_EM_ABERTO,
    i.inst_nome AS UNIDADE,
    a.sistema AS AREA,
    l.local AS SETOR,
    u.nome AS TECNICO,
    ua.nome AS ABERTO_POR,
    s.status AS STATUS
    /*sls.slas_tempo AS TEMPO_DE_SOLUCAO*/
FROM
    ocorrencias AS o
        LEFT JOIN
    sistemas AS a ON a.sis_id = o.sistema
        LEFT JOIN
    localizacao AS l ON l.loc_id = o.local
        LEFT JOIN
    instituicao AS i ON i.inst_cod = o.instituicao
        LEFT JOIN
    usuarios AS u ON u.user_id = o.operador
        LEFT JOIN
    usuarios AS ua ON ua.user_id = o.aberto_por
        LEFT JOIN
    `status` AS s ON s.stat_id = o.status
        LEFT JOIN
    status_categ AS stc ON stc.stc_cod = s.stat_cat
        LEFT JOIN
    problemas AS p ON p.prob_id = o.problema
        LEFT JOIN
    sla_solucao AS sls ON sls.slas_cod = p.prob_sla
        LEFT JOIN
    prioridades AS pr ON pr.prior_cod = l.loc_prior
        LEFT JOIN
    sla_solucao AS slr ON slr.slas_cod = pr.prior_sla
        LEFT JOIN
    script_solution AS sol ON sol.script_cod = o.oco_script_sol
        LEFT JOIN
    prior_atend AS prioridade_atendimento ON prioridade_atendimento.pr_cod = o.oco_prior
WHERE
        a.sistema = 'Ti'
        AND o.status IN (1 , 2)
ORDER BY numero ASC",$ConnOcomon)or die(mysql_error());

    if (empty($query_pesquisa)) {
    echo "Nenhum registro encontrado.";
    }
    $num_rows = mysql_num_rows($query_pesquisa);

Connection:

<?php
   header('Content-Type: text/html; charset=utf-8');

 //BANCO FOLLOWUP
$local_serve1       = "127.0.0.1";  
$usuario_serve1     = "root";     
$senha_serve1       = "";             
$banco_de_dados1    = "bigchamados";      
$ConnChamados       = mysql_connect($local_serve1,$usuario_serve1,$senha_serve1) or die ("O servidor não responde!");
$db1                = mysql_select_db($banco_de_dados1,$ConnChamados)            or die ("Não foi possivel conectar-se ao banco de dados!"); 

// BANCO OCOMON
$local_serve2       = "127.0.0.1";  
$usuario_serve2     = "root";     
$senha_serve2       = "";             
$banco_de_dados2    = "ocomon_rc6";      
$ConnOcomon         = mysql_connect($local_serve2,$usuario_serve2,$senha_serve2) or die ("O servidor não responde!");
$db2                = mysql_select_db($banco_de_dados2,$ConnOcomon)              or die ("Não foi possivel conectar-se ao banco de dados!");

//BANCO USUÁRIOS
$local_serve3       = "127.0.0.1";
$usuario_serve3     = "root";
$senha_serve3       = "";
$banco_de_dados3    = "bigcadastro";
$ConnUser           = mysql_connect($local_serve3,$usuario_serve3,$senha_serve3) or die ("O servidor não responde!");
$db3                = mysql_select_db($banco_de_dados3,$ConnUser)                or die ("Não foi possivel conectar-se ao banco de dados!");


  mysql_query("SET NAMES 'utf8'");
  mysql_query('SET character_set_connection=utf8');
  mysql_query('SET character_set_client=utf8');
  mysql_query('SET character_set_results=utf8'); 
?> 

Error message:

Table 'bigcadastro.ocorrencias' doesn't exist

Answer 1

First question and check the database, its internal structure. As it was not posted I will assume that the database ocomon_rc6 really has the table ocorrencias. Second, the code you posted is incomplete, you should post the whole code so you can understand its logic.

But basically it’s as follows, the bank you’re using doesn’t have the table you’re accessing (obviously) of the two:

1. you are passing the wrong connection to perform that query;
2. you’re unintentionally omitting the link_identifier connection, so PHP uses the last link opened by mysql_connect(), in the documentation available here you can check this behavior:

   link_identifier The Mysql connection. If the Identifier link is not specified, the last link opened by mysql_connect() is used. If a previous connection not existing will be tried to create one from a chadama a mysql_connect() without any argument. If no connection is found or established, an E_WARNING level error is generated.

Answer 2

There is not much to complement, but the fact of not seeking in the right place is why it is not indicating the place to be sought,

Example:

$sql = '...';

// Para buscar no banco bigchamados
mysql_query( $sql, $db1 );

// Para buscar no banco ocomon_rc6
mysql_query( $sql, $db2 );

// Para buscar no banco bigcadastro
mysql_query( $sql, $db3 );

Answer 3

The caution that must be taken, in systems with more than one BD, is that it logically becomes a system with several CONNECTIONS.

What I see are the data and all connecting at the same time, so do the connection file include.

My first suggestion: separate the connections, give the include of the main, and when using the others, do not forget to use mysqli_close().

My second suggestion: If you need ALL to be connected at the same time, I’m afraid you should take a look at webservers, thus, a data competition is seen in a more organized way. In English, the PHP website shows something with competing connections

My third suggestion: websockets. As your conception of the word says:

is a technology that allows bi-directional communication by full-duplex channels over a single socket Transmission Control Protocol (TCP). It is designed to run on browsers and web servers that support HTML5, but can be used by any client or application server.