Distinguish equal fields in a SELECT with LEFT JOIN

Question

Distinguish equal fields in a SELECT with LEFT JOIN

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

In SELECT done in two tables with similar fields I'd like to distinguish the field from each table when it shows the results. For example:

$sql = BD::conn()->prepare("SELECT a.*, b.*
                                FROM tabela_a a
                                LEFT JOIN tabela b ON
                                a.campo = b.campo");
$sql->execute();
$dados = $sql->fetch();

Let's imagine that in both tables I have the field data and I would like to show the field of each distinguished:

 $dados["a.data"] ou $dados["b.data"]

By giving print_r to $dados the array keys are named by table a, while the columns of table b that are the same as those of table a are enumerated increasingly as a common array, there is some way to display the data of each separate?

Note: To minimize the question I did not put while in the search

php mysql pdo

asked by anonymous 24.02.2017 / 14:12

2 answers

1

You can create aliases for your fields in your select, so they would have names, eg:

SELECT a.campo1 as campo1A, a.campo2 as campo2A, a.data as dataA, b.data as dataB
FROM tabela_a a
LEFT JOIN tabela b ON
a.campo = b.campo

24.02.2017 / 14:16

Git, upload codes directly to the server How do I make a switch button to switch css?

score 2 · Accepted Answer

You will need to manually name these specific columns in order to be able to "pick them up" later within the while.

Example

$sql = BD::conn()->prepare(
    "SELECT 
    a.campo1, a.campo2, a.campo3, a.campo_igual as a_campo_igual,
    b.campo1, b.campo2, b.campo3, b.campo_igual as b_campo_igual
    FROM tabela_a a
    LEFT JOIN tabela b ON a.campo = b.campo"
);

If you want to add the table as a prefix for each column

So you can pick up the "equal fields" with no problem in the while.

// array para informar qual coluna pertence a qual tabela
// irei processar isso depois para formar o apelido "tabela_x_coluna_y"
// é muito provável que este array seja preciso montar manualmente, 
// mas irá facilitar sua vida no SELECT
$tabelas = [
    'tabela_a' => [
        'campo1',
        'campo2',
        'campo3',
        'campo_igual',
    ],
    'tabela_b' => [
        'campo1',
        'campo2',
        'campo3',
        'campo_igual',
    ]
];

// array com as colunas apelidadas, é preenchido no foreach abaixo
// vou usar este array em um implode depois para facilitar o uso da vírgula que separa cada coluna
$columnsSql = [];

foreach ($tabelas as $tabela => $colunas) {  
    foreach ($colunas as $coluna) {
        $colunaDB      = $tabela.'.'.$coluna; // é a coluna com prefixo da tabela
        $colunaApelido = $tabela.'_'.$coluna; // é a coluna com apelido, para você poder pegar depois no while

        $columnsSql[] = $colunaDB.' AS '.$colunaApelido
    }
}

$sql = BD::conn()->prepare(
    "SELECT ".implode(', ', $columnsSql)."
    FROM tabela_a a
    LEFT JOIN tabela b ON a.campo = b.campo"
);

UPDATE

According to documentation in the PDO connection you can set the PDO::ATTR_FETCH_TABLE_NAMES attribute to:

Uses the name of the table as a prefix in each column name returned in the result set. The table name and the column name are separated by a decimal (.) Character. Support for this attribute is at the level driver; may not be supported by your driver.

Credits: this answer and here is .