where using MATCH()...AGAINST()

ep

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.

allan

$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

ep

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..."

allan

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

Allan

allan

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

ep

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.

allan

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

ep

Great! Thanks very much.