#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    6
    Rep Power
    0
    I’d like to perform an “OR” search in a MySQL table for several columns which returns the result ordered by the hit rate. E.g., somebody is searching for the strings ‘daimler’ OR ‘chrysler’, then I run the following MySQL query:

    SELECT * FROM products WHERE
    lcase(manufacturer) REGEXP lcase('daimler|chrysler')
    OR lcase(kategorie) REGEXP lcase('daimler|chrysler')
    OR lcase(itemid) REGEXP lcase('daimler|chrysler')
    OR lcase(description) REGEXP lcase('daimler|chrysler')

    How can I extend the query to order the rows by the highest hit rate? Is it possible in MySQL or does anybody know a solution for PHP?

    Thanx.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    24
    Rep Power
    0
    just add the "ORDER BY columnname" to you query
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    6
    Rep Power
    0
    Thank you anand!

    Unfortunately, "order by" will not help. Maybe my question was not clear (as I am not a native English speaker): I'd like to have the query result ordered by the highest occurence of the searched strings in <b>all</b> columns.

    This means, when I search for two phrases (Boolean "OR") in several columns, I want to have the rows listed on top, where the most phrases where found in total (i.e. in all columns).
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    It would be along the lines of:

    select *,(if(lcase(manufacturer) REGEXP lcase('daimler|chrysler') ,1,0)+if(lcase(kategorie) REGEXP lcase('daimler|chrysler'),1,0)+if(lcase(itemid) REGEXP lcase('daimler|chrysler'),1,0)+if(lcase(description) REGEXP lcase('daimler|chrysler'),1,0)) as matches from products where lcase(manufacturer) REGEXP lcase('daimler|chrysler')
    OR lcase(kategorie) REGEXP lcase('daimler|chrysler')
    OR lcase(itemid) REGEXP lcase('daimler|chrysler')
    OR lcase(description) REGEXP lcase('daimler|chrysler') order by matches desc;

  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    6
    Rep Power
    0
    That's fabulous rod k, thanky you!

    Can you recommend any books or online tutorials where I can learn more about SQL queries, just like the one above? All books or tutorial I read so far, offered rather basic knowledge, but nothing about more complicated queries.

Similar Threads

  1. query syntax error
    By collegebloke in forum MS SQL Development
    Replies: 1
    Last Post: January 19th, 2004, 04:08 PM
  2. Overwriting mysql query with the query results
    By onerandomday in forum PHP Development
    Replies: 1
    Last Post: January 14th, 2004, 04:45 AM
  3. Syntax error in INSERT INTO query
    By Lee Miles in forum Visual Basic Programming
    Replies: 6
    Last Post: December 19th, 2003, 07:37 AM
  4. checkbox insert query
    By numb in forum PHP Development
    Replies: 11
    Last Post: December 15th, 2003, 11:10 AM
  5. Generating external js subcategory list from MySQL using PHP
    By NanoEntity in forum PHP Development
    Replies: 4
    Last Post: December 15th, 2003, 09:50 AM

IMN logo majestic logo threadwatch logo seochat tools logo