Hi all,
I've been stumped with this one for a while now. What I have almost works, but not quite, and I thought I could ask here for any fresh ideas on how to do this...
The MySQL table in queston is layed out a bit strange (don't ask), and as such records aren't arranged in rows. Rather there's an itemid for each record and data is stored in a `data_txt` field in each row, with each row being a different field for the record.
For example, the following will get me all the rows for a particular record:
SELECT * FROM jos_sobi2_fields_data WHERE`itemid` = 107
while the following will just select the data field (data_txt) for the same record
SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `itemid` = 107
and the following gets me just the data (data_txt) for the same record, and only one field (field 42, here the "shop range" field)
SELECT `data_txt` FROM jos_sobi2_fields_data WHERE `fieldid` = 42 AND `itemid` = 107
I'm fine for the most part with this layout. However, this particular field (field 42) has data stored accross multiple rows. That is, for any given record (e.g. record 107), in the `data_txt` column, row 1 will have a string for product 1, row 2 will have a string for product 2, etc...
What I need to do is get all the data in these rows into a format that I can echo them as one string.
E.g. For record 107, take all the `data_txt` columns where the fieldid is 42, and output as a string.
Here is a link to a screenshot from MySQL workbench: [
dl.dropbox.com...] which should help explain things.
Hope this is in the right place - most of this post is about the MySQL layout of my table, but I need a php solution. I'll be happy to post the code I'm working on, but am hoping for a fresh approach!
Thanks :)
