fulltext php search

Discussion in 'PHP' started by trichnosis, Jan 13, 2008.

0
  1. #1
    Hi;

    I'm writing some php code for fulltext mysql searching.

    At last my code creates one sql query like

    

SELECT * FROM jos_content WHERE ((LOWER(title) LIKE LOWER('%digital%') AND LOWER(title) LIKE LOWER('%point%')) OR (LOWER(introtext) LIKE LOWER('%digital%') AND LOWER(introtext) LIKE LOWER('%point%')) OR (LOWER(fulltext) LIKE LOWER('%digital%') AND LOWER(fulltext) LIKE LOWER('%point%'))) and sectionid=17 and catid=207 and state=1 LIMIT 0,25
    PHP:
    It's the query when you search digital point.

    sectionid, catid, state, title, introtext and fulltext are the coloumns from my database.

    If I remove 'fulltext' coloumn from the query , everythink goes well but if I add it, It's giving error and code does not work.

    How can i solve this?

    Thanks for the comments
     
    trichnosis, Jan 13, 2008 IP
  2. nico_swd

    nico_swd Prominent Member

    Messages:
    4,153
    Likes Received:
    344
    Best Answers:
    18
    Trophy Points:
    375
    #2
    First off, remove all LOWER()s... SELECT queries are case-insensitive. And there's absolutely no reason to convert the column names to lower case.

    Then put backslashes around the word "fulltext", because it's a reserved keyword and if you have a column called like that it's confusing MySQL.

    
SELECT * FROM table_name WHERE `fulltext` LIKE '%something%' ....
    Code (sql):
     
    nico_swd, Jan 13, 2008 IP