| SitePoint Sponsor |
Hi all,
I have a table, with a column having the data as comma(,) separated values...
Now i want to write a query to fetch the rows related to one of the comma(,) separated values...
Data in table COLUMN is;
1ST ROW----- A,B,C,D
2ND ROW----- V,S,R
3RD ROW----- C,V
4TH ROW----- A,D
NOW I WANT TO SHOW THE ROWS BASED ON THE VALUE 'C'
MEANS;
I WANT TO DISPLAY ONLY 1ST ROW AND 3RD ROW...
HOW TO WRITE A QUERY TO GET THE DATA....
THANKING YOU.....
It's possible, but you would be far better normalising the data. Is that an option?
@AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.
I tried some trails to get it.
But i failed to retrieve the rows based on the value presented in the column..
Help me to get it..
WHERE FIND_IN_SET('C',daColumn) > 0Originally Posted by venkat6134
but this is gonna get slower and slower as your table grows larger and larger
Thanks for your reply,
but is there any chance to use REGEXP or RLIKE.
or
any better way to retrive.(if it is having large table)
those will also be slow
the problem is your design, it breaks first normal form
the best thing you can do is redesign and normalize
At that time the table size is more na...
I have a table having the related cables under one group say 'A'. and another column having cable list....
and i have another table having connectors,based on connectors related cables will be shown....for that i inserted that cable group_id in the connectors table....
How to design the tables for better use...
please do a SHOW CREATE TABLE for your tables, so that we have something to discuss rather than just generalities...
CREATE TABLE IF NOT EXISTS `connector` (
`sno` int(5) NOT NULL,
`series` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`manufacturer` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`impedance` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`gender_polarity` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`orientation_mount` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`method` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`conn_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`cable_group` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`conn_image` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `cable_desc` (
`sno` int(5) NOT NULL,
`cable_name` varchar(30) DEFAULT NULL,
`cable_group` varchar(10) DEFAULT NULL,
`cable_desc` varchar(1000) DEFAULT NULL,
`type` varchar(30) DEFAULT NULL,
`impedance` varchar(20) DEFAULT NULL,
`frequency` varchar(20) DEFAULT NULL,
`cable_template` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
that looks a bit weird, because every column is VARCHAR, and you don't even have primary keys
okay, could you please tell us which column has the comma-delimited list?
In connector table, cable_group is having Group id's.
In cable_desc table, cable_name is having names of the cables under each cable_group id's.
In both tables, these two columns are having comma separated values..
wow, i am ~really~ lost
if cable_group in connectors table has multiple values, does cable_group in cable_desc table also have multiple values?
i think you should "go back to square one" and completely redesign both tables, without using comma-delimited values in any column anywhere
cable_group column in connectors table has multiple values like; A,B,C. these are group id's.
cable_name column in cable_desc table has multiple values like;
cable_name cable_group
145,174 A
185,205 B
219,306,.4lr C
i think you should normalize
how to do this?
I don't know much about database handling and normalization....
Give me some example regarding my problem with normalization...
Thanking you...
sorry, i cannot, because i do not know your data
you really should take some database tutorials
fortunately, there are literally hundreds of normalization tutorials on teh interwebs
Posting Permissions
Reply With Quote
Bookmarks