I've what (with my limited MYSQL understanding) is really a relatively complex query:

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    song_description, uploaded_time, emotion, tempo,
    user, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count, waveform, datasize, display_name, user_url,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted 
FROM (
        SELECT
            sp.songsID, projects_count,
            AVG(rating) as rating,
            COUNT(rating) AS ratings_count,
            COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count

                FROM (
                    SELECT songsID, COUNT(*) as projects_count
                    FROM $sTable s
                    LEFT JOIN $sTable2 p ON s.songsID = p.songs_id

                    GROUP BY songsID) as sp

            LEFT JOIN $sTable3 r ON sp.songsID = r.songid

            GROUP BY sp.songsID) as spr


JOIN $sTable s USING (songsID)
LEFT JOIN $sTable5 q ON s.user = q.ID   


        $sWhere
        $sOrder
        $sLimit

This question will get all the details in regards to a given song from 5 different tables.

the variable $sWhere develops the WHERE area of the query and is dependent around the data delivered to the server to ensure that this listing of tunes to become dynamically filterable in line with the user input.

$sOrder and $sLimit order and limit the outcomes again according to user input.

Now i have to give a sixth table directly into this equation which consists of a listing of genre's (wordpress_genres), this table is from the songsID column in $sTable using a link table (wordpress_song_genres) using the following structure:-

$sTable(wp_songs)    $sTable6(wp_genre_songs)     $sTable7(wp_genres)
songsID*             song_id*                     genre_id**
column2              genre_id**                   genre_name
column3                                           icon_url
column4
column5
etc...

The asterisks denote the hyperlinks.

What I have to do would be to allow my dynamic filter from the song leads to have the ability to filter their email list of tunes by Genre however i can't see how introducing the brand new table(s) ($sTable6 and $sTable7) in to the query.

Below may be the code producing the WHERE a part of my query:

This part takes the written text recieved via 'sSearch' and searches the posts for just about any matching text.

    $sWhere = "";
if ( $_GET['sSearch'] != "" )
{
    $aWords = preg_split('/\s+/', $_GET['sSearch']);
    $sWhere = "WHERE (";

    for ( $j=0 ; $j<count($aWords) ; $j++ )
    {
        if ( $aWords[$j] != "" )
        {
            $sWhere .= "(";
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $aWords[$j] )."%' OR ";
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ") AND ";
        }
    }
    $sWhere = substr_replace( $sWhere, "", -4 );
    $sWhere .= ')';
}

This part is really a 'range' filter to ensure that the consumer can input the absolute minimum tempo value along with a maximum tempo value (done using a jquery range slider around the font finish). The information is recieved via sSearch_*.

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
    if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
    {
        if ( $sWhere == "" )
        {
            $sWhere = "WHERE ";
        }
        else
        {
            $sWhere .= " AND ";
        }







        $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
        // check for values range
        $rangeSeparator = "~";
        if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
            // get min and max

             $columnFilterRangeMatches =  explode('~', $columnFilterValue);

            // get filter
            if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                $sWhere .= " 0 = 0 ";
            else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
            else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
            else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
        } else {
            $sWhere .= $aColumns[$i] . " LIKE '%" . $columnFilterValue . "%' ";
        }
    }
}

The information for that genre_id's is going to be arriving via sSearch_23. i.e. $_GET['sSearch'] will retain the genre_id If only to filter the outcomes by. Please be aware that I have to have the ability to add the outcomes of numerous genre tags together.

If your user selects genre:rock (genre_id: 1) then all tunes in $sTable which have a genre_id:1 connected together ought to be came back. (together with the relaxation from the details about the tunes as retrievedby my current query.

But... When the user chooses genre:rock (genre_id:1) AND genre:pop (genre_id:2) AND genenre:rap (genre_id:3) then all tunes with Any one of individuals tags ought to be came back.

Sorry this is really an intricate question and that i understand I might not have articulated rid of it. Also I realize this is quite specific to my current code. Regardless I am hoping someone might help me or at best pint me within the right direction.

Thank you!

EDIT:

I've just anaged to obtain his to 'nearly' work with the addition of the folling into my query:

LEFT JOIN (
            SELECT igs.song_id, igs.genre_id, g.genre_name
            FROM $sTable6 AS igs
            JOIN wp_genres AS g 
            ON igs.genre_id = g.genre_id
            ) as gs 
            ON songsID = gs.song_id         

To ensure that the entire factor now appears like:-

    $sQuery = "
        SELECT SQL_CALC_FOUND_ROWS
    songsID, song_name, artist_band_name, author, song_artwork, song_file,
    genre, song_description, uploaded_time, emotion, tempo,
    user, happiness, instruments, similar_artists, play_count,
    projects_count,
    rating, ratings_count, waveform, datasize, display_name, user_url, genre_id,
    IF(user_ratings_count, 'User Voted', 'Not Voted') as voted 
FROM (
        SELECT  
            sp.songsID, projects_count, 
            AVG(rating) as rating,
            COUNT(rating) AS ratings_count,
            COUNT(IF(userid=$userid, 1, NULL)) as user_ratings_count

                FROM (
                    SELECT songsID, COUNT(*) as projects_count
                    FROM $sTable s
                    LEFT JOIN $sTable2 p ON s.songsID = p.songs_id

                    GROUP BY songsID) as sp

            LEFT JOIN $sTable3 r ON sp.songsID = r.songid





            GROUP BY sp.songsID) as spr

LEFT JOIN (
            SELECT igs.song_id, igs.genre_id, g.genre_name
            FROM $sTable6 AS igs
            JOIN wp_genres AS g 
            ON igs.genre_id = g.genre_id
            ) as gs 
            ON songsID = gs.song_id         



JOIN $sTable s USING (songsID)
LEFT JOIN $sTable5 q ON s.user = q.ID   





        $sWhere
        $sOrder
        $sLimit

The queries by genre_id now happen perfectly and dynamically as intended. However I get dupilcate rows when no genre_id is specified by the WHERE area of the query. Each song which has an entry within the tunes/genres link table will get copied.

To begin with is my current solution a high quality one when it comes to optimisation/guidelines? Next, how do i prevent these duplicate rows?

NOTE: I actually do curently have a distinctive primary key across both posts within the link table.