Conditional row count across 4 tables I have the following function that works fine.  Is there a better way to do it and will this be efficient if I have 100+ rows returned from each table?

The database

Tables


wcx_articles
wcx_videos
wcx_apps
wcx_links


The corresponding _active column for each table


article_active
video_active
app_active
link_active


_active is a switch 1 or 0

This is my current function

public function findInactiveItems() {

    $query = "SELECT * FROM wcx_articles WHERE article_active = '0'
    UNION
    SELECT * FROM wcx_videos WHERE video_active = '0'
    UNION
    SELECT * FROM wcx_apps WHERE app_active = '0'
    UNION
    SELECT * FROM wcx_links WHERE link_active = '0'
    ";
        if( $this-&gt;num_rows( $query ) &gt; 0 ) {    
            $count = $this-&gt;num_rows($query);
            return $count;
        }
    }

 public function num_rows( $query ) {
    $query = $this-&gt;link-&gt;query( $query );
    if( mysqli_error( $this-&gt;link ) ) {
        $this-&gt;log_db_errors( mysqli_error( $this-&gt;link ), $query, 'Fatal' );
        return mysqli_error( $this-&gt;link );
    }
    else {
        return mysqli_num_rows( $query );
    }
    mysqli_free_result( $query );
}
SQL

If you want a count, ask for a count.  Don't ask the server to send all the data for all the rows, just to have the client count the rows.  (A few hundred rows is a trivial amount of data for a database to handle, but still, you should follow good practices.)

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 wcs_objects;


Schema

If in fact all four of your tables are union-compatible with each other, then you should strongly consider merging them into one table with a wcx_type attribute that declares what type of object that row represents.

Are your …_active columns really strings?  Those columns should be of the BOOLEAN (i.e., TINYINT(1)) type.  The query should be adjusted accordingly: use …_active = 0 instead of …_active = '0'.

PHP

You reused the $query parameter to store the result of the query.  Don't be stingy like that — it's confusing.  Use a new variable to store the result.

You have a leak: mysqli_free_result( $query ); never gets called, because that statement is unreachable.

You are mixing the object-oriented and procedural styles of mysqli.  You should either use the object-oriented style consistently…

public function num_rows($query) {
    if (!($result = $this-&gt;link-&gt;query($query))) {
        $this-&gt;log_db_errors($this-&gt;link-&gt;error, $result, 'Fatal');
        return $this-&gt;link-&gt;error;
    }
    $num_rows = $result-&gt;num_rows;
    $result-&gt;free();
    return $num_rows;
}


or use the procedural style consistently…

public function num_rows($query) {
    if (!($result = mysqli_query($this-&gt;link, $query))) {
        $this-&gt;log_db_errors(mysqli_error($this-&gt;link), $result, 'Fatal');
        return mysqli_error($this-&gt;link);
    }
    $num_rows = mysqli_num_rows($result);
    mysqli_free_result($result);
    return $num_rows;
}