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

Question

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 relational tables, with the respective indexes. But I don’t know much about mysql, I even tried to use the relational tables in phpmyadmin, but I didn’t know how to.

What to do? Obviously I would not sit idly by in front of the pc, so I tried to come up with a solution with a PHP script, since I am more familiar. Here is 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 from a table, and get its id;

2 - use the respective product id 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 presented script, I managed to return the expected results, but as I am studying good code practices, I think this script is not in the way it should be to perform the described tasks. I even called it gambiarra.

So:

What would be the right way to make these queries?

• as I could have done in phpmyadmin/mysql
• as I could have done the script (in case the current one is redundant)
• how this code could be refactored.

Answer 1

1 . First let’s see how your SELECT's 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: http://blog.thiagobelem.net/relacionamento-de-tabelas-no-mysql/

2 . Now we will apply the SELECT in function, leaving it much thinner:

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 . About refactoring code, it’s always good to leave the connection in a separate file, or in a global variable, avoid you passing the connection data in various parts of the code.

In the idea below, we create 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 on the pages that will have access to the database, just include the page connection.php and use the variable connect for access to the bank:

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 its database and tables created here... If you have errors let me know that you have corrected it.