Professional Web Applications Themes

where value in array - MySQL

Is teher anyway to replace a where clause like : I have a serie of string values in an array ["01", "02", "58", "61"...] is there anyway to not to build the SELECT clause with a loop : WHERE dpt_code = '01' OR dpt_code = '02' OR dpt_code = '58' OR dpt_code = '61'... but rather use something like WHERE dpt_code IN [ a set.. of values] thanks joss...

  1. #1

    Default where value in array

    Is teher anyway to replace a where clause like :
    I have a serie of string values in an array ["01", "02", "58", "61"...]

    is there anyway to not to build the SELECT clause with a loop :
    WHERE dpt_code = '01' OR dpt_code = '02' OR dpt_code = '58' OR dpt_code
    = '61'...
    but rather use something like WHERE dpt_code IN [ a set.. of values]

    thanks

    joss



    Josselin Guest

  2. #2

    Default Re: where value in array

    Josselin wrote: 

    Yes, but you cannot avoid the loop (*). Just change a little bit the way
    you are building the SELECT clause. Instead of

    clause = "SELECT ... WHERE"
    foreach array_element BEGIN
    clause = clause + " dpt_code = '" + array_element + "' OR"
    END
    clause = clause - 3 characters
    clause = clause + " ORDER BY ..."

    change the *'d lines

    * clause = "SELECT ... WHERE dpt_code in ["
    foreach array_element BEGIN
    * clause = clause + array_element + ", "
    END
    * clause = clause - 2 characters
    * clause = clause + "] ORDER BY ..."

    (*) there may be languages that do the foreach loop internally, with a
    single instruction.

    --
    I (almost) never check the dodgeit address.
    If you *really* need to mail me, use the address in the Reply-To
    header with a message in *plain* *text* *without* *attachments*.
    Pedro Guest

  3. #3

    Default Re: where value in array

    On 2006-11-07 21:17:03 +0100, Pedro Graca <com> said:
     
    >
    > Yes, but you cannot avoid the loop (*). Just change a little bit the way
    > you are building the SELECT clause. Instead of
    >
    > clause = "SELECT ... WHERE"
    > foreach array_element BEGIN
    > clause = clause + " dpt_code = '" + array_element + "' OR"
    > END
    > clause = clause - 3 characters
    > clause = clause + " ORDER BY ..."
    >
    > change the *'d lines
    >
    > * clause = "SELECT ... WHERE dpt_code in ["
    > foreach array_element BEGIN
    > * clause = clause + array_element + ", "
    > END
    > * clause = clause - 2 characters
    > * clause = clause + "] ORDER BY ..."
    >
    > (*) there may be languages that do the foreach loop internally, with a
    > single instruction.[/ref]

    thanks Pedro, indeed using Ruby I can do it with internal loop

    #(read array... split limits + insert current one)...
    _LIMITS = city.department.dpt_limits.split(",").insert(0,c ity.zip_codel[0,2])
    # read all record using Rails ActiveRecord , loop on each one 'd' and test
    dpt_ids = Department.find_where(:all) {|d| d.code === _LIMITS }

    joss


    Josselin Guest

Similar Threads

  1. array data matches but array created in loop doesn't work
    By Reed Law in forum PHP Development
    Replies: 1
    Last Post: August 13th, 04:25 AM

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