too long

Question

This question already has an answer here:

• mysql keyword search 2 answers
• MySQL Fulltext Stopwords Rationale 1 answer

Ok so I'm pretty new to PHP and building a question/answer site. I want a page where the user can search my questions table to find a question and after some research and work I've come up with this but my problem is common words. If a user types "is" in the search every question with "is" in it turns up. My question is either 1) Is my approach to this search function completely wrong? or 2) is there a way I can inject an array of common words to be omitted from the query?

search_reslut.php:

<?php


$servername = "127.0.0.1";
$username = "dylan326";
$password = "";
$dbname = "questions87";
$port = 3306;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname, $port);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

echo "Your search results: <br>";
echo "<br />";
$query = $_POST['query'];

$query  = "$query $query $query";
$pieces = explode(" ", $query);
$qindex0 = $pieces[0]; // piece1
$qindex1 = $pieces[1]; // piece2
$qindex2 = $pieces[2]; // piece3
$qindex3 = $pieces[3];
$qindex4 = $pieces[4];
$qindex5 = $pieces[5];
$qindex6 = $pieces[6];



echo $query;

$sql = "select q_id, question, username, q_date from questions where (question like '%$qindex0%' or question like '%$qindex1%' or question like '%$qindex2%' or question like '%$qindex3%'
or question like '%$qindex4%' or question like '%$qindex5%' or question like '%$qindex6%')";



$result = $conn->query($sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_array($result)){

        $question = $row['question'];

echo ('<a href="totalqs.php?q_id=' . $row['q_id'] .'" >' . $question .'Asked by:'.' '.$row['username'].' '.$row['q_date'] .' </a>'  . '<br>');


}}

else {echo "No resluts found";}




?>

</div>

Answer 1

MySQL has the ability to make keyword searches easy and much faster than what you're doing. This is done through the MATCH(column) AGAINST ('words to search') syntax. Add a FULLTEXT index to your table, for the column you want to make searchable (question). Then something like this would work to return all questions that have at least one of the search words

// Get the query. escape all single quotes.
$words = str_replace("'","\'",$_POST['query']);

$sql = <<< "SQL"
select q_id, question, username, q_date from questions 
where MATCH(`question`) AGAINST('$words' IN BOOLEAN MODE)
SQL;

$result = $conn->query($sql);

The nice thing about FULLTEXT searches is that they automatically exclude common words (stop words) from the searches for you. Learn more here as well as here

If you have a custom list of stop words, you can just remove them from the $words string before you execute the query

$stopWords = [
  '/is/', 
  '/the/'
];

// Where is the cat   >> Where   cat
$words = preg_replace($stopWords,'',$words);

Note from the docs:

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.