One table, multiple queries?

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-r ows-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

Re: One table, multiple queries?

It sounds like you are wanting to "transpose" rows and columns. But
you used the terms convert and fields, so google may not be helping
you.

I found this:

http://forums.mysql.com/read.php?86,23026,25752#msg-25752

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

Re: One table, multiple queries?

--001485f423241b7ffb046a866adb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Fri, May 22, 2009 at 4:24 PM, Dee Ayy wrote:

> It sounds like you are wanting to "transpose" rows and columns. But
> you used the terms convert and fields, so google may not be helping
> you.
>
> I found this:
>
> http://forums.mysql.com/read.php?86,23026,25752#msg-25752
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

cross tab query?
--

Bastien

Cat, the other other white meat

--001485f423241b7ffb046a866adb--

Re: One table, multiple queries?

Bastien Koert wrote:
> On Fri, May 22, 2009 at 4:24 PM, Dee Ayy wrote:
>
>> It sounds like you are wanting to "transpose" rows and columns. But
>> you used the terms convert and fields, so google may not be helping
>> you.
>>
>> I found this:
>>
>> http://forums.mysql.com/read.php?86,23026,25752#msg-25752

I had seen that, I linked it in fact. I don't want to sum the data,
just list it. Transpose is a more correct word I suppose. The issue
is that the data in the value field is different, depending on the
value in the field_id field. Therefore, I want to take each of the
three data types and convert them to new fields, or columns, so that
all of the data for one product_id is on one row, instead of three.


>
> cross tab query?

Huh? Whuzzat? I'm off to RTFM, and ty for the pointer.

Ski

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