Outputting results from a conditional row count across multiple tables
MySQL

Your query looks fine, I'm not sure it could be further optimized of itself. What I would suggest though is to plug it into a stored procedure, and there are a few good reasons to do this. 


Cleaner PHP script by only having your behavioral code in your script while just calling the procedure from there.
Easier to modify, e.g., if you found yourself using this SQL script in multiple PHP scripts and you had to make a change to it, you would have to change it everywhere, as opposed to just changing the stored procedure once. 
Better SQL optimization. The way you have it now, every time you run the PHP script it sends an ad-hoc request to the SQL engine and it has to figure out the optimization each time. By storing the SQL script in the RDBMS the engine will optimize it ahead of time, so it will respond quicker each time.


Create procedure 

You can run this just once:

DELIMITER |    
CREATE PROCEDURE wcx_objects() AS    
BEGIN    

SELECT SUM(inactive_count) AS inactive_count
FROM (    
    SELECT COUNT(article_active) AS inactive_count    
        FROM wcx_articles WHERE article_active = 0    
    UNION ALL    
    SELECT COUNT(video_active)    
        FROM wcx_videos WHERE video_active = 0    
    UNION ALL    
    SELECT COUNT(app_active)    
        FROM wcx_apps WHERE app_active = 0    
    UNION ALL    
    SELECT COUNT(link_active)    
        FROM wcx_links WHERE link_active = 0    
) AS wcx_objects;    

END|    
DELIMITER ;


Call Procedure in PHP

Then from PHP all you need to do to get your result set is write this:

$query = "CALL wcx_objects();"