"" -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
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
