Professional Web Applications Themes

search on first name or last name, or both - MySQL

I know I could do this with 3 different searches, but I presume there is an easier way I can not see. I have a form that is submitted to a PHP script that has 2 fields, first name and last name. If just the first name is submitted I want to search by first name and return all that match. If just the last name is submitted I want to search by last name and return all that match. If both are submitted, I want to search by last name and return all that match both. By "match" I ...

  1. #1

    Default search on first name or last name, or both

    I know I could do this with 3 different searches, but I presume there
    is an easier way I can not see.

    I have a form that is submitted to a PHP script that has 2 fields,
    first name and last name.
    If just the first name is submitted I want to search by first name and
    return all that match.
    If just the last name is submitted I want to search by last name and
    return all that match.
    If both are submitted, I want to search by last name and return all
    that match both.

    By "match" I mean that I want to return all that contain the first or
    last name, e.g.: partial names are submitted and full names are found.

    I have an index: fname that is the first then last names (2 fields)
    I have an index: lname that is the last then first (2 fields)

    Is this clear ?
    I guess there are two questions:
    1) how to match a partial name ?
    2) can I do all 3 cases in one search ?

    bill

    bill Guest

  2. #2

    Default Re: search on first name or last name, or both

    bill wrote: 

    Bill,

    Generally when I need to do searches like this I create the SQL in a
    programming language (i.e. PHP, Perl, etc.) according to the input.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: search on first name or last name, or both

    On Jul 4, 10:42 am, Jerry Stuckle <net> wrote: 





    >
    > Bill,
    >
    > Generally when I need to do searches like this I create the SQL in a
    > programming language (i.e. PHP, Perl, etc.) according to the input.
    >
    > --[/ref]

    Jerry,
    Ok, I can do that. Now about the other part of the question:

    what is the syntax for doing a selection on a partial key.
    eg: if the user enters "Smi" when searching for Smith or Smilie
    or . . .


    bill Guest

  4. #4

    Default Re: search on first name or last name, or both

    bill wrote: 
    >> Bill,
    >>
    >> Generally when I need to do searches like this I create the SQL in a
    >> programming language (i.e. PHP, Perl, etc.) according to the input.
    >>
    >> --[/ref]
    >
    > Jerry,
    > Ok, I can do that. Now about the other part of the question:
    >
    > what is the syntax for doing a selection on a partial key.
    > eg: if the user enters "Smi" when searching for Smith or Smilie
    > or . . .
    >
    >[/ref]

    .... WHERE column_name LIKE 'Smi%';

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: search on first name or last name, or both

    On 4 Jul, 15:10, bill <com> wrote: 

    Regarding:

    "If both are submitted, I want to search by last name and return all
    that match both."

    This is not possible. You will need to search by both last name and
    first name, otherwise you have no way of knowing whether the first
    name matches.

    Also you say "By "match" I mean that I want to return all that contain
    the first or last name, e.g.: partial names are submitted and full
    names are found."

    This contradicts the part where you say that you must match "both". If
    you want a match that contains the first "or" last name, then that is
    a search that matches "either".

    Either = or
    Both = and

    Take some time to sort out your boolean logic

    Captain Guest

  6. #6

    Default Re: search on first name or last name, or both

    On Jul 5, 6:09 am, Captain Paralytic <com> wrote: 





    >
    > Regarding:
    >
    > "If both are submitted, I want to search by last name and return all
    > that match both."
    >
    > This is not possible. You will need to search by both last name and
    > first name, otherwise you have no way of knowing whether the first
    > name matches.
    >
    > Also you say "By "match" I mean that I want to return all that contain
    > the first or last name, e.g.: partial names are submitted and full
    > names are found."
    >
    > This contradicts the part where you say that you must match "both". If
    > you want a match that contains the first "or" last name, then that is
    > a search that matches "either".
    >
    > Either = or
    > Both = and
    >
    > Take some time to sort out your boolean logic[/ref]

    Actually, my logic was fine, my syntax was sloppy.
    my logic is both, but I meant in either (of 3 cases)

    Anyway, I have it running now just fine, thanks for the help.

    bill

    bill Guest

Similar Threads

  1. ANN: InterAKT Site Search - search in multiple tables
    By Alexandro Colorado in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 29th, 04:23 PM
  2. #25786 [NEW]: PHP website uses cookies to remember last search phrase in search box
    By tipsen at imada dot sdu dot dk in forum PHP Development
    Replies: 0
    Last Post: October 8th, 08:24 AM
  3. Search within Search Results
    By Maria Kovacs in forum ASP Database
    Replies: 1
    Last Post: October 7th, 04:45 PM
  4. XP Search - how to not search inside .ZIP files
    By Tom D. in forum Windows XP/2000/ME
    Replies: 1
    Last Post: July 16th, 08:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139