将两个查询合并为单个查询

Question

I have this function which gets information from a database, namely the amount of songs in the songs table and the amount of artists from the artists table who have songs in the song table:

function getInfo() {
    try {
        $q = $this->connection->prepare('SELECT artist_id FROM '.TBL_SONG.'');
        $q->execute();
        if ($q->rowCount() > 0) {
            $songs = $q->rowCount();
        } else {
            $songs = '0';
        }
        $q = $this->connection->prepare('SELECT id FROM '.TBL_ARTIST.' a WHERE EXISTS (SELECT * FROM '.TBL_SONG.' s WHERE a.id = s.artist_id)');
        $q->execute();
        if ($q->rowCount() > 0) {
            $artists = $q->rowCount();
        } else {
            $artists = '0';
        }
        return "<span class='italic'>Current songs: </span>".$songs." <span class='italic'>Active artists: </span>".$artists;
    } catch (PDOException $e) {
        echo RESULTS_ERROR;
        logError($e->getMessage());
    }
}

The first query gets the amount of songs from the song table and returns the rowcount to a variable. The second query gets the artist id from the artist table, if they have songs in the songs table. The result of this function is to return both values.

I want to be able to have both these values returned from a single query. I've tried writing it as one query and fetching the results and using the count function to get the amount of the rows I need but this doesn't seem to work. Don't really know where I'm going wrong here. Also, is it pointless checking if the row count is > 0 with an if statement and storing it in a variable as it'll return the value '0' anyway? Thanks.

Answer 1

This is actually pretty easy. You want to join the artist table and the song table using the artist id. From that join, you want to know the number of distinct artist ids and song ids. The query you want will be something like this:

select count(distinct a.id) as artists, count(distinct s.id) as songs
from artists a
inner join songs s on s.artist_id = a.id;

I highly recommend you get your query right from a console of some kind before plugging it into PHP. The output will be a single row that looks something like this:

+---------+-------+
| artists | songs |
+---------+-------+
|      20 |   150 |
+---------+-------+

From PHP, you just need to fetch the one-row answer and use it in your response:

if ($q->rowCount() > 0) {
    $c = $q->fetchObject();
    $output = "<span class='italic'>Current songs: </span>{$c->songs}<span class='italic'>Active artists: </span>{$c->artists}";
}