advanced myslq query

[eluser]abada[/eluser]
hi all

table colors

--------------
id name
--------------
1 red
2 blue
3 green
4 black
5 yellow
--------------

table tshirts
------------------------
id name colors
------------------------
1 tshirt1 1,2
2 tshirt2 1,3,5
3 tshirt3 1,4,5
4 tshirt4 3,4,5
5 tshirt5 1,2,3,4,5
----------------------------

how can i get tshirts which have
case 1:
colors (1,3)
and i want return only tshirt2,tshirt5

case 2:
colors (1,2,3,4,5)
and i want return only tshirt5

case 3:
colors (4,5)
and i want return only tshirt3,tshirt4,tshirt5


thanks

[eluser]dudeami0[/eluser]
Havn't tested this query, but something like

case 1:

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?1[^0-9]?' AND `colors` REGEXP '[^0-9]?3[^0-9]?'

case 2

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?1[^0-9]?' AND `colors` REGEXP '[^0-9]?2[^0-9]?' AND `colors` REGEXP '[^0-9]?3[^0-9]?' AND `colors` REGEXP '[^0-9]?4[^0-9]?' AND `colors` REGEXP '[^0-9]?5[^0-9]?'

case 3

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?4[^0-9]?' AND `colors` REGEXP '[^0-9]?5[^0-9]?'

That is just repeating the AND operative alot. You can do just one REGEXP check like:

case 1:

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?1[^0-9]?.*[^0-9]?3[^0-9]?'

case 2:

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?1[^0-9]?.*[^0-9]?2[^0-9]?.*[^0-9]?3[^0-9]?.*[^0-9]?4[^0-9]?.*[^0-9]?5[^0-9]?'

case 3:

Code:

SELECT * FROM `tshirt` WHERE `colors` REGEXP '[^0-9]?4[^0-9]?.*[^0-9]?5[^0-9]?'

That should work

I didn't use LIKE because of the fact if you get into a 2+ digit number 20 could cause %2% to match it.

If you need help understanding how it works let me know. Also, there might be better solutions to this that I am not aware of.

EDIT: Updated the SQLs (Added the optional ? after the [^0-9]) and tested on my local MySQL server and it works fine!

[eluser]abada[/eluser]
thanks @dudeami0

for fast helping

i`ll try it

thank u

[eluser]abada[/eluser]
it work

many thanks

[eluser]tonanbarbarian[/eluser]
much easier all round if you store the colors of the shirts in a separate table rather than a comma separated list

[eluser]dudeami0[/eluser]
I agree with tonanbarbarian. Its also alot less stress placed on the MySQL server due to the regex command.

[eluser]abada[/eluser]
ya it`s easier but it take big data !!!

[eluser]tonanbarbarian[/eluser]
it might take "big data" but that is what a database is designed to do, process lots of data quickly and efficiently.

i garentee that if you desgin the database correctly and use a separate table as suggested you would see a major speed improvement once you have a real dataset to work with.
the regexp might seem ok when you run it with only a few records in the table, but once you have thousands of records the separate table with the colour selections will be vastly quicker

and it will be easier to change to doing it now than it will at some later date when you have thousands of records in the table