1

I have a websites that stores a lot of pdf articles. The fields for these articles e.g title, author etc are stored in a MySQL database. I would like to construct a search that returns the nearest matches for the field 'title' and have the search return a result.

For example I may have articles with the following titles:

  1. 'Deregulated proliferation and differentiation in brain tumors.'
  2. 'Tumor-induced myeloid dysfunction and its implications for cancer immunotherapy.'
  3. 'Epigenetic alterations in inflammatory bowel disease and cancer.'

So if i entered into the search: 'cancer and epigenetics', articles with titles similar to (3), would show up in the result.

At the moment, when I generate an sql search query, only exact matches will return a result. Please could someone advise me on how to move forward with this problem? Some broad strokes and pointers would be very much welcome.

My php code:

 // HANDLES SEARCH INPUT
if(isset($_POST['searchstring'])){

$searchterm = $_POST['searchstring'];

if($mysqli->connect_errno){
/*echo 'error connecting to the database';*/
    echo 'error db connection';
    exit();
}

$searchterm = $mysqli -> real_escape_string($searchterm);

$sql = "SELECT id FROM pdf_library WHERE title='$searchterm'";

$result = $mysqli -> query($sql);
if($result){
    $num_rows = $result -> num_rows;
    if($num_rows <1){
        echo 'no_result';
    }else{
        echo 'you_have_results';
    }

}else{
    echo 'searh failed';
    exit();
}

}

My javascript code:

 var search_btn = document.getElementById('searchbtn');
 search_btn.addEventListener('click', function(){
var string = document.getElementById('search_input').value;
console.log('search string: ' + string);

//AJAX REQUEST
var formdata = new FormData();
formdata.append('searchstring', string);

// xmlhttpRequest object
var xmlhttp = new XMLHttpRequest;

xmlhttp.onreadystatechange = function(){
    if(xmlhttp.readyState == 4 && xmlhttp.status == 200){
        if(xmlhttp.responseText == 'no_result'){
            console.log('no results found');
        }else if(xmlhttp.responseText == 'you_have_results'){
            console.log('results found');
        }else{
            console.log(xmlhttp.responseText);
        }
    }
}
xmlhttp.open('POST', 'search_aux.php');
xmlhttp.send(formdata);

});

dave
  • 475
  • 6
  • 17

3 Answers3

0

Maybe something along the lines of the Levenshtein Distance would help.

http://www.artfulsoftware.com/infotree/queries.php#552

MySQL - Finding Close Matches

Community
  • 1
  • 1
LeDoc
  • 935
  • 2
  • 12
  • 24
0

SQL wildcards may be of use to you:

% A substitute for zero or more characters

_ A substitute for a single character

[charlist] Sets and ranges of characters to match

[^charlist] or [!charlist] Matches only a character NOT specified within the brackets

And, like @Djip said in the comments, you need to use LIKE with wildcards:

SELECT id FROM pdf_library WHERE title LIKE '%$searchterm%'
Community
  • 1
  • 1
Dmitry Grigoryev
  • 3,156
  • 1
  • 25
  • 53
0

I think that you actually have to treat the things you're looking for as tags. That is, in "cancer and epigenetics" you have 2 tags (cancer, epigenetics) and a separator (and). I would build a list (array) of tags, and then search for them in each title i.e. see if strings "cancer" & "epigenetics" are contained in title.

Of course, you will get more hits in some titles than in others, which translates into a score, so you can sort the output based on that.

Actually, you should only permit the users to input tags separated by a fixed separator (a comma, for example) and then do the above.

Mikey
  • 117
  • 2
  • 12