GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

order by, with like clause

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • order by, with like clause

    i am using this statment

    select * from o_articles where catid='1' and forid='2' and ( article
    like('%big house%') OR article like('%big%') OR article like('%house%')
    and status='1' OR title like('%big house%') ) order by 'article' asc
    limit 0,10

    there are two rows matching this , one having at its very begining the
    word "big house" another have just the word "big"
    i am trying to get them ordered by the NEAREST MATCHING comes first,
    but it did not work, i change the Order by clause to all possible
    values it did not work mad:

    it always get orderd by the catid as it is the primary key,
    the fields article is full text and title is varchar

    if you can help i will be so thankful to you,
    thanks

  • #2
    What you can use is a STRPOS function.

    Are atid, forid and status stored as INTs in the table? You're comparing them as strings, killing the performance and utalization of any indexes that could be used to speed up the execution of the query.

    Comment


    • #3
      Ramma,

      Your query is rather strange - you're ordering by "article" which is constant why you would expect it to order by nearest match ?

      In your case you can ether use boolean full text search and do order by "relevance" or explicitly count number of matched like statements, something like:

      select ("aa" like "%a%") + ("bbb" like "%b%");

      Comment


      • #4
        What you need is good'ol full text search. It's a lot easier on the server

        Comment

        Working...
        X