You can run an update query and change the | to , to use FIND_IN_SET, or write a simple script that separates everything.
Do you have access to the script that's putting IN the data? ---John Holmes... > -----Original Message----- > From: Miguel Cruz [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 23, 2002 3:06 PM > To: [EMAIL PROTECTED] > Subject: Re: [PHP] php/mysql join query help > > The sooner you fix it, the less work you have to do down the road! > > miguel > > On Thu, 23 May 2002, ROBERT MCPEAK wrote: > > > I appreciate you help. I inherited the pipe-delimited data. I would > > have done it as you suggested. > > > > Thanks. > > > > -Bob > > > > >>> "1LT John W. Holmes" <[EMAIL PROTECTED]> 05/23/02 01:57PM > > >>> > > It's generally a bad idea to store delimited data in a single column in > > a > > database. It kind of goes against what a database is there for. A > > better > > layout would be to have your second table contain a row for each of > > the > > "delimited" values. > > > > So if you have '1,2,3,4' in your database now, the better way would be > > to > > have four rows, 1 through 4 in the table instead, with whatever other > > info > > you keep for each row. Then a simple SELECT var, COUNT(var) FROM table > > will > > give you the answers you're looking for. > > > > but...if you want to leave it the way it is, then you can use some kind > > of > > matching to find the rows using LIKE. I couldn't get a query to work, > > though. If your data can be seperated by commas, an easy way to do it > > is > > with something like this > > > > SELECT table1.key, COUNT(*) FROM table1, table2 WHERE > > FIND_IN_SET(table1.key,table2.keys) GROUP BY table1.key > > > > But, the flaw of that is that it'll only count one occurance of > > table1.key > > per row in table2. If a row has 'blue' twice, only one will be > > counted. > > > > ---John Holmes... > > > > ----- Original Message ----- > > From: "ROBERT MCPEAK" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, May 23, 2002 1:22 PM > > Subject: [PHP] php/mysql join query help > > > > > > > I have a table with a field "key" containing a unique value. I have > > a > > > second table containing a field called "keys" containg a > > pipe-delimited > > > list of values from the "key" table. > > > > > > I'd like to do a query that took each value from key, and matched it > > > agains the field "keys" in the second table, and returned a third > > table > > > of unique key values from the first table, and number of matches > > from > > > the second table. > > > > > > I've already set up a full text index for the values in the second > > > table and have done successful matches in simpler queries. > > > > > > Make sense? > > > > > > The table I'd like to generate might look like this: > > > > > > key matches > > > blue 50 > > > yellow 6 > > > green 29 > > > > > > This would indicate that there were 3 key values in the first table > > > (blue, yellow, green), and, respectively, there were 50, 6, and 20 > > > matches for each term in the second table. > > > > > > Can somebody help me do this? > > > > > > Thanks! > > > > > > -Bob > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
