目前，我有一個包含元素的陣列和四個類似的查詢，只是使用陣列中的不同元素。大致情況是這樣的（簡化）
$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);
有沒有另一種方法可以做到這一點，而不是重復相同的查詢來替換元素？
uj5u.com熱心網友回復：
您可以嘗試下一個查詢：
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 代碼
uj5u.com熱心網友回復：
這個答案是基于問題中的給予
function result($myArray = [], $DB= '' , $year='' ){
foreach ($myArray as $tors_array ) {
$element[] = $tors_array;
}
$query = "SELECT en as country, :ELEMENT AS VALUE
FROM administrative
WHERE year= :YEAR
AND :ELEMENT IS NOT NULL";
$stmt = $DB->prepare($query);
$stmt->bindParam(':YEAR',$year);
$stmt->bindParam(':ELEMENT',$element, PDO::PARAM_INT);
if ($stmt->execute()){
$row = $stmt->fetch(PDO::FETCH_ASSOC);
}
return $row;
}
try{
//$PDO = connect to database
$myArray = array('element1', 'element2', 'element3', 'element4');
$year = '2021';
$result = result($myArray,$PDO,$YEAR);
echo json_encode($result);
}
catch (PDOException $e) {
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/439808.html