generate query dynamically with union

Question

generate query dynamically with union

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

$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;
    }
}

It is generating like this:

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 I do not put UNION ? the same thing if I only have a database name in the array, I should get the UNION

php mysql

asked by anonymous 01.07.2015 / 18:33

2 answers

1

You can use the rtrim() function, in addition there is ltrim() and trim() that are used to remove spaces (< in> if unspecified parameter) or a text specified in the parameter. In the case of rtrim will only be on the right side, 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");

Output:

SELECT COUNT () FROM tab1 WHERE status = 2 AND namePerform="The here will vary" UNION SELECT COUNT () FROM tab2 WHERE status = 2 AND nomePessoa="O here vai vary" UNION SELECT COUNT (*) FROM tab3 WHERE status = 2 AND nomePessoa="The here will vary"

IdeOne Example

01.07.2015 / 18:40

DNS update cpanel How to Do Transition in the Hover

score 0 · Accepted Answer

I think placing each query mounted on an array and then using the implode function would also be a way to merge your queries.

    $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);