Page 1 of 1

Need help retreiving data from a table

Posted: Fri Jun 08, 2007 6:12 am
by mzfp2
Hi, I have table with the following data :


ID ProdID ExValID Content
18311 4677 1 19°
18312 4677 1 22°
18313 4677 1 25°
18314 4677 1 28°
18316 4677 2 R/H
18317 4677 2 L/H
18319 4677 4 Regular
18320 4677 4 Stiff/Firm
18321 4677 4 Light/Senior

The application of my query is more concerned with the last column; Content. I need to create a query which returns every combination of of values based on the Content column for a given prodID, so in the example above it should return something similar to :

ProdID
4677 R/H 19° Regular
4677 R/H 22° Regular
4677 R/H 25° Regular
4677 R/H 28° Regular
4677 R/H 19° Stiff/Firm
4677 R/H 22° Stiff/Firm
4677 R/H 25° Stff/Firm
4677 L/H 19° Regular

... and so on


Is this possible to acheive using a mysql query?
I've tried several things, but more guessing than any educated attempts, so anyone who can help shed some light on how to solve this would be really appreciated

Thanks

Posted: Fri Jun 08, 2007 12:48 pm
by califdon
I'm not clear on whether you want to correct a terribly bad existing table structure in order to create a properly normalized table, or whether you don't realize that the existing structure is impossibly bad and think that you can somehow use it as a working database. If it's the former, I don't think you'll be able to do it with just SQL, but you can probably write a PHP script that cycles through each record and performs the necessary testing and logic to write out records to a new table. If it's the latter, you are expecting the impossible.

Posted: Fri Jun 08, 2007 1:01 pm
by mzfp2
Hi

Thanks for your reply, I do realise that table structure is awful, and not in a normalised fashion, however the database was created some time ago by my clients previous developers, and there is too much data to start making changes to the structure. I simply require the combination of values to generate a product feed.

I had a feeling i'll be needing to use php to generate these combinations, but thought i'd ask if there was q quicker and easier way to acheive this in SQL.

Thanks for your help

Posted: Thu Jun 14, 2007 7:04 pm
by califdon
I'm afraid you can't make a silk purse from a sow's ear. SQL isn't capable of recombining data like that. You will have to use SQL to retrieve records, probably store each row in an array in memory, then using PHP or Perl or Python to generate the combinations you want, or preferably, write everything to a new table, which you would then use for subsequent work. If there are too many scripts based on the existing structure, you will have a sizable job on your hands. Essentially you need to totally rewrite a bad database.