Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    120
    Thanks
    13
    Thanked 3 Times in 3 Posts

    Search in more than one field won't work...

    Code:
    SELECT * FROM persons WHERE name AND last_name LIKE '%$_POST[search]%'
    So, I made the search box which is used to search for names, what i want to do is, make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.

    BTW, i'm using PHP too...

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Dhuan View Post
    Code:
    SELECT * FROM persons WHERE name AND last_name LIKE '%$_POST[search]%'
    So, I made the search box which is used to search for names, what i want to do is, make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.

    BTW, i'm using PHP too...
    - you must specifiy the condition for both name and last_name, only name is not a expression
    - quote keys in arrays, is $_POST['search']
    - don't pass POST data to mysql without checking before, is a bad idea.
    - don't fetch *, usualy you don't need all fields from that table

    PHP Code:
    $mysearch $_POST['search'];
    $query "select * from persons where name like '%" $mysearch "%' and last_name like '%"$mysearch "%'"
    best regards

  • Users who have thanked oesxyl for this post:

    Dhuan (04-17-2009)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Right on.

    Excepting only that, if you read his message, and not his bogus SQL, you find that what he *REALLY* needs is an OR, not an AND.
    make the MySQL search the typed name in not only the 'name' field but also in the 'last_name' field.
    It wouldn't make sense to look for "JON" in both the first name and last name of "Mary Jones"; it would make sense to look for "JON" in *either* the first OR last name.

    So:
    Code:
    $mysearch = $_POST['search'];
    ... should have code here for sanitizing $mysearch...
    $query = "select * from persons "
           . "where name like '%" . $mysearch . "%' " 
           . " OR last_name like '%". $mysearch . "%'";

  • Users who have thanked Old Pedant for this post:

    Dhuan (04-17-2009)

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    Right on.

    Excepting only that, if you read his message, and not his bogus SQL, you find that what he *REALLY* needs is an OR, not an AND.
    you could be right, but I'm not sure. That "not only in" could mean an 'and'.
    Anyway both are assumption so is a good idea that you rise this problem. Op could this way think what realy want.

    It wouldn't make sense to look for "JON" in both the first name and last name of "Mary Jones"; it would make sense to look for "JON" in *either* the first OR last name.
    you assume that is english, I don't think this apply to any country,

    best regards.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Yes, and his fields are "Name" and "last_name", not "first_name" and "last_name". I was just going with the odds, I admit it.

    Seems to be what he wanted.

  • #6
    Regular Coder
    Join Date
    Mar 2009
    Posts
    120
    Thanks
    13
    Thanked 3 Times in 3 Posts
    The 'OR' command was what i nedeed, thank you guys!
    Actually, I had tried 'OR' before, but in a wrong way:

    Code:
    SELECT * FROM persons WHERE name OR last_name LIKE '%$_POST[search]%'
    that, as you may have expected, didn't quite work...

    then, after reading your posts...

    Code:
    $search=$_POST['search'];
    
    $query="SELECT * FROM persons WHERE name LIKE '%$search%' OR last_name LIKE '%$search%'";


    Yes, and his fields are "Name" and "last_name", not "first_name" and "last_name". I was just going with the odds, I admit it.

    Seems to be what he wanted.
    hmm... the 'name' field is for first names only... should I name it first name then(though i dont think its necessary)?

    - don't fetch *, usualy you don't need all fields from that table
    Is there any risk in doing so? Will the page load slower or something like that?
    Last edited by Dhuan; 04-17-2009 at 05:25 PM.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    hmm... the 'name' field is for first names only... should I name it first name then(though i dont think its necessary)?
    Doesn't matter at all so long as YOU know what it all means. You could use "foo" and "bar" to mean address and zip code and it wouldn't matter.

    - don't fetch *, usualy you don't need all fields from that table
    Is there any risk in doing so? Will the page load slower or something like that?
    No risk. Could things run slower? Yes, certainly.

    Example: Say you were getting 1000 records and each record had an image in it so that the average record size was maybe 200KB, then you just transferred 200MB of data from the DB server to the Web server. If all you really needed was the name of each image--maybe 30 bytes each?--for a total of 30KB, then you just transferred 199.97 extra megabytes.

    And, I will point out that your query *COULD* return a *MASSIVE* amount of data. If you have (say) 10,000 people in that table and somebody enters a search term of "e", then you will be returning a record for every person who has the letter "e" in his/her first or last name.

    If you were only getting 1 or 2 records, the SELECT * wouldn't be such a big deal. But with this query, yes, I'd be careful about only getting the fields you actually will be using.


  •  

    Posting Permissions

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