CODEWITHSUNDEEP
phpmysql
user3514052
user3514052

Reputation: 429

how to get only similar word with like mysql query

I make an autocomplete search and I want to get words suggestions from the descriptions. For example I have a post description: "this is sample description".

I use this query

$sql ="SELECT post_content FROM {$wpdb->posts} WHERE LOWER(post_content) LIKE LOWER('%".$s."%') AND post_status='publish'";

but I get all the matching description. How can i get only one word that matches query? For example if I write "sam..." I want to get only "sample" word, or if i write "sample des..." I want to get "sample description" not the full description where that word is it.

Upvotes: 0

Views: 89

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can try this if it works for you:

sample

SELECT 
    SUBSTRING_INDEX(
        SUBSTRING('this is sample description and more' FROM LOCATE('sample des','this is sample description and more'))
        ,' '
        ,LENGTH('sample des') - LENGTH(replace('sample des', ' ', '')) +1
    ) as result;

result

result
sample description

try this query for your sql:

$sql ="
SELECT 
    SUBSTRING_INDEX(
        SUBSTRING(post_content FROM LOCATE('".$s."',post_content))
        ,' '
        ,LENGTH('".$s."') - LENGTH(replace('".$s."', ' ', '')) +1
    )
 FROM {$wpdb->posts} 
 WHERE LOWER(post_content) LIKE LOWER('%".$s."%') AND post_status='publish'";

please let me know if it works for you (its not testet)

Upvotes: 1

Related Questions