mysqli_num_rows with left join on 2 tables

I have 2 tables, “artists” and “songs”. I need a number of artist in a category when I open it on the site.

This gives me a number of all songs in the category “house”.

$query = mysqli_query($_connect, 'SELECT `cat` 
    FROM `songs` 
    WHERE `cat` = "house"');
$result = mysqli_num_rows($query);
echo $result; //ok

Now I tested this to show all artist in the category. “(pass) have the same number in both tables for joining with USING”

$query = mysqli_query($_connect, 'SELECT `artist` 
    FROM `artists` 
    LEFT JOIN `songs` USING (pass) 
    WHERE `cat` = "house"');
$result = mysqli_num_rows($query);
echo $result; //fail

The output is the number of songs but I need the number of artists in the category “house”. How I can get the number of artists in a selected category?

Answer

Assumed DB structure

I’m going to assume that pass is a unique identifier that would equate to artist.id

artists             songs
    id                  id
    artist              song
    pass                pass
                        cat

SQL

There are a couple of ways to do this, depending on what you want. However, the bones of it is that you need to use GROUP BY to limit your results to one artist

Modify your original query

This will give you a result set of the unique values in the artist column. Which you can then use mysqli_* functions on.

SELECT artist FROM artists
LEFT JOIN songs USING (pass)
WHERE cat = "HOUSE"
GROUP BY artist;

Use SQL to return the COUNT

This will return the count as the result set.

SELECT COUNT(*) as numberOfArtists
FROM (
    SELECT COUNT(*) FROM songs
    WHERE cat="HOUSE"
    GROUP BY pass
    ) as query

Update

Coming back to look at this again and with the additional detail in the comments You could optimise the queries:

Number of artists

Returns a result set with one column: the number of artists that have songs in the HOUSE category.

SELECT COUNT(DISTINCT pass) as count FROM songs WHERE cat="HOUSE";

Number of songs and artists

Returns a result set with two columns: the number of artists that have songs in the HOUSE category as artists and the number of songs in the HOUSE category as songs.

SELECT 
    COUNT(DISTINCT pass) as artists,
    COUNT(*) as songs
FROM songs
WHERE cat="HOUSE";