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

Asked

Viewed 1,174 times

1

How can I do more than one select in the same query? I tried it this way (using UNION), but it didn’t work:

DATABASE

Table: cameras_lines

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

php cameras.

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:

Ele consegue localizar sempre apenas um dos selects, o outro ele dá erro, se eu inverter a ordem, o erro passa para o outro select

Can anyone give a boost? Thanks in advance!

php javascript mysql query select

  • If you do your job return mysql_fecth_assoc($resultado) is enough.

2 answers

3


You can use subqueries.

Behold:

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 column cl.nome_linha_camera as nome_linha_camera, you must do c_linha.nome_linha_camera.

See also

Subqueries: Where and when to use

  • Thank you for your reply. I made the change you suggested and returned the following error: Warning: mysqli_fetch_assoc() expects Parameter 1 to be mysqli_result, Boolean Given in C: wamp64 www admin includes cameras.php on line 38 Line 38: while($camera = mysqli_fetch_assoc($result) { array_push($cameras, $camera); } Return $cameras; }

  • Got it. Did you ever run the query directly in the database? It works?

  • Rodei, he made the following mistake: #1241 - Operand should contain 1 column(s)

  • Take off the c.* subquery (as well as the comma after it). Leave only the column cl.nome_linha_camera as nome_linha_camera in subquery. Try now, please.

  • Oops, thanks for the feedback. I took the c.,* and returned the following error: #1242 - Subconsulta returns more than 1 record

  • I added in the question the structure of the tables of the database, I do not know if this helps in solving the problem. I am since Thursday breaking my head and I can’t solve this problem that is "simple".

  • I changed the code of the post. Please try to run it now. It will be missing a column.

  • I changed it again. Now it will work 100% (I think rsrs).

  • Perfect! Thank you very much. Only one detail instead of C1.name_line_camera is cl.name_line_camera. VALEU!

  • Hehe, blz! I’m glad I helped! You can mark my answer as a solution, please? :-)

Show 5 more comments

2

Try using subquerys and / or joins if they are relational tables, and don’t forget to filter everything by group by if you have replicated data, but keep in mind the structure of your database, because if you grow your system maintenance will be difficult.

Browser other questions tagged php javascript mysql query select

You are not signed in. Login or sign up in order to post.