[TYPO3-dev] Order result of query based on comma-seperated string

[Oliver Hader]

Hi Sacha,

Sacha Vorbeck schrieb:
> I have created a little extension with the two tables "authors" and
> "books". The author table contains a db-relation field "bookIDs" so that
> an editor can connect the author to several books.
> 
> I use the following query to read the book records belonging to an
> author. Beforehand the var $bookIDs was filled with  the comma-seperated
> string of book-uids entered by the author in the field "bookIDs".
> 
> $bookIDs = $row['bookIDs'];
> $query = $GLOBALS['TYPO3_DB']->SELECTquery(
>     'uid,title',
>     'tx_myext_books',
>     ' uid IN (' . $bookIDs . ')'.$this->enableFields,
>     '',
>     '',
>     ''
> );
> $res = $GLOBALS['TYPO3_DB']->sql(TYPO3_db, $query);
> 
> This works and delivers the selected books but it doesn`t take the sort
> order into account. So when the editor opens a record of the author
> table in the backend and changes the sort order of the values in the
> field bookIDs to:
> 
> 3,1,4,2
> 
> The result array of the query will still be: 1,2,3,4

The 5th parameter of SELECT_query() can be set to the sort column of the
table. But I guess you'd like to have a very specific sorting as
provided in the string $bookIDs ("3,1,4,2")? Therefore you can use the
"ORDER BY FIELD()" in MySQL - however this query is not very efficient
compared to database normalization...

Example:
$query = $GLOBALS['TYPO3_DB']->SELECTquery(
     'uid,title',
     'tx_myext_books',
     'uid IN (' . $bookIDs . ')' . $this->enableFields,
     '',
     'FIELD(uid, ' . $bookIDs . ')',
     ''
);

olly
-- 
Oliver Hader
TYPO3 4.3 Release Manager