count some limited words and display them correspond to their names

Question

I am using a search box that searches the user input from "title". Besides this i want to count some keywords from another column "subject" only in the presense of user input corresponds in "title" column. but the problem is that how to find count of only words i have mentioned in the query i.e.

Title        Subject

a       word1,word2,word1,
ab     word2,word4,word3,
bb     word1,word4,word4,
aa     word2,word2,word4,
cb     word1,word1,word3,
ac     word2,word1,word3,

So, here if i have searched for a in textbox then, how can i get count of word1 from subject where only a is present in the title field and then similar for the other words so that i could get output like:

your search a contains :
word1 (3)
word2 (5)
word3 (2)
word4 (2)

The code for query is given below:

$getq = "SELECT Title,Subject COUNT(*) FROM tablename WHERE Title LIKE '%$search_each%' Subject LIKE '%word1%' OR Subject LIKE '%word2%' OR Subject LIKE '%word3%' OR Subject LIKE '%word4%') GROUP BY Subject";
$getquery = $conn->query($getq);
while( $runrows = mysqli_fetch_assoc($getquery))
{
$sub = $runrows ['Subject'];
$countsub = $runrows ['COUNT(*)'];  
}
echo "<a href='#'>"word1"(".$countsub.")</a> ";
echo "<a href='#'>"word2"(".$countsub.")</a> ";
echo "<a href='#'>"word3"(".$countsub.")</a> ";
echo "<a href='#'>"word4"(".$countsub.")</a> ";

Here, $search_each is textbox input and the above code shows nothing in place of $countsub because i don't know how to get individual count for all words under presense of user's search term. How this can be done?.

Your help will be highly appreciated.

Answer 1

Try this:

$z = array("word1", "word2", "word3", "word4", "word5");

$countsub1 = array();
for ($i = 0; $i <= 4; $i++) {
    $getq3 = "SELECT COUNT(*) FROM table WHERE (Subject LIKE '%$z[$i]%' AND Title LIKE '%$search_each%') ";
    $getquery4 = $conn->query($getq3);

    while ($runrows = mysqli_fetch_assoc($getquery4)) {
        $sub = $runrows['Subject'];
        $countsub = $runrows['COUNT(*)'];
    }
    $countsub1[$i] = $countsub;

    echo "<a href='#' >$z[$i](" . $countsub1[$i] . ")</a><br> ";
}

Don't write links outside the loop, this can solve your problem.