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'
select b.product_id, b.`value` as `url`
from t1.s01_CFM_ProdValues b
where b.field_id = '2'
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://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?


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to