Cannot return exact match using full text search?

Question

I have 2 tables is song and lyric . Now I used full text search for people to search songs or lyrics they want .but the exact match doesn't appear in the 1st result .

Example : I want to search the song or lyric name "Love and Let Love" . But it return like this :

        [0] => Array
            (
                [song_title] => This Is Love
                [type] => songs
                [point] => 3.9282567501068115
            )

        [1] => Array
            (
                [song_title] => I Will Love Again 2014
                [type] => songs
                [point] => 3.8840975761413574
            )

        [2] => Array
            (
                [song_title] => A Love I Think Will Last
                [type] => lyric
                [point] => 3.811438798904419
            )

        [3] => Array
            (
                [song_title] => Love and Let Love
                [type] => lyric
                [point] => 3.811438798904419
            )

Here is my code :

public function searchSongs2($keyword, $start, $limit,$deleted=0)
{
    $sql = " (SELECT s.song_title, 'songs' as type ,MATCH(s.song_title) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) as point
              FROM song s
              JOIN category_song cs ON cs.song_id = s.song_id
              JOIN category c ON c.category_id = cs.category_id
              WHERE s.song_type ='publish' AND MATCH(s.song_title) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) 
              GROUP BY s.song_title  )  
       UNION
              (SELECT l.lyric_name, 'lyric' as type  ,MATCH(l.lyric_name) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE) as point
              FROM ml_lyric l
              JOIN ml_singer s ON s.singer_id = l.singer_id
              JOIN ych_category_lyric cl ON cl.lyric_id = l.lyric_id
              JOIN ych_category c ON c.category_id = cl.category_id
              WHERE MATCH(l.lyric_name) AGAINST ('".$keyword."' IN NATURAL LANGUAGE MODE)
          GROUP BY l.lyric_name  ) 
  ORDER BY point DESC  LIMIT ".$start.",".$limit;
    return Yii::app()->db->createCommand($sql)->queryAll();
}

Is there anyway to let the string people search always in 1st place ?

Answer 1

Edited so it actually works:

May not be the most elegant, but just tested and it works. I'm sure someone else could pretty it up a bit.

$myarray = array(
         array
            (
                'song_title' => 'This Is Love',
                'type' => 'songs',
                'point' => '3.9282567501068115'
            ),

        array
            (
                'song_title' => 'I Will Love Again 2014',
                'type' => 'songs',
                'point' => '3.8840975761413574'
            ),

        array
            (
                'song_title' => 'A Love I Think Will Last',
                'type' => 'lyric',
                'point' => '3.811438798904419'
            ),

        array
            (
                'song_title' => 'Love and Let Love',
                'type' => 'lyric',
                'point' => '3.811438798904419'
            ),
     );
$new_array = array();
foreach ( $myarray as $key => $value ) {
   if ( $value['song_title'] == 'Love and Let Love' ) {
        $new_array[0]['song_title'] = $value['song_title'];
        $new_array[0]['type'] = $value['type'];
        $new_array[0]['point'] = $value['point'];
                unset($myarray[$key]);
    }
}
     $new_array = array_merge($new_array, $myarray);
    print_r ( $new_array );

Returns:

Array
(
    [0] => Array
        (
            [song_title] => Love and Let Love
            [type] => lyric
            [point] => 3.811438798904419
        )

    [1] => Array
        (
            [song_title] => This Is Love
            [type] => songs
            [point] => 3.9282567501068115
        )

    [2] => Array
        (
            [song_title] => I Will Love Again 2014
            [type] => songs
            [point] => 3.8840975761413574
        )

    [3] => Array
        (
            [song_title] => A Love I Think Will Last
            [type] => lyric
            [point] => 3.811438798904419
        )

)