SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can someone hel in this?

    I am having records in a field (field_id) of a table (in mysql database), which is having ID stored per line, for example

    1
    2
    3
    10
    11
    13


    above is one record, and I have many records similar to that.

    and, when I am writing the query SELECT * FROM $table_name where field_id like '%1%'

    then the rows having the results like 10
    11
    13


    are also returned. I am upset. How to overcome this problem? Can someone provide me any way? Thanks.
    I love to help and also to get helped.

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please someone suggest me any good method, I found one fix to this, but it fails in most of the conditions, this is it -

    SELECT * FROM `$table_name` WHERE field_id like '%\n1%' OR '%1\n%'

    This fails, when the (field_id) have only one record, for example, '1'. I want to catch everything, where line equals to 1, whether single line in the record, or many.

    I am going mad for this.
    I love to help and also to get helped.

  3. #3
    SitePoint Enthusiast derokorian's Avatar
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can I asked why if its an ID field, its not a single UNIQUE value for every row?

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's not the main ID field, the main ID field is separate, and is unique also.
    I love to help and also to get helped.

  5. #5
    SitePoint Enthusiast derokorian's Avatar
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't say mian ID field, i just said ID field... if it holds an ID it should only hold one ID if you need a row to be linked to multiple ids you have, and in return those IDs can show up in multiple rows then you should use a many to many relationship schema...

    Personally I would use a third table to link them.

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, you are right, but there are some circumstances, under which I have no other easy option instead using this kind of thing, means storing multiple ID line by line in each record (some records have only one ID stores, means one in one line, and rest may have several lines).
    I love to help and also to get helped.

  7. #7
    SitePoint Enthusiast derokorian's Avatar
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes but look at it like this.

    Table Books
    id_book
    book_title

    Table Authors
    id_author
    author_name

    Table Books_authors
    book_id
    author_id

    Some books may only have one author, and some authors may only have one book, but its a many to many relation ship controlled in the third table, since one author MAY have more than one book, and one book MAY have more than one author... so while some books or authors will only show up once in the third table, it makes it much easier to use do a search.
    For example all books by author with id 5
    SELECT * FROM books_authors JOIN books ON books.id_book=books_authors.book_id WHERE books_author.author_id=5
    Or all authors who wrote a book with id 10
    SELECT * FROM books_authors JOIN authors ON authors.id_author-books_authors.author_id WHERE books_author.book_id=10

    instead of trying to search a row in books for an author_id using a like or vice versa

  8. #8
    SitePoint Enthusiast derokorian's Avatar
    Join Date
    Jan 2011
    Location
    Ohio
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also for you problem without reworking the DB you could set it up so all IDs are followed by a line break (do this in your insert) you could then UPDATE all rows to have an extra line break after the current ID field this way there is always an empty line break at the end

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes you are right, I thought the same earlier, but is there any other better way of fixing it, some standard way?
    I love to help and also to get helped.

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    derokorian, I think I will have to adopt your method as you said that multiple-relationships, in bookis_id and author_id.

    This seems much better way. But I don't know about the insertion and deletion commands for them, I think I have to search them on internet. Well thanks derokorian for giving me this idea.
    I love to help and also to get helped.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    my sincere advice: normalize your data

    otherwise your query will always perform a table scan, i.e. it will start off really slow and get slower and slower the more rows you have

    but if you insist on keeping the current design, try this --
    Code:
    WHERE CONCAT(',',REPLACE(REPLACE(field_id,'\r',''),'\n',','),',') LIKE '%,1,%'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2010
    Location
    ONS
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy Sir, could you please describe in detail this, that which method will be slower? The one told by derokorian or of mine ? If mine, then will the method told by derokorian perform best?

    I found one other solution of my query as

    SELECT * FROM `$table_name` WHERE field_id RLIKE '(^|[^0-9])1($|[^0-9])'
    I love to help and also to get helped.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, a properly normalized design like in post #7 will perform efficiently
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •