generate query dynamically with Union

Question

$sql = 'SELECT COUNT(*) FROM ';
$cola = ' UNION '; // Deixar os espaços em branco "em volta".
$colaWhere = ' WHERE ';
$first = true;

$wheres = "";



foreach($arrayNomeBDs as $nomeBD){
    $where = ' status = 2'. ' AND nomePessoa="O aqui vai variar" ';
    if($first == true){
        $wheres .= $sql . $nomeBD .$colaWhere . $where. $cola;
        $first = false;

    } else {
        $wheres .= $sql . $nomeBD .$colaWhere . $where. $cola;
    }
}

He is generating so:

SELECT COUNT(*) FROM tab1 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION SELECT COUNT(*) FROM tab2 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION SELECT COUNT(*) FROM tab3 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION

how do I remove the last Union? what implementation tip could you give me? a good way is to check the last index of the array, if it is I do not put the UNION? same thing if you only have a database name in the array, I should take the UNION

Answer 1

You can use the function rtrim(), beyond that there is the ltrim() and trim() that serve to remove spaces (if not specified parameter) or a text specified in the parameter. In the case of rtrim will only be on the side right, while ltrim is only on the left side and trim both sides. Example:

$sql = 'SELECT COUNT(*) FROM tab1 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION SELECT COUNT(*) FROM tab2 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION SELECT COUNT(*) FROM tab3 WHERE status = 2 AND nomePessoa="O aqui vai variar" UNION';
$sql = rtrim($sql, "UNION");

Exit:

SELECT COUNT() FROM tab1 WHERE status = 2 AND namePessoa="O here will vary" UNION SELECT COUNT() FROM tab2 WHERE status = 2 AND namePersooa="O here will vary" UNION SELECT COUNT(*) FROM Tab3 WHERE status = 2 AND namePersooa="Here will vary"

Ideone Exemplo

Answer 2

I believe to put each query mounted in an array and then use the function implode would also be a way to unite your consultations.

    $wheres[] = 'SELECT * FROM x';
    $wheres[] = 'SELECT * FROM y';
    $wheres[] = 'SELECT * FROM z';

    $wheres_compiled = implode(' UNION ', $wheres);

    var_dump($wheres_compiled); // SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM z

Ideone example

I imagine that, in your case, the desired result is something like below:

$tables = ['table_1', 'table_2', 'table_3'];

$wheres = [];

foreach ($tables as $table) {

    $wheres[] = sprintf('SELECT * FROM %s WHERE nomePessoa="%s"', $table, 'Aqui vai variar');
}

$compiled_with_unions = implode(' UNION ', $wheres);