Get name of the respective PHP table in SELECT UNION

Question

Get name of the respective PHP table in SELECT UNION

Navigation

#1 by (2 votes)

1

Hello, guys, how are you?

Does anyone know how to get the name of the respective table from the pulled value?

I have the following code:

$query= "
SELECT DISTINCT titulo, categoria, views
FROM (
  SELECT DISTINCT titulo, categoria, views FROM secao_aves UNION ALL
  SELECT DISTINCT titulo, categoria, views FROM secao_peixes
) as dados
ORDER BY views DESC
LIMIT 3
";

Then I get the data and start the loop:

//PEGA OS DADOS
if($stmt_mvw = $mysqli->prepare($query)){ /* INICIA DECLARAÇÃO PRINCIPAL */

    //ABRE DECLARAÇÃO
    $stmt_mvw->execute();

    //TRAZ O RESULTADO DA CONSULTA
    $stmt_mvw->bind_result($titulo, $categoria, $views);

    while($stmt_mvw->fetch()){
        echo '<a href="busca.php?table='.$NOME_DA_TABELA.'">'.$titulo.'('.$views.' visualizações)</a>';
    }
}

Let's say my database looks like this:

" secao_aves " table:

id |      titulo      | categoria | views |
1  |      Pardal      |     AA    |  250  |
2  |    Beija-Flor    |     AB    |  100  |
3  |  João-de-Barro   |     AC    |  145  |

" secao_peixes " table:

id |      titulo      | categoria | views |
1  |      Bagre       |     PX    |  180  |
2  |     Dourado      |     PY    |  165  |
3  |     Pintado      |     PZ    |  75   |

So far, easy. For example, this would be printed (the 3 records with more "views"):

<a href="busca.php?table=<?>">Pardal (250 visualizações)</a>
<a href="busca.php?table=<?>">Bagre (180 visualizações)</a>
<a href="busca.php?table=<?>">Dourado (165 visualizações)</a>

But in fact, I'd like it to print like this:

<a href="busca.php?table=secao_aves">Pardal (250 visualizações)</a>
<a href="busca.php?table=secao_peixes">Bagre (180 visualizações)</a>
<a href="busca.php?table=secao_peixes">Dourado (165 visualizações)</a>

In case, I do not know how to pull the name of the table relative to the given, to create the variable $ NAME_TABLE : /

How to proceed? : v

php mysql

asked by anonymous 19.05.2018 / 06:28

1 answer

Doubt calling method with If Java Display content of the "title" attribute next to the option element

score 2 · Accepted Answer

You can create an additional 'dummy' field within SQL by identifying the table:

SELECT DISTINCT titulo, categoria, views, tabela
FROM (
  SELECT DISTINCT titulo, categoria, views, 'secao_aves' as tabela FROM secao_aves UNION ALL
  SELECT DISTINCT titulo, categoria, views, 'secao_peixes' as tabela FROM secao_peixes
) as dados
ORDER BY views DESC
LIMIT 3

So, through the tabela field you get the resulting row source:

titulo  categoria   views   tabela
Pardal  AA           250    secao_aves
Bagre   PX           180    secao_peixes
Dourado PY           165    secao_peixes

See the template at: link