+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: advanced myslq query (/showthread.php?tid=35588)
advanced myslq query - El Forum - 11-04-2010
[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
advanced myslq query - El Forum - 11-04-2010
[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]?'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]?'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]?'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]?'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!
advanced myslq query - El Forum - 11-04-2010
[eluser]abada[/eluser]
thanks @dudeami0
for fast helping
i`ll try it
thank u
advanced myslq query - El Forum - 11-04-2010
[eluser]abada[/eluser]
it work
many thanks
advanced myslq query - El Forum - 11-04-2010
[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
advanced myslq query - El Forum - 11-04-2010
[eluser]dudeami0[/eluser]
I agree with tonanbarbarian. Its also alot less stress placed on the MySQL server due to the regex command.
advanced myslq query - El Forum - 11-07-2010
[eluser]abada[/eluser]
ya it`s easier but it take big data !!!
advanced myslq query - El Forum - 11-07-2010
[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