Re: Is this possible? G roup related results from mysql to one fi eld…

The only way that comes to my mind is using a SET column type - in related table you would not wave toid relating to categories, but instead it would be of type SET("demonstrations","people"). This column can be set to "" -empty, no category "demonstrations" "people" "demonstrations,people" But SET column is limited to 64 different members, still you can use more columns. Victor Spång Arthursson wrote:

Hi! Perhaps this is a little bit of topic, but if it's not possible to fix directly when the result is returning from mysql I need some tips on an intelligent solution... This is the problem: I'm searching the database for files and i get some results. In the sql I have a LIMIT to split the result up. Every file can have none, one or several categories associated with it. This is done by having an extra table between the table with the references to the files and the table with the categories. What I want to do is to get all categories associated with an file concatenated and returned in _one_ field together with the other data. Look below for visualisation. I'm having this main table with references to files in it: files +-----+-------------+ | id | filename | +-----+-------------+ |168 |v008-12.jpg| +-----+-------------+ Then I have this table to relate one file to one or several categories: relatedtable +-----+--------+------+

 |    id   |   fromid   |   toid   |

+-----+--------+------+

 |    4    |       1       |     2     |
 |  257  |       2       |     2     |

+-----+--------+------+ Which lies in this table for categories: categories +----+--------------+ | id | categoryname | stickword +----+--------------+

 |   5   |       demonstrations         |
 |   6   |        people           |

+----+--------------+ The following sql: SELECT DISTINCT

    files.id, files.filename, categories.categoryname

FROM

    files

LEFT JOIN

    relatedtable

ON

    files.id = relateratabell.fromid

LEFT JOIN

    kategorier

ON

    relatedtable.toid = categories.id

WHERE

    (relatedtable.fromid IS NULL OR relatedtable.fromid IS NOT NULL)

AND files.stickword LIKE '%basta%' //for example ORDER BY

    filename;

Gives the following result: +---+-----------+--------------------------+

 |  id  |     filename     |           categoryname                |

+---+-----------+--------------------------+

 | 166| v007-86.jpg | demonstrations                      |
 | 166| v007-86.jpg | people                       |
 | 167| v008-03.jpg | demonstrations                      |
 | 167| v008-03.jpg | people                      |

+---+-----------+--------------------------+ This is what I expected it to, but I'ld rather get a result as this one: +---+-----------+--------------------------+

 |  id  |     filename     |           categoryname                |

+---+-----------+--------------------------+

 | 166| v007-86.jpg | demonstrations, people    |
 | 167| v008-03.jpg | demonstrations, people    |

+---+-----------+--------------------------+ Where the categories have been collected together into the same field, so that I don't get multiple rows for the same file… Is this possible to achieve? Many many thanks to the one who can give me some input! Sincerely Victor