Is it possible to execute multiple queries in a single execution?

Question

Is it possible to execute multiple queries in a single execution?

Navigation

#1 by (2 votes)

1

I needed some data from a database in mysql. However, the data were in different tables, so the most obvious solution was to filter using the relational tables, with their indexes. But I do not know much about mysql, I even tried to use the relational tables in phpmyadmin, but I did not know how to do it.

What to do? Obviously I would not sit idly in front of the pc, so I tried to create a solution with a script in PHP, since I'm more familiar. Here's the script:

<?php

function queryDB($user, $pass)
{
    $connect = new PDO('mysql:host=localhost;dbname=foobar', $user, $pass);

    foreach($connect->query("SELECT id, titulo FROM produtos WHERE publicado > 0") as $produto)
    {
        print($produto['id'].' -> '.$produto['titulo'].'<br>');

        $id = $produto['id'];

        $assuntos = $connect->query("SELECT id_assunto FROM produtos_tem_assuntos WHERE id_produto = $id");

        foreach($assuntos as $assunto_id)
        {
            foreach($connect->query("SELECT titulo FROM assuntos WHERE id = $assunto_id[0]") as $final)
            {
                print('<pre>');
                var_dump($final[0]);
                print('</pre>');
            }
        }
    }
}

print(queryDB('root', 'admin'));

The goal was:

1 - filter the product of a table, and get your id;

2 - use the id of the respective product to filter the subject id (which was in another table);

3 - use the subject id to finally show the subject (which was also in another table).

With the script presented, I was able to return the expected results, but as I'm studying good code practices, I think this script is not the way it should be to perform the tasks described. I even called it gambiarra .

Then:

What would be the correct way to do these queries?

how could I have done in phpmyadmin / mysql
how could I have done the script (if the current one is redundant)
how refactoring of this code could be done.

php mysql algoritmo phpmyadmin

asked by anonymous 29.12.2015 / 21:15

1 answer

wkHtmlToPdf - Header and footer on all pages 'show more' script adaptation with javascript

score 2 · Accepted Answer

1. First let's see what your SELECT's would look like if we use JOIN :

SELECT pr.id, pr.titulo, pt.id_assunto, as.titulo 
FROM produtos pr 
INNER JOIN produtos_tem_assuntos pt ON pr.id = pt.id_produto
INNER JOIN assuntos as ON pt.id_assunto =  as.id
WHERE pr.publicado > 0;

A good study material for relationships between tables is the link below: link

2. Now let's apply the SELECT to the function, making it much more rusty:

function queryDB($user, $pass)
{
    $connect = new PDO('mysql:host=localhost;dbname=foobar', $user, $pass);

    $sql = "SELECT pr.id, pr.titulo, pt.id_assunto, as.titulo 
            FROM produtos pr 
            INNER JOIN produtos_tem_assuntos pt ON pr.id = pt.id_produto
            INNER JOIN assuntos as ON pt.id_assunto =  as.id
            WHERE pr.publicado > 0";

    $retorno = $connect->query($sql);

    // Testamos o retorno para não dar Warning
    if(count($retorno) > 0)
    {
        foreach( $retorno as $linha )
        {
            // Aqui fica sua lógica do que fazer com o retorno...
            print('<pre>');
            var_dump($linha);
            print('</pre>');
        }
    }
}

3. On refactoring the code, it is always good to leave the connection in a separate file, or in a global variable, prevents you from passing the connection data to various parts of the code.

In the idea below, we created a file connection.php just to define the connection:

$user = "seu-usuario";
$pass = "sua-senha";
$connect = new PDO('mysql:host=localhost;dbname=foobar', $user, $pass);

Then in the pages that will have access to the database, just include the page connection.php and use the connect variable to access the database:

include_once "connection.php";

function queryDB()
{
    // Pegamos a connecção global
    global $connect;

    $sql = "SELECT pr.id, pr.titulo, pt.id_assunto, as.titulo 
            FROM produtos pr 
            INNER JOIN produtos_tem_assuntos pt ON pr.id = pt.id_produto
            INNER JOIN assuntos as ON pt.id_assunto =  as.id
            WHERE pr.publicado > 0";

    $retorno = $connect->query($sql);

    // Testamos o retorno para não dar Warning
    if(count($retorno) > 0)
    {
        foreach( $retorno as $linha )
        {
            // Aqui fica sua lógica do que fazer com o retorno...
            print('<pre>');
            var_dump($linha);
            print('</pre>');
        }
    }
}

*** Note: The solution presented was not tested because I did not have your database and tables created here ... If you have any errors, please let me know.