where using MATCH()...AGAINST()

where using MATCH()...AGAINST()

epep Posts: 24Questions: 6Answers: 0

I have a database table with a column named TEXTCOL of type TEXT with a FULLTEXT KEY. To get something like a fulltext search on this column I have the following code fragment

$p = $_POST;
if ( isset( $p["search"])  && ( $p["search"]["value"] !== "" )){
  $word_arr = preg_split( '/ +/', trim( $p["search"]["value"] ));
  foreach( $word_arr as $word ){
    $editor -> where( 'TEXTCOL', '%' . $word . '%', 'LIKE' );
  }
}
$editor -> process( $p );
$editor -> json();

which works for me. But I know this is bad code since the fulltext index isn't used. Since my table has about 500.000 row entries, the response times are very long. I use serverside processing.

So I like to find some alternative code which uses the database fulltext index with the SQL MATCH()...AGAINST() syntax. Can you please help me.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,442Questions: 1Answers: 10,053 Site admin
    edited March 2023
    $editor->where( function ($q) {
      $q->where('MATCH( TEXTCOL ) ', 'AGAINST ( :binding )', '', false );
      $q->bind(':binding', EXPRESSION );
    } )
    

    would be the way to do it. The bind method is used to use user input without exposing the possibility of an SQL injection attack.

    Allan

  • epep Posts: 24Questions: 6Answers: 0

    Thanks. It seems that I didn't understand everything. I now have the following code:

    $p = $_POST;
    if ( isset( $p["search"])  && ( $p["search"]["value"] !== "" )){
      $word_arr = preg_split( '/ +/', strtolower( trim( $p )));
      $word_arr = array_map( function( $val ) { return "+" . $val; }, $word_arr );
      $vts = implode( ", ", $word_arr );
    
      $editor->where( function( $q ) use( $vts ){
        $q->where( 'MATCH( TEXTCOL )', '( :binding )', '', false );
        $q->bind( ':binding', "AGAINST( '" . $vts . "' IN BOOLEAN MODE)" );
      });
    
    $editor->process( $p );
    $editor->json();
    

    but this gives the error message "Invalid JSON response..."

  • allanallan Posts: 61,442Questions: 1Answers: 10,053 Site admin

    What is the response from the server? Likely it will include an error message.

    Allan

  • allanallan Posts: 61,442Questions: 1Answers: 10,053 Site admin

    On one thing I just spotted, don't use AGAINST in the binding. I see I missed that part in my comment above - I'll edit that just now.

    Allan

  • epep Posts: 24Questions: 6Answers: 0

    Sorry, I had an error in my PHP code which lead to invalid JSON. I corrected that. I also fiddled with " and ' in the strings, but that didn't help. My new code snippet

    if ( isset( $p["search"])  && ( $p["search"]["value"] !== "" )){
      $word_arr = preg_split( '/ +/', strtolower( trim( $p["search"]["value"] )));
      $word_arr = array_map( function( $val ) { return "+" . $val; }, $word_arr );
      $vts =  implode( ", ", $word_arr );
    
      $editor->where( function( $q ) use( $vts ){
        $q->where( 'MATCH( TEXTCOL )', ' :binding ', '', false );
        $q->bind( ':binding', ' AGAINST( "' . $vts . '" IN BOOLEAN MODE )');
      });
    
    $editor->process( $p );
    $editor->json();
    

    now leads to this SQL error message:

    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' AGAINST( \\"+m\\" IN BOOLEAN MODE )' AND(1=1)' at line 1

    The +m is the first character of my search string. So the correct SQL fragment should be

    ... AGAINST( '+m' IN BOOLEAN MODE )
    

    but whatever I do I am not able to have the +m in correct parenthes.

  • allanallan Posts: 61,442Questions: 1Answers: 10,053 Site admin
    Answer ✓

    Did you try it with AGAINST not in the binding like I suggested?

      $editor->where( function( $q ) use( $vts ){
        $q->where( 'MATCH( TEXTCOL )', 'AGAINST( :binding IN BOOLEAN MODE )', '', false );
        $q->bind( ':binding', $vts);
      });
    

    That, I think should do it.

    Allan

  • epep Posts: 24Questions: 6Answers: 0

    Great! Thanks very much.

Sign In or Register to comment.