mysql database search by columns

Question

I'm new to mysql and php. I'm working on songs database. I have a search script which searches all columns of my database and it shows results in a table. But I want to make that search script to search by selected column field. Below the search box I want to add bullet options (ex: search by column 1, column 2, column 3, column 4) You can see a pic below.

Songs database table.

+----+--------+----------+-----+-------+
| id | title  | artist   | key | genre |
+----+--------+----------+-----+-------+
|  1 | xxxxx  | xxx xxxxx| xxx | xxxxx |    
|  2 | xxxxx  | xxx xxxxx| xxx | xxxxx |       
|  3 | xxxxx  | xxx xxxxx| xxx | xxxxx |       
|  4 | xxxxx  | xxx xxxxx| xxx | xxxxx |        
|  5 | xxxxx  | xxx xxxxx| xxx | xxxxx |        
+----+--------+----------+-----+-------+

Search script :

 <?php 


    $host = "localhost";
    $user = "xxxxxxx";
    $password = "xxxxxxx";
    $database_name = "xxxxxx";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

    ));

$pdo->exec("set names utf8");
$search=$_POST['search'];
$query = $pdo->prepare("select * from table where title LIKE '%$search%' OR key LIKE '%$search%' OR genre LIKE '%$search%' OR artist LIKE '%$search%' LIMIT 0 , 100");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();

         if (!$query->rowCount() == 0) {

          $row_cnt = $result->num_rows;

    printf("<p class='notice'>Your search for <span class='blue'>“</span><span class='blue'>" . $result['search'] . "</span><span class='blue'>”</span> yielded %d results.\n</p>", $row_cnt);
                 echo "<table class='table'>";

                echo "<tr class='tablehead'>";

                    echo "<th>Title</th>";
                    echo "<th>Artist</th>";
                    echo "<th>Key</th>";
                    echo "<th>Genre</th>";
                    echo "</tr>";           
            while ($results = $query->fetch()) {
             echo "<tr>";

            echo "<td> <a href=publicsong.php?id=".$results['id'] . ">"  "</a> " . $results['title'] . " </td>";

                    echo "<td>" . $results['artist'] . "</td>";
                    echo "<td>" . $results['key'] . "</td>";
                    echo "<td>" . $results['genre'] . "</td>";

                echo "</tr>";

            }
                echo "</table>";        
        } else {
            echo 'Nothing found';
        }
?>

Im newbie. Please help.

UPDATE

$search=$_POST['search'];
$field=$_POST['radio_grp'];
switch($field)
{
case 'Title' : $field='title';
break;
case 'Artist' : $field='artist';
break;
case 'Category' : $field='category';
break;
}

$query = $pdo->prepare("select * from lyrics_a where $field LIKE '%$search%' LIMIT 0 , 100");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();


     echo"<form action='search.php' method='post'>
Search: <input type='text' id='search' name='search'  placeholder='Search' required data-validation-required-message='Please enter atleast 3 characters'/>
<input type='submit' class='searchButton greenButton' value='Go' />
<input type='radio' name='search' value='title'/>Title
<input type='radio' name='search' value='artist'/>Artist
<input type='radio' name='search' value='category'/>Category

</form><br>";



    // Display search result
         if (!$query->rowCount() == 0) {

          $row_cnt = $result->num_rows;


                 echo "<table class='table'>";

                echo "<tr class='tablehead'>";



                    echo "<th>Title</th>";

                    echo "<th>Artist</th>";

                    echo "<th>Category</th>";

                echo "</tr>";           
            while ($results = $query->fetch()) {
             echo "<tr>";

            echo "<td> <a href=publicsong.php?id=".$results['id'] . ">" . $results['tel_title'] . "</a> " . $results['title'] . " </td>";

                    echo "<td>" . $results['artist'] . "</td>";

                    echo "<td>" . $results['category'] . "</td>";

                echo "</tr>";

            }
                echo "</table>";        
        } else {
            echo 'Nothing found';
        }
?>

Answer 1

Try this code

Set the value of radio button same as column name

<input type="radio" name="search_by" value="title">title<br>
<input type="radio" name="search_by" value="artist">artist<br>
<input type="radio" name="search_by" value="key">key<br>
<input type="radio" name="search_by" value="genre">genre<br>

$search=$_POST['search'];
$search_by=$_POST['search_by'];

$query = $pdo->prepare("select * from table where $search_by LIKE '%$search%' LIMIT 0 , 100");

Answer 2

You should get values of your radio buttons and make appropriate query

$search=$_POST['search'];
$field=$_POST['radio_grp'];
switch($field)
{
case 'Title' : $filed='title' // or your column field
break;
//---similar cases
}
$query = $pdo->prepare("select * from table where $field LIKE '%$search%' LIMIT 0 , 100");