Re: 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" <holmes072000@charter.net> 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" <RMCPEAK@jhuccp.org> > To: <php-general@lists.php.net> > 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 > > > > >