How to make more than one select in the same query?

Question

How to make more than one select in the same query?

Navigation

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

1

How can I make more than one select in the same query? I tried the following (using UNION), but it did not work:

DATABASE

Table: lines_cameras

id_camera_linha | nome_linha_camera

Table: Manufacturers

id_fabricante | nome_fabricante

Table: cameras

id_camera | fabricantes_id_fabricante (chave estrangeira) | cameras_linhas_id_camera_linha (chave estrangeira) | modelo_camera

cameras.php

function listaCameras($conexao) {
    $cameras = array();
    $resultado = mysqli_query($conexao,"(select c.*, f.nome_fabricante as nome_fabricante from cameras as c join fabricantes as f on c.fabricantes_id_fabricante = f.id_fabricante)
    UNION ALL
    (select c.*, cl.nome_linha_camera as nome_linha_camera from cameras as c join cameras_linhas as cl on c.cameras_linhas_id_camera_linha = cl.id_camera_linha)");

    while($camera = mysqli_fetch_assoc($resultado)) {
        array_push($cameras, $camera);
    }

    return $cameras;
}

index.php

 <?php 
     require_once $_SERVER["DOCUMENT_ROOT"] . "/admin/includes/connect.php";
     require_once $_SERVER["DOCUMENT_ROOT"] . "/admin/includes/cameras.php";

    <?php
        $cameras = listaCameras($conexao); //função para listar os produtos (verificar arquivo includes/produtos.php)

    ?>
        <table class="table table-striped table-bordered">

    <?php
        foreach($cameras as $camera) {
    ?>

        <tr>
            <td><?= $camera['nome_fabricante'] //mostra o produto?></td>
            <td><?= $camera['nome_linha_camera'] //mostra o produto?></td>
            <td><?= $camera['modelo_camera'] //mostra o produto?></td>
             <td><a class="btn btn-primary" href="edit.php?id_produto=<?=$camera['id_camera']?>">editar</a> <!-- botão editar -->
            <td>
                <form action="delete.php" method="post"> <!-- botão deletar -->
                    <input type="hidden" name="id_produto" value="<?=$camera['id_camera']?>" />
                    <button class="btn btn-danger">X</button>
                </form>
            </td>
        </tr>

And the result is this:

Can anyone give a force? Thank you in advance!

php mysql javascript query select

asked by anonymous 09.01.2017 / 13:23

2 answers

2

Try to use subquerys and / or joins if it is a relational table, and do not forget to filter everything by group by if you have data replicated, but pay attention to the structure of your database, because in case it grows the maintenance of your system it will be difficult.

09.01.2017 / 13:59

What is the correct way to choose the types of columns? [closed] Effects with jquery

score 3 · Accepted Answer

You can use subqueries .

See:

select 
    c.*, 
    f.nome_fabricante as nome_fabricante, 
    temp_sql.nome_linha_camera
from 
     (select 
           c.id_camera,
           c1.nome_linha_camera
      from 
           cameras as c 
           join cameras_linhas as cl 
           on c.cameras_linhas_id_camera_linha = cl.id_camera_linha
      ) temp_sql 
      join cameras as c 
      on temp_sql.id_camera = c.id_camera
      join fabricantes as f 
      on c.fabricantes_id_fabricante = f.id_fabricante

That way, if you want to access the cl.nome_linha_camera as nome_linha_camera column, you should c_linha.nome_linha_camera .

How to make more than one select in the same query?

2 answers

See also