Professional Web Applications Themes

SQL statement question - PHP Development

Howdy, I have a table full of names, last & first, and I would like to do a SQL select to get all of the last names that start with A-H (I'll run another for I-P & Q-Z). The resulting last name, first name combo needs to end up in a drop down box. I can get the results of a query into a drop down just fine, but I can't get the SQL statement to work right. I've tried a few statements including LIKE, but nothing seems to work. Any ideas on how I could go about doing this? ...

  1. #1

    Default SQL statement question

    Howdy,

    I have a table full of names, last & first, and I would like to do a SQL
    select to get all of the last names that start with A-H
    (I'll run another for I-P & Q-Z). The resulting last name, first name combo
    needs to end up in a drop down box.

    I can get the results of a query into a drop down just fine, but I can't get
    the SQL statement to work right.
    I've tried a few statements including LIKE, but nothing seems to work. Any
    ideas on how I could go about doing this?

    Thanks,
    Jason


    Jason Reljac Guest

  2. #2

    Default Re: SQL statement question

    You didn't state clearly whether the firstnames and lastnames were in the
    same field together or if they were different fields. If they are in
    different fields, then Leslie's suggesting will work great. If your
    firstnames and lastnames are in the same field, then you need something more
    like this:

    Leslie's Suggestion:
    SELECT * FROM table WHERE lname REGEXP "^[A-H]"

    Modifed for fullname field:
    SELECT * FROM table WHERE fulllname REGEXP " [A-H]"

    Notice that Leslie used a '^' symbol for start of line, where I used a ' '
    (space). The space idea will only work if you can assume there is exactly
    one space in each field, which is a risky assumption. If the first names
    and last names are combined and the number of spaces in the field are
    unpredicatable, that is a real challenge. :-)

    Jerry

    "Jason Reljac" <jmr109hotmail.com> wrote in message
    news:vhisl830s4v002corp.supernews.com...
    > Howdy,
    >
    > I have a table full of names, last & first, and I would like to do a SQL
    > select to get all of the last names that start with A-H
    > (I'll run another for I-P & Q-Z). The resulting last name, first name
    combo
    > needs to end up in a drop down box.
    >
    > I can get the results of a query into a drop down just fine, but I can't
    get
    > the SQL statement to work right.
    > I've tried a few statements including LIKE, but nothing seems to work.
    Any
    > ideas on how I could go about doing this?
    >
    > Thanks,
    > Jason
    >
    >

    JerryH Guest

  3. #3

    Default Re: SQL statement question

    JerryH wrote:
    > You didn't state clearly whether the firstnames and lastnames were in
    > the same field together or if they were different fields. If they
    > are in different fields, then Leslie's suggesting will work great.
    > If your firstnames and lastnames are in the same field, then you need
    > something more like this:
    >
    > Leslie's Suggestion:
    > SELECT * FROM table WHERE lname REGEXP "^[A-H]"
    >
    > Modifed for fullname field:
    > SELECT * FROM table WHERE fulllname REGEXP " [A-H]"
    >
    > Notice that Leslie used a '^' symbol for start of line, where I used
    > a ' ' (space). The space idea will only work if you can assume there
    > is exactly one space in each field, which is a risky assumption. If
    > the first names and last names are combined and the number of spaces
    > in the field are unpredicatable, that is a real challenge. :-)
    >
    > Jerry
    If they are in separate fields (there is a lname field or something like
    that) then both of you are nuts for using REGEXPs for such a simple
    thing...just do WHERE lname BETWEEN 'aaa' AND 'hzz' etc. (Or if you are
    worried about names starting with hzz, for example, hzzsomething, you can do
    WHERE lname >= 'A' AND lname < 'I' (note that that's an I in quotes, not a
    T...)

    Using regexps is way overkill and will likely be slower.


    Agelmar Guest

Similar Threads

  1. Replies: 9
    Last Post: April 27th, 04:44 AM
  2. Simple If statement question
    By quovadimus02 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 22nd, 06:37 PM
  3. If statement question
    By Smriti Dev in forum PHP Development
    Replies: 3
    Last Post: December 8th, 11:12 PM
  4. real if then statement question.
    By aoninja webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 0
    Last Post: October 25th, 04:51 AM
  5. Select Statement Question (Again)
    By Largo SQL Tools in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: July 14th, 05:02 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