Consolidating repetitive queries in php-CodePudding

Currently, I have an array with elements and four queries that are similar just using different elements from the array. Roughly the situation seems like this (simplified)

$myArray = array('element1', 'element2', 'element3', 'element4');
foreach ( $myArray as $tors_array ) {
    $element[] = $tors_array;
    
}

$query = "SELECT en as country, $element[0] as value 
                FROM administrative
                WHERE year=2021 
                AND $element[0] IS NOT NULL"; 
$result = $DB->query($query);
$query1 = $DB->fetch_object($result);

$query = "SELECT en as country, $element[1] as value 
                FROM administrative
                WHERE year=2021
                AND $element[1] IS NOT NULL";
$result = $DB->query($query);
$query2 = $DB->fetch_object($result);


$query = "SELECT en as country, $element[2] as value 
                FROM administrative 
                WHERE year=2021
                AND $element[2] IS NOT NULL";
$result = $DB->query($query);
$query3 = $DB->fetch_object($result);

$query = "SELECT en as country, $element[3] as value 
                FROM administrative 
                WHERE year=2021
                AND $element[3] IS NOT NULL";
$result = $DB->query($query);
$query4 = $DB->fetch_object($result);

Is there another way how this be done instead of repeating the same query only to replace the element?

CodePudding user response：

You can try next query:

SELECT 
    en as country, 
    COALESCE($element[0], $element[1], $element[2], $element[3]) as value 
FROM administrative
WHERE year=2021 AND (
    $element[0] IS NOT NULL OR
    $element[1] IS NOT NULL OR
    $element[2] IS NOT NULL OR
    $element[3] IS NOT NULL
);

PHP code for generate query from array