1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL Query - SELECT MATCH AGAINST

Discussion in 'PHP' started by Weirfire, Sep 7, 2005.

  1. #1
    I'm trying to run this query

    $Result = mysql_query("SELECT title,category FROM articles WHERE MATCH(title,category) AGAINST ('text')");
    while ($get_info = mysql_fetch_row($Result)){

    and the while row is giving me this error

    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource
    SEMrush
    I've tried everything to get this to work but I'm obviously doing something wrong. Any ideas?
     
    Weirfire, Sep 7, 2005 IP
    SEMrush
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try "SELECT title,category FROM articles WHERE MATCH(title,category) AGAINST ('text')" in phpMyAdmin and see if it actually returns anything. Then you'll have a better idea what to do with the PHP side of it.
     
    T0PS3O, Sep 7, 2005 IP
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #3
    Thanks T0PS. Good idea.

    #1191 - Can't find FULLTEXT index matching the column list

    is the error I got in phpmyadmin. Does this give you any clues? It's the first time I've actually used this function.
     
    Weirfire, Sep 7, 2005 IP
  4. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #4
    Do as T0PS says and in addition are you sure you have a full text index on title and category and there is a t least one record that contains the word text.

    For extra debug in PHP you can do something like
    
    echo mysql_error();
    
    PHP:
     
    dct, Sep 7, 2005 IP
    Hodgedup and Weirfire like this.
  5. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #5
    dct, Sep 7, 2005 IP
  6. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Not sure but I think it can only do such searches on indexed columns. So you'll have to make it index and fulltext it seems. You can do that when looking at the table structure in phpMyAdmin. Best to back up first under Export.
     
    T0PS3O, Sep 7, 2005 IP
  7. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #7
    You've got it dct. I've put an index on them instead of a full text index. I'll try that now! :)
     
    Weirfire, Sep 7, 2005 IP
  8. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #8
    What about T0PS as it was a bit of a joint effort, pleased you're sorted :)
     
    dct, Sep 7, 2005 IP
  9. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Yeah, what about me?! :)

    $result = mysql_query("UPDATE reputation SET quantity = (quantity + Weirfire's Rep) WHERE member_id = 114") or die "Or die!"; :D
     
    T0PS3O, Sep 7, 2005 IP
  10. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #10
    You must spread some Reputation around before giving it to T0PS3O again.


    I did try :)

    I'll just lavish praise on you T0PS instead.

    Here goes.... If anyone ever needed a quality response in a thread like this it would come from T0PS. He's a busy man but he'll always find time for his DP family. His words are like gold spilling from his mouth. Men like these get statues built for them. I could go on but I don't want to bore everyone. :rolleyes:
     
    Weirfire, Sep 7, 2005 IP
    T0PS3O likes this.
  11. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #11
    That'll do just fine sir :) :cool: Gold spilling from my mouth... Wow! LOL
     
    T0PS3O, Sep 7, 2005 IP
  12. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #12
    lol thanks for the rep.

    Now here's my next problem

    
    SELECT title
    FROM articles
    WHERE  MATCH ( title )
    AGAINST (  '%a%' )
    
    Code (sql):
    Is that what I want to do to find all rows where an 'a' exists in the title? or do the percentage signs just break up a variable so you can check if any of the words in a variable appear in a title

    e.g.

    $keywords = "a b c";

    
    SELECT title
    FROM articles
    WHERE  MATCH ( title )
    AGAINST (  '%$keywords%' )
    
    Code (sql):
    would return all those rows where an 'a', a 'b' or a 'c' occurred in the title?

    I'll have a read over the site you posted as well dct but I thought I'd just post my query just to help anyone who might stumble across this thread in the future.
     
    Weirfire, Sep 7, 2005 IP
    srobona likes this.
  13. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,220
    Likes Received:
    778
    Best Answers:
    0
    Trophy Points:
    0
    #13
    I'd do that with WHERE title LIKE '%a%' off the top of my head. To be honest, never even seen this MATCH AGAINST business.
     
    T0PS3O, Sep 7, 2005 IP
  14. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #14
    I hadn't either until I found someone using it with their search system. I saw the LIKE method as well. I'm definitely going to have to read that site dct posted now to see what exactly it does differently. It must be something to do with the speed of queries if it's using an index.

    The LIKE way works btw :) Cheers.

    You will be dually repped whenever I can rep you again T0PS lol
     
    Weirfire, Sep 7, 2005 IP
  15. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #15
    Like is good but not always scalable, if you do like '%something' it will not be able to use any index and will do a full table scan every time which on a large table with big fields will kill performance.

    Like you 2 I'm not that familiar with MATCH but generally when you do full text indexing it basically indexes the textual content internally so that searching for words and/or phrases is a lot faster and more powerful.

    In summary if you have any concerns about performance due to many records or large fields be wary of like '%....' (like 'some%' is fine as it will still be able to use an index) and you should really look into and consider full text indexing.
     
    dct, Sep 7, 2005 IP
  16. noppid

    noppid gunnin' for the quota

    Messages:
    4,246
    Likes Received:
    232
    Best Answers:
    0
    Trophy Points:
    135
    #16
    I have to agree. I have been following this and I'm of the impression this app wil bring a server to it's knees that way it's laid out and coded so far.
     
    noppid, Sep 7, 2005 IP
  17. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,975
    Likes Received:
    364
    Best Answers:
    0
    Trophy Points:
    280
    #17
    Thanks for the comments on this.

    I'll see what I can find out and post the solution MATCH query if I find 1. :)
     
    Weirfire, Sep 7, 2005 IP