[SOLVED] sql query to search from 2 fields mysql Fulltext

Issue

I have a search that searches for songs in a database, currently they can search by artist or the title of the song. So at the moment they could search tom jones or its not unusual they couldn’t search tom jones unusual. How do I make this possible my current SQL looks like this

SELECT * 
FROM songs  
WHERE artist LIKE '%$search%' 
   OR songname LIKE '%$search%' 
LIMIT 6

I have added full text search to the artist and songname columns. Can’t work out how to search on both rows.

Solution

The solution to this is using full text search. You first need to define the rows you want to use in full text search you can do this with the following mysql command

ALTER TABLE  songs 
ADD FULLTEXT(artist, songtitle)

and then the sql query is

$sql = "SELECT * FROM songs WHERE MATCH (songname, artist) AGAINST ('$search')  LIMIT 6";

This will return results only when there is a matching word if you want like results before a full word is typed you can so something like this.

$sql = "SELECT * FROM songs WHERE MATCH (songname, artist) AGAINST ('$search' IN BOOLEAN MODE)  LIMIT 6";
$sqllike = "SELECT * FROM songs  WHERE artist like '%$search%' OR songname like '%$search%' LIMIT 6";
$result = mysqli_query($con, $sql);
$resultlike = mysqli_query($con, $sqllike);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "<a href='". $row["songid"] . "' class='songclicker'>";
        echo "<div class='song whitefont'>";
        echo "<img src='" . $row["artwork"]."' class='songimage'>";
        echo "<p>". $row["songname"] . "</p>";
        echo "<p>". $row["artist"] . "</p></div>";

    }
    echo "<a href='". urlencode($search) ."'><h1 class='seeall'>See all search results</h1></a>";
} else {
    if (mysqli_num_rows($resultlike) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($resultlike)) {
        echo "<a href='". $row["songid"] . "' class='songclicker'>";
        echo "<div class='song whitefont'>";
        echo "<img src='" . $row["artwork"]."' class='songimage'>";
        echo "<p>". $row["songname"] . "</p>";
        echo "<p>". $row["artist"] . "</p></div>";

    }

    echo "<a href='searchresults.php'><h1 class='seeall'>See all search results</h1></a>";
} else {
    echo "0 results";
}
}

Answered By – Tom Bruton

Answer Checked By – Senaida (BugsFixing Volunteer)