Professional Web Applications Themes

searching for single entry in column - MySQL

Hi Forgive my newbiness. I am starting my first project using an sql database and am trying to decide the best way to solve this. I have a table with an "owner" field...this field will have more than one owner in it, seperated by newlines. I would then like to be able go through the table and select all the entries where say "chris" is listed as an owner. Is there a better way to setup the database for this sort of search. it seems like if I had an entry in the "owner" field like this "mike\nchris\nbob\n" and I ...

  1. #1

    Default searching for single entry in column

    Hi

    Forgive my newbiness. I am starting my first project using an sql
    database and am trying to decide the best way to solve this.

    I have a table with an "owner" field...this field will have more than
    one owner in it, seperated by newlines. I would then like to be able
    go through the table and select all the entries where say "chris" is
    listed as an owner.

    Is there a better way to setup the database for this sort of search.
    it seems like if I had an entry in the "owner" field like this
    "mike\nchris\nbob\n" and I did a select where owner ='chris'; that
    entry would not be selected because of all the stuff around it.

    Hopefully this is coherent enough, I would love to hear any comments or
    suggestions on a better implementation.

    Mike Cardeiro

    mcardeiro@yahoo.com Guest

  2. #2

    Default Re: searching for single entry in column

    [email]mcardeiro[/email] wrote:
    > Hi
    >
    > Forgive my newbiness. I am starting my first project using an sql
    > database and am trying to decide the best way to solve this.
    >
    > I have a table with an "owner" field...this field will have more than
    > one owner in it, seperated by newlines. I would then like to be able
    > go through the table and select all the entries where say "chris" is
    > listed as an owner.
    >
    > Is there a better way to setup the database for this sort of search.
    > it seems like if I had an entry in the "owner" field like this
    > "mike\nchris\nbob\n" and I did a select where owner ='chris'; that
    > entry would not be selected because of all the stuff around it.
    >
    > Hopefully this is coherent enough, I would love to hear any comments
    > or suggestions on a better implementation.
    >
    > Mike Cardeiro
    You could do a search for owner like '%chris\n%' but this would also find
    names that end in chris.

    The proper way to do this is to set up a many to many table that links
    owners to records in the target database


    Paul Lautman Guest

  3. #3

    Default Re: searching for single entry in column

    [email]mcardeiro[/email] wrote:
    > Hi
    >
    > Forgive my newbiness. I am starting my first project using an sql
    > database and am trying to decide the best way to solve this.
    >
    > I have a table with an "owner" field...this field will have more than
    > one owner in it, seperated by newlines. I would then like to be able
    > go through the table and select all the entries where say "chris" is
    > listed as an owner.
    >
    > Is there a better way to setup the database for this sort of search.
    > it seems like if I had an entry in the "owner" field like this
    > "mike\nchris\nbob\n" and I did a select where owner ='chris'; that
    > entry would not be selected because of all the stuff around it.
    >
    > Hopefully this is coherent enough, I would love to hear any comments or
    > suggestions on a better implementation.
    >
    > Mike Cardeiro
    >
    Mike,

    Google for "database normalization" to get some ideas on how to set up a
    database. A decent book on relational database design would be good, also.

    It is almost always wrong to have multiple pieces of information in one filed
    such as you are suggestion. Rather, as Paul pointed out, you should have a
    second table with two columns - recordid and owner. Recordid matches the unique
    id for the row in your original table.

    Or, better yet, a third table with ownerid and owner info (name, department,
    whatever). Then the link table would have columns recordid and ownerid.


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

  4. #4

    Default Re: searching for single entry in column

    Excellent, thanks for your responses; this makes sense.

    I already do have a seperate table with user information. I guess I
    will just need a 3rd table that links owners to files. Certainly
    more elegant than the convoluted workarounds that I was thinking up.
    Thanks for the pointers!

    Mike Cardeiro

    mcardeiro@yahoo.com Guest

Similar Threads

  1. split single pdf doc to many based on line/column
    By MichaelL in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: October 24th, 02:45 PM
  2. Searching TEXT column
    By Bob Barrows [MVP] in forum ASP Database
    Replies: 1
    Last Post: June 16th, 09:45 PM
  3. Replies: 3
    Last Post: August 23rd, 02:40 PM
  4. Help with restricting types of column entry
    By Les in forum Microsoft Access
    Replies: 0
    Last Post: July 11th, 12:56 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