Hi, I'm working with a MySQL table that is back end for a Miva Merchant v5.x store. MySQL v5.x and PHP v5.2.x currently installed. CentOS 4.x for the server. I'm working in MySQL Query Browser ATM, basically I'm trying to convert rows to fields in one table, so I can then do a join with a second table, using a common key (product_id) to glue everything together. Then I'll convert the query to a proper PHP call to the db for dynamic, self-updating product displays based on product category fields contained in the 2nd table. I'm having a spot of difficulty with the multiple queries/sorts to the 1st table. Note that t1 has over 600 rows, with 3 entries for each product_id and the default sort is by field_id. t1 has this sort of layout: field_id product_id value 1 1 date1 1 2 date2 1 3 date3 ... ... ... 2 1 product_url1 2 2 product_url2 2 3 product_url3 ... ... ... 4 1 name1 4 2 name2 4 3 name3 ... ... ... Note that as `field_id` changes, `product_id` repeats and `value` gets a new string value. This is what I want: field_id product_id value1 value2 value3 1 1 date1 product_url1 name1 2 2 date2 product_url2 name2 3 3 date3 product_url3 name3 I do not need the field_id in the return but I have listed it here for clarity. I have tried: select a.product_id, a.`value` as `date` from t1.s01_CFM_ProdValues a where a.field_id = '1' union select b.product_id, b.`value` as `url` from t1.s01_CFM_ProdValues b where b.field_id = '2' union select c.product_id, c.`value` as `author` from t1.s01_CFM_ProdValues c where c.field_id = '4' order by product_id; That completes w/out error but doesn't work as desired. The rows are resorted and grouped/ordered together by product_id but each product still takes up three rows and the newly created `date` field contains 1 of the 3 values on each of the 3 rows, respectively. Google searches suggest using the sum() function: http://archives.devshed.com/forums/databases-139/transpose-rows-into-columns-1484979.html http://forums.mysql.com/read.php?86,23026,25752 and etc. but I haven't been able to get any of those examples to work for me, there's always an error reported on in vicinity of the sum() function. How do I get the product_id compressed to one line and the three `value` strings moved to value1, value2, and value3 fields? SL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
