Member Avatar for mohansinhf

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?

I found one fix to this, but it fails in most of the conditions, this is -

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.

Can someone provide me any way? Thanks.

  • 2 Contributors
  • 4 Replies
  • 80 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by mohansinhf

Recommended Answers

All 4 Replies

Member Avatar for smantscheff

It's quite unclear what you want. Do you want all records with a field_id=1? Then just select them. Or do you want all records in which a text field contains the string representation of the number 1? In this case use a regular expression:

select * from mytable wher field_id rlike '(^|[^0-9])1($|[^0-9])'

Also it looks like a flawed database design to have several id entries in one field.

Edited by smantscheff because: n/a
Member Avatar for mohansinhf

Wow. Thanks smantscheff Sir, thats really great. It worked. Thanks a lot.

Member Avatar for smantscheff

Please mark this thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.