The sooner you fix it, the less work you have to do down the road!
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.
> >>> "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
> database. It kind of goes against what a database is there for. A
> layout would be to have your second table contain a row for each of
> "delimited" values.
> So if you have '1,2,3,4' in your database now, the better way would be
> have four rows, 1 through 4 in the table instead, with whatever other
> you keep for each row. Then a simple SELECT var, COUNT(var) FROM table
> 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
> 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
> 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
> per row in table2. If a row has 'blue' twice, only one will be
> ---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
> > second table containing a field called "keys" containg a
> > 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
> > of unique key values from the first table, and number of matches
> > 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