Can someone hel in this?

[mohansinfh]

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.

[mohansinfh]

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.

[derokorian]

can I asked why if its an ID field, its not a single UNIQUE value for every row?

[mohansinfh]

it's not the main ID field, the main ID field is separate, and is unique also.

[derokorian]

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.

[mohansinfh]

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).

[derokorian]

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

[derokorian]

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

[mohansinfh]

Yes you are right, I thought the same earlier, but is there any other better way of fixing it, some standard way?

[mohansinfh]

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.

[r937]

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 --

WHERE CONCAT(',',REPLACE(REPLACE(field_id,'\r',''),'\n',','),',') LIKE '%,1,%'

[mohansinfh]

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])'

[r937]

yes, a properly normalized design like in post #7 will perform efficiently