MySQL Complicated Query

Hi Friend.

I want a query to find some ids from field. But it's so difficult for me.

table field name: product_ids

product_ids (value): 1, 2, 3, 4, 5 (These all values are in one field with comma, It's a set of id's in one record field).

Now i want to search 3 & 4 from this field.

If user find 3 & 4, so i'll have to search 3 & 4 from product_ids

I've tried "like" & "in"... But! still getting issue.

Please help me out.

I Tried E.g:

1) select * from table_name where product_ids in (3, 4) // Not Working...

2) select * from table_name where product_ids like '%3%' or product_ids like '%4%' // Not Working...

Please help me out friends.

 

I don't see any reason why your second query wouldn't have worked:

SELECT * FROM table_name WHERE product_ids LIKE '%3%' OR product_ids LIKE '%4%'

Code (markup):

The only thing I can say is make sure that the table_name and column name are correct. Sorry :/

 

Thanks for reply Thorlax402

I've already tried this query

When i'm executing your query (SELECT * FROM table_name WHERE product_ids LIKE '%3%' OR product_ids LIKE '%4%')

It gets "33" behalf of '%3%' and "44" behalf of '%4%'

It's not fetching exact match from field using like '%xyz%'

Thanks again.

 

Because the type is int and you can't do like exact-ing them..

 

Thanks for reply Youmax

So then how should i make & execute this query? How could i match and then fetch 3 & 4 from record field?

 

Your database is bad designed... you should create a new table to create the relationships... for example if you have a table called "new" and another called "category".
new
----
idnew
title
content

category
--------
idcategory
name

If you want to create a "many to many" relationship you should create a table called "new_category" for example:

new_category
-------------
idnew
idcategory

but you should not make some thing like:

new
----
idnew
title
categories_ids

and save in categories_ids all categories ids...

i know my english is bad i hope you undertand my point...

 

Anyway if you are not interested in follow my method you can use your method, i think you can do something like:

SELECT * FROM table_name WHERE product_ids LIKE '% 3,%'

So include the space before and the "," after...

So if you have for example, 2, 12, 222, 432 and you are looking for the ID "2" SQL Will search for " 2,", if the space don't exists you can do something like:
SELECT * FROM table_name WHERE product_ids LIKE '%,3,%' (comma before and comma afther the ID)

Again excuse my english i hope you understand....

 

SELECT * FROM table_name WHERE FIND_IN_SET(3, product_ids) OR FIND_IN_SET(4, product_ids)

Code (markup):

 

Thanks for reply elixiusx
You English is perfect
Well i know database structure is not good, but i'll have to work on it :S
Because we've done too many work with this database structure.
Thanks for guide to restructure database.

Your queries are good. but it will not work on every condition.
Like if my record value is "3, 4, 5, 5" then we can't use this method '%,3,%'
If my record value is "1, 2, 3" still we can't use this method '%,3,%' or '% 3,%'

These are really logical queries or i think i'm beginner
Please think more about it

Thanks for your help.

 

Thanks for reply Gray Fox
Oh my GOD
You've done a really great job.
Now i'm going to give u star reputation
Thanks a lot again friend.
It really helped me out.

 

Thanks for reply Gray Fox
Oh my GOD
You've done a really great job.
Now i'm going to give u star reputation
Thanks a lot again friend.
It really helped me out.

 

what about '% 3,%' or '%, 3' or '3, %'?

 

Thanks for your reply elixiusx

'% 3,%' // it's perfect

'%, 3' // It means 3 or 31 or more

'3, %' // It means 3 or 13 or more

 

'% 3,%' // it's perfect

'%, 3' // It means 3 or 31 or more (Nope because i don't include a % at the end)

'3, %' // It means 3 or 13 or more (Nope because i don't include a % at the begin)

 

So i think this method are correct...

 

I've tried both of them, Not Working On Every Condition :S

 

Ah i think you are using only 1...

The query must include the 3... so the query will be something like

SELECT * FROM table_name WHERE product_ids LIKE '% 3,%' or product_ids LIKE '%, 3' or product_ids LIKE '3, %'
So you need to include the 3 in the same query

 

Record Values: 1, 2, 3, 4, 5

Query: SELECT * FROM ids WHERE pro_ids LIKE '%, 3'
Result: No Record Found

Query: SELECT * FROM ids WHERE pro_ids LIKE '3, %'
Result: No Record Found

 

Oh 3 Statements for just one match
I've more than 10 values and all those i'll have to find from record
10 Matches * 3 = 30

 

don't worry about it... are just some simple querys...