PHP search engine for text files with indexing

sariDon 0
Viewed 47

I have some text files inside a directory (and its sub directories). The number of text files will be (50000+) and the directory is outside 'public_html':

text_root_dir
|-- |-- `001
           |-- text0003.txt
           |-- text0004.txt
           |-- text0005.txt
           |-- `001_a
                   |-- text0006.txt
                   |-- text0007.txt
                   |-- text0008.txt
    |-- text0001.txt
    |-- text0002.txt

The text file details are saved in a MySQL table (with the 'art_textfile' storing the text file name and 'art_path' column storing the file path):

CREATE TABLE `stxt_articles` (
  `art_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `art_title` VARCHAR(127) NOT NULL,
  `art_author`  VARCHAR(255) NOT NULL,
  `art_textfile`  VARCHAR(255) NOT NULL, /* TEXT FILE NAME */
  `art_path` VARCHAR(255) NOT NULL, /* TEXT FILE PATH */
    PRIMARY KEY(`art_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am using PHP/MySQL (LAMP) and want to do a string search on the text files (with regular expressions if possible). The methods that will work logically are:

  1. Storing the contents in the MySQL database and perform a search with MySQL query (LIKE 's%')
  2. Scan the directory by PHP and search within each text file for a search expression.

But with a large dataset of 5000 +files (tend to grow over time), the above options are not practical. It will be too slow to use.

What I am looking for is a PHP/MySQL search idea which creates index for text files and do a search. Pretty much what Lucene does in JAVA. Maybe I can refer it as a lucene alternative in PHP with MySQL.

Thanks for reading this far. Also thanks for your thoughts.

search-engine search mysql php
2 Answers

Using something like AJAX seems to be pretty fast, I'm sorry if I misunderstood your post. (Code will need tweaks for doing exactly what you want but should be a good starting point)

index.html

<html>
<head>
<script>
function showResult(str) {
  if (str.length==0) {
    document.getElementById("search").innerHTML="";
    document.getElementById("search").style.border="0px";
    return;
  }
  var xmlhttp=new XMLHttpRequest();
  xmlhttp.onreadystatechange=function() {
    if (this.readyState==4 && this.status==200) {
      document.getElementById("search").innerHTML=this.responseText;
      document.getElementById("search").style.border="1px solid #A5ACB2";
    }
  }
  xmlhttp.open("GET","search.php?q="+str,true);
  xmlhttp.send();
}
</script>
</head>
<body>

<form>
<input type="text" size="30" onkeyup="showResult(this.value)">
<div id="search"></div>
</form>

</body>
</html>

search.php

<?php
//get the q parameter from URL
$files = scandir("FOLDER")
$q=$_GET["q"];

//lookup all links from the xml file if length of q>0
if (strlen($q)>0) {
  $hint="";
  $directory = 'Directory';
  $results_array = array();

  if (is_dir($directory)) {
  if ($handle = opendir($directory)) {
    while(($file = readdir($handle)) !== FALSE) {
      $results_array[] = $file;
    }
    closedir($handle);
  }
}


foreach($results_array as $value) {
  if(str_starts_with($value, $q)){
    echo $value;
  }
}
Rodney_RWR0

50000 file opens, alone, would take a long time. That does not include the time to search the text in each.

Load the data into a MySQL table with ENGINE=InnoDB (not the deprecated MyISAM). Then you can do very fast queries that are "word oriented" -- this is meeting FULLTEXT's limitation.

You can also do LIKEs (slow) or REGEXPs (even slower).

What I do in such situations is allow the user to use either LIKE syntax or REGEXP syntax or simple word(s). Add FULLTEXT(txt) (assuming txt contains all the text you need to search). Then my code goes something like:

If it looks like "word(s)" of at least 3 letters, stick a '+' in front of each word and build MATCH(txt) AGAINST ("+John +Doe" IN BOOLEAN MODE). In most situations it will be very fast.

Else, if I see %, then I build a LIKE expression and assume the user knows LIKE syntax.

Else, if I assume it is a regexp and go down that path.

It's imperfect, but it covers a lot of bases.

If the users understand that "words" are faster, they will gravitate that way.

Rick James0
Related