Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2013
    Posts
    8

    Unanswered: Help needed with LIKE query

    Evening all,

    I'm hoping someone is able to help me out with this. Following on from a previous thread (although completely unrelated) I'm in the process of creating my first database.

    I have now managed to solve the previous problem, but now I am creating a search page for the database. The page is basic and just contains one search box, it is hoped that the end user can simply type in a few key words and have all the relevant documents displayed to them.

    The search query I have created works up to an extent, but the users would have to be very specific with the wording to get the desired results.

    The search statement is:

    Code:
    $query = "SELECT * FROM issues WHERE issue LIKE '%$searchTerm%'";
    If the search term was "Samsung Lines" this wouldn't yield any results. BUT, if the search term was "Samsung SCX6322DN - Lines" the query works perfectly.

    Is there a way to query the DB and omit the model number (SCX6322DN) in the search term?

    Any help would be appreciated,

    Many thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are multiple choices.

    The best answer would be to enable the MySQL :: MySQL 5.0 Reference Manual :: 12.9 Full-Text Search Functions

    The next best answer would be to preprocess your $searchTerm so that you replaced anything that you didn't care about (like whitespace) with percent signs. In your case the "Samsung lines" would be processed into "%Samsung%lines%", but note that this has some serious drawbacks... The terms need to be in the same order that they appear in the database, and this type of search performs an AND operation instead of an OR operation so each string must be present.

    A further refinement would be to break up each of the words within $searchTerm so that they because separate LIKE clauses, and then wire those together with OR operations.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2013
    Posts
    8

    Thanks Pat!

    Hey Pat,

    Thanks for the reply with that

    I am having a few issues with the FULLTEXT query. If possible, could you point to where I am going wrong? I have browsed through that manual you have kindly linked, but this goes way over my head as a beginner.

    I have firstly amended the DB enginer with the following;

    Code:
    alter table issues ENGINE - MYISAM;
    This worked fine and amended the engine. Next, I enabled the FULLTEXT by doing this;

    Code:
    ALTER TABLE issues ADD FULLTEXT (issue);
    This seemed to work again and didn't give me any error messages.

    Then I get to the bit I am having issues with! I amended the SQL query to;

    Code:
    SELECT * FROM issues WHERE issue AGAINST ('%$searchTerm%')
    When I search on the webpage, I now get a Syntax error

    I would like to take this approach from the suggestions you give as I think this would be the correct way and would be future proof should I ever want to update or change anything

    Again, many thanks for your help so far

  4. #4
    Join Date
    Nov 2013
    Posts
    8

    Success!

    Hey Pat!

    Just wanted to reply back to say your suggestion has worked perfectly!

    After realising that there was a next page in that manual I have found how to correctly structure the search query;

    Code:
    $query = "SELECT * FROM issues WHERE MATCH (issue) AGAINST ('%$searchTerm%')";
    Thanks again and I hope this thread helps someone else out in the future!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Those blasted manuals always seem to have "next pages", don't they? It gets even more frustrating when they add the "next page" a week after you had the problem!

    I'm glad to hear that you've got things working the way you wanted!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2013
    Posts
    8

    Whooops

    Hello again!

    I'm back with another question...this time I have checked through the documentation and I cannot see where I am going wrong!

    I have created the query as;

    Code:
    $query = "SELECT * FROM issues WHERE MATCH (issue) AGAINST ('$searchTerm' IN BOOLEAN MODE)";
    This works fine when typing one search term in. BUT, when an end user types two words in, the search results aren't displayed as wanted. For example, if the user types in "Samsung", they get the desired result, all documents with the word "Samsung" are displayed. However, if they type in "Samsung lines", they are still given all the documents with the "Samsung" in the title.

    What I would like to do, is only display documents with the words "Samsung" & "Lines" in the title. I believe this can be corrected using the BOOLEAN mode, but I am having trouble getting my head around this. I can understand the + and - BOOLEAN attributes, but I loose it when trying to relate this to myHTML search box ($searchTerm).

    Does anyone have any information they could point me to? Or is my database structured in such a way that this type of narrowed search isn't possible?

    Many thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check out MySQL :: MySQL 5.0 Reference Manual :: 12.9.2 Boolean Full-Text Searches for details (which you probably already have). The key lies on that web page near the string
    Code:
    '+apple macintosh'
    What I think you want in order to find columns that contain both words is:
    PHP Code:
    $searchTerm "+Samsung lines"
    $query "SELECT *
       FROM issues
       WHERE MATCH (issue) AGAINST ('
    $searchTerm' IN BOOLEAN MODE)"
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2013
    Posts
    8
    Thanks again Pat

    I can sort of understand the Boolean, but when trying to relate this to the search term the end user enters, I loose it.

    Would I have to build the fulltext-boolean into my query? The problem I have is that there are many manufacturers that would share different documents with the term "line".

    So the user may enter;

    Samsung lines
    Sharp lines
    Xerox lines
    Konica lines

    I would only want the relevant documents to be returned.

    So, would I have to do something like this in the query;

    Code:
    $searchTerm = "+Samsung +HP +Xerox +Konica +Sharp" 
    $query = "SELECT * 
       FROM issues 
       WHERE MATCH (issue) AGAINST ('$searchTerm' IN BOOLEAN MODE)";
    My understanding of this would be that if the user search contains "HP" & "Lines" only these documents would be displayed.

    I should probably tell you the structure of my DB to make sure I'm on the same page. I have one table in the DB which is 'issues' and the table has the following colums; id, issue, resolution, helpFile, helpFile2 & video. I hope this layout is OK for what I want to achieve as I have been reading on linking tables and how it may be better to have a manufacturers table linked to this issue table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •