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 Spng 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
