PHP-在SQL选择查询中忽略NULL - PHP - Ignore NULL in SQL Select Query

PHP - Ignore NULL in SQL Select Query

本文关键字：NULL 查询 SQL 选择 PHP- | 更新日期: 2024-03-19

我正试图通过存储在主'movies'表中的外键来提取演员列表。如果填充了所有actor字段，它就会工作，但如果其中任何一个字段为NULL，它就会失效。

$stars = mysql_query("
    SELECT actor_name
    FROM actors
    WHERE actorID = $row[5] OR actorID = $row[6] OR actorID = $row[7] OR actorID = $row[8]  OR actorID = $row[9] OR actorID = $row[10]
")
or die("<h1>Error - (stars) the query could not be executed</h1>'n");
$count = 0;
while ($row_stars = mysql_fetch_array($stars)) {
    print("$row_stars[0]<br/>'n");
    if ($count == 2) {
        print("</td>'n");
        print("<td>'n");
    }
    $count++;
}
print("</td>'n </tr>'n");

处理这个问题的最佳方法是什么？

$actors = array_slice($row, 2, 6);
$actors_without_nulls = array_filter($actors, 'strlen');
$actorIds = implode(', ', $actors_without_nulls);

现在试试，

SELECT actor_name
FROM actors
WHERE actorID IN ( actorIds )

使用MySQL COALESCE（）函数

$stars = mysql_query("
    SELECT actor_name
    FROM actors
    WHERE actorID = COALESCE($row[5],$row[6],$row[7],$row[8],$row[9],$row[10],-1)
")