Search in SQL db for text from phpMyAdmin

  • markval

    (@markval)


    15 years, 3 months ago

    Hi all,

    I am trying to search the posts table for FULLTEXT. Even if mySeachText is some content of post_content or post_title and that its publish and post I always end up with the folowing error:
    #1191 – Can’t find FULLTEXT index matching the column list

    Heres my code:

    SELECT * FROM wp_posts
WHERE MATCH (post_content,post_title)
AGAINST ('mySeachText')
AND post_status = 'publish'
AND post_type = 'post'

    Any idea of what I am doing wrong?

Viewing 3 replies - 1 through 3 (of 3 total)
  • mrmist

    (@mrmist)

    15 years, 3 months ago

    You would need to add a FULLTEXT index to the wp_posts table before you can use the mysql full text search against it.

    Don’t believe that WordPress puts them on by default – you’d need to specifically add one with a CREATE INDEX statement.

    Thread Starter markval

    (@markval)

    15 years, 3 months ago

    Hi mrmist,

    I have put the two colums as FULLTEXT but it still didn’t work so I have change my QUERY a bit to:

    SELECT * FROM wp_posts
    WHERE post_title LIKE ‘%mySearchText%’ OR post_content LIKE ‘%mySearchText%’
    AND post_status = ‘publish’
    AND post_type = ‘post’

    And now it works but I think it have a draw back. The first option could search for 2 words. Ex: (mySearchText no)
    While my new QUERY will search for the whole sentence instead of 2 separate word.

    With the first QUERY I was hable to search in one column at a time but with no result. Even if the words were in the column with the same Case.

    Any Idea how I could make it work for 2 separate words or more in one query?

    mrmist

    (@mrmist)

    15 years, 3 months ago

    You’d have to use mysql functions like instr if you aren’t able to use the full text indexing. Unfortunately I suspect that both instr and your method above with % .. % will end up being pretty slow as index usage will be poor.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Search in SQL db for text from phpMyAdmin’ is closed to new replies.