PHP - Ignore NULL in SQL Select Query

Question

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?

instead of all those ORs use the IN command. like this actorID IN ($row[5],...) — Sam
– Sam, Commented Dec 10, 2013 at 21:37

Geordee Naliyath · Accepted Answer · 2013-12-10 21:41:32Z

1

$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 )

answered Dec 10, 2013 at 21:41

Geordee Naliyath

1,85921 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sam H Over a year ago

Thanks for your response. The array slice is selecting the wrong values from my array. Should it not be ($row, 5, 6) i.e starting at the 5th element and return 6 values? Even when I run this, it returns the wrong element, but if I print out $row[5] it is the correct element.

Sam H Over a year ago

So i've got the slice working now, needs to be ($row, 10, 12). My code is as follows:

$actors = array_slice($row, 10, 12);										$actors_without_nulls = array_filter($actors, 'strlen');								$actorIds = implode(', ', $actors_without_nulls);								$stars = mysql_query("SELECT actor_name FROM actors WHERE actorID IN ( $actorIds )")										or die("<h1>Error - (stars) the query could not be executed</h1>\n");

This will still not work for NULL Values.

Geordee Naliyath Over a year ago

The array indexes were wrong... I tried it locally using a test array, and copied that on to the response. So many variables are declared to make it easier to understand. You can chain them together.

Uri Goren · Accepted Answer · 2013-12-10 21:50:02Z

0

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)
")

answered Dec 10, 2013 at 21:50

Uri Goren

13.8k8 gold badges62 silver badges113 bronze badges

Collectives™ on Stack Overflow

PHP - Ignore NULL in SQL Select Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related