Forum

Code: Select all

ALTER TABLE ssep_pgd_1 ADD FULLTEXT (title);
ALTER TABLE ssep_pgd_1 ADD FULLTEXT (description);
ALTER TABLE ssep_pgd_1 ADD FULLTEXT (content);

Code: Select all

SELECT DISTINCT index_name 
FROM INFORMATION_SCHEMA.STATISTICS
WHERE (table_schema, table_name) = ('Your_Database_Name', 'ssep_pgd_1')
AND index_type = 'FULLTEXT';

Code: Select all

private function getSearchSql($words){
    $re = getTL('er_ssep_results') .': '. implode(' ', $words);
    $nr_w = count($words);
    $min_val = [['tdc'=>0.01, 't'=>0.01, 'd'=>0.01], ['tdc'=>0.03, 't'=>0.15, 'd'=>0.1], ['tdc'=>0.05, 't'=>0.4, 'd'=>0.3]];  // minim value of row results to Boolean-Mode
    $i_mv = 0;

    if($nr_w > 0) {
      if($this->obsql) {
        $start = $this->pgi * $this->rowsperpage;           // the row from which start to select the content
        $against = '+'. implode(' +', $words);   // make a string with + in front of each word, separated by space

        // traverses by number of words, and removes '+' to each iteration, from the ending, till row with result
        for($i=0; $i<$nr_w; $i++) {
          if($i > 0) $against = preg_replace('/\+([^\+]+)$/i', '$1', $against);    // removes last +

          // make SELECT for each $min_val, to set the total number of pages ($totalpages)
          for($i2=0; $i2<3; $i2++) {
            $sql[$i2] = 'SELECT COUNT(idurl) AS nr FROM '. $this->tables['pgd'] .' WHERE
MATCH(title,description,content) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val[$i2]['tdc'] .' OR
(
  MATCH (title) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val[$i2]['t'] .'  AND
  MATCH (description) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val[$i2]['d'] .'
)';
          }

          $resql[0] = $this->obsql->sqlExec($sql[0]);  $num_rows[0] = $this->obsql->num_rows;  $i_mv = 0;
          // if more than 80 results in $min_val[0], get with $min_val[1] (if higher than 3)
          if(isset($resql[0][0]['nr']) && $resql[0][0]['nr'] > 80) {
            $resql[1] = $this->obsql->sqlExec($sql[1]);  $num_rows[1] = $this->obsql->num_rows;
            if(isset($resql[1][0]['nr'])) {
              if($resql[1][0]['nr'] > 3) $i_mv = 1;
              if($resql[1][0]['nr'] > 80) {
                $resql[2] = $this->obsql->sqlExec($sql[2]);  $num_rows[2] = $this->obsql->num_rows;
                if(isset($resql[2][0]['nr']) && $resql[2][0]['nr'] > 3) $i_mv = 2;  // index of accepted $min_val
              }
            }
          }

          if($resql[$i_mv] && $num_rows[$i_mv] > 0) {
            if($i < ($nr_w - 1) && $resql[$i_mv][0]['nr'] < 7) continue;   // pass over less than 7 results, if not last iteration
            else if($resql[$i_mv][0]['nr'] > 0) {
              $this->nr_results = $resql[$i_mv][0]['nr'];
              $this->totalpages = ceil($this->nr_results / $this->rowsperpage);
              break;
            }
          }
        }

        if($this->nr_results > 0) {
          $min_val = $min_val[$i_mv];
          // CASE for SQL, title, description applyed to each word
          $sql_case = "CASE WHEN %s REGEXP '(^[^A-z0-9 ]*%s[^A-z0-9 ]* | [^A-z0-9]*%s[^A-z0-9]* | [^A-z0-9]*%s[^A-z0-9 ]*$){%s}' THEN %d ELSE 0 END";

          // traverses by number of words, store in $sql_case_words each $sql_cases to each word
          // Score for relevance (Description start: 10+$nr_w ; Title start: description_start+$nr_w)
          // Decrease $i from $start_ in next iteration, to have more relevance for first words
          $sql_case_words = [];
          $start_d = 10 + $nr_w;
          $start_t = $start_d + $nr_w;
          for($i=0; $i<$nr_w; $i++) {
            $sql_case_words[] = sprintf($sql_case, 'title', $words[$i], $words[$i], $words[$i], 1, $start_t);
            $sql_case_words[] = sprintf($sql_case, 'description', $words[$i], $words[$i], $words[$i], '1,2', $start_d);
            $start_t--;  $start_d--;
          }

          // select to get the results
          $sql = 'SELECT domain, protocol, url, title, description, content, size,
( '. implode(' + ', $sql_case_words) .'
  + MATCH (title,description,content) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) / 2
) / '. (($nr_w * 2) + 1) .' AS score
FROM '. $this->tables['pgd'] .'
LEFT JOIN '. $this->tables['url'] .' ON '. $this->tables['pgd'] .'.idurl = '. $this->tables['url'] .'.id
LEFT JOIN '. $this->tables['dom'] .' ON '. $this->tables['dom'] .'.id = '. $_SESSION['src_dom_id'] .'
WHERE
MATCH(title,description,content) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val['tdc'] .' OR
(
  MATCH (title) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val['t'] .' AND
  MATCH (description) AGAINST ("'. $against .'" IN NATURAL LANGUAGE MODE) > '. $min_val['d'] .'
)
ORDER BY score DESC LIMIT '. $start .', '. $this->rowsperpage;

          $resql = $this->obsql->sqlExec($sql);
          if($resql) {
            // If returned rows, removes those url from $urls
            if($this->obsql->num_rows > 0) $re = $resql;
          }
          else $re = $this->obsql->error;
        }
      }
      else $re = $this->obsql->error;
    }

    return $re;
}