RE: [PHP] php/mysql join query help
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
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
Re: [PHP] php/mysql join query help
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
Re: [PHP] php/mysql join query help
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
