MYSQL Query help?

Discussion in 'Web Design and Development' started by e7e, May 17, 2010.

  1. e7e macrumors member

    Joined:
    Dec 15, 2007
    #1
    Not sure if I am posting in the right section, but here it goes.

    I am currently trying to learn MYSQL, trying various task using PHP and javascript. So I created a search function on my web-site. My Person table looks like this:

    Code:
    ID
    fname
    lname
    
    And when the user is searching for a person, he can input first name or last name in the search fiend and that works fine. The problem occurs when the user is typing the full name of the person, like for instance "Steve Jobs".

    My SQL query code looks like this:
    Code:
    SELECT * FROM person WHERE fname LIKE "%$QueryString%" OR lname LIKE "%$QueryString%"
    
    The problem is, if I input full name, I wont get anything. For example if I type "Steve Jobs" I wont get a single result, even though I know it exists in the database.

    So can anybody help me with this SQL query?
     
  2. plinden macrumors 68040

    plinden

    Joined:
    Apr 8, 2004
    #2
    So you're saying if you enter "Steve Jobs", your query is:
    Code:
    SELECT * FROM person WHERE fname LIKE "%Steve Jobs%" OR lname LIKE "%Steve Jobs%"
    You don't see why it's failing?
     
  3. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    Try splitting the words, if your sending it Steve Jobs then it is looking for a match of "Steve Jobs" on first name and last name.

    What your wanting to do it split "Steve Jobs" into "Steve" and "Jobs" and search for them individually.
     
  4. e7e thread starter macrumors member

    Joined:
    Dec 15, 2007
    #4
    I see why its failing, because it tries to search for "Steve Jobs" in fname and lname, I am just not sure how to write a proper query this. I want to somehow combine the fname+lname.

    So I can perhaps do something like: Query LIKE %FNAME+LNAME%
     
  5. jaikob macrumors 6502

    jaikob

    Joined:
    Jul 1, 2008
    Location:
    Freeland, MI
    #5
    Code:
    
    $query = "John Doe";
    
    $mysql_query = "SELECT *, UPPER(CONCAT(fname,' ',lname)) as fullName, UPPER(fname) as firstName, UPPER(lname) as lastName FROM person HAVING firstName LIKE '%".$query."%' OR lastName LIKE '%".$query."' OR fullName LIKE '%".$query."%'";
    
    $result = mysql_query($mysql_query) or die(mysql_error());
    
    Try this. The variable $query is your search variable.

    What it does is selects all records, converts first name and last name to upper case, giving them each their own sql variable, and then combining them creating a new variable called fullname. So it's basically searching for a match in three different ways.
     
  6. e7e thread starter macrumors member

    Joined:
    Dec 15, 2007
    #6
    Fantastic, so this is how you solve it. Thanks alot :D
     
  7. stomer macrumors 6502a

    Joined:
    Apr 2, 2007
    Location:
    Leeds, UK
    #7
    That query is grossly inefficient. If the person table ever has a non-trivial amount of records, lets say a million records, then that query will be guaranteed to have serious performance issues.

    - You're asking MySQL to retrieve *every single* record from person and so MySQL will not be able to use any indexing to speed up the query.
    - If person has a million records, then you're asking MySQL to perform 3m UPPER()s, 1m CONCAT()s and 3m LIKEs!!!
    - Even if person doesn't have lots of records, you're still asking MySQL to do more work than it needs to. If your site/app usage results in a non-trivial number of name searchs, then your site/app could grind to a halt.

    If you want to perform natural language searches on MySQL table, then MySQL provides a certain type of index for that. http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

    Code:
    ALTER TABLE `fullname` ADD FULLTEXT (fname,lname);
    SELECT * FROM person WHERE MATCH(fname,lname) AGAINST ('foo bar');
    
    I've not tested the above, but it's the direction where you should be heading. My main point is, only ever use LIKEs on small resultsets, you definitely DO NOT want to be performing likes on all the records in a table.
     
  8. jaikob macrumors 6502

    jaikob

    Joined:
    Jul 1, 2008
    Location:
    Freeland, MI
    #8

    I'm sure he isn't going to be storing 1 million people. I'm also sure you won't even be storing that much data to notice any problems. :rolleyes:
     
  9. stomer macrumors 6502a

    Joined:
    Apr 2, 2007
    Location:
    Leeds, UK
    #9
    It probably wont take 1 million records to slow down his site, mostly likely at lot less. 1 million was just an example.

    Yes, he'll most likely get away with it, but sometime down the line, he'll be caught out. Since it's only 2 lines of code, you might as well do it right from the beginning.

    If any of my developers wrote such code, they'd get a slap around the face.
     
  10. e7e thread starter macrumors member

    Joined:
    Dec 15, 2007
    #10
    Thanks for this tip, as mention I am only a beginner at MYSQL, I appreciate tips like these. :)
     

Share This Page