php – dynamically generate query 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;
    }
}

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 so I don't put the UNION ? the same thing if I only have a database name in the array, I should take the UNION

Answer:

You can use the rtrim() function, besides that there is the ltrim() and trim() which serve to remove spaces ( if no parameter is specified) or a text specified in the parameter. In case the rtrim will be on the right side only, while ltrim is on the left side only 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 personName="This will vary" UNION SELECT COUNT( ) FROM tab2 WHERE status = 2 AND personName="This will vary" UNION SELECT COUNT(*) FROM tab3 WHERE status = 2 AND nomePessoa="This will vary"

IdeOne Example