CODEWITHSUNDEEP
phpmysql
Sam H
Sam H

Reputation: 7

PHP - Ignore NULL in SQL Select Query

I'm trying to pull a list of my actors via the foreign key stored in my main 'movies' table. It will work if all actor fields are populated, but will die if any one of those fields are 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");

Whats the best way to handle this?

Upvotes: 0

Views: 188

Answers (2)

Uri Goren
Uri Goren

Reputation: 13700

Use the MySQL COALESCE() function

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

Upvotes: 0

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

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

Now try,

SELECT actor_name
FROM actors
WHERE actorID IN ( actorIds )

Upvotes: 1

Related Questions