Page 1 of 1
Return a list as a field in MySQL
Posted: Sat Apr 01, 2006 1:59 pm
by Todd_Z
I have two tables: galleries and images
I would like to form a query that given a gallery id, it would return the gallery title and a list of the image extensions in the gallery, separated by commas.
Is there a function that I can use on a query such as:
Code: Select all
SELECT DISTINCT(extension) FROM images WHERE gallery = XX;
that would return a string like "jpg, bmp, gif"?
Posted: Sat Apr 01, 2006 2:04 pm
by John Cartwright
I can't really wrap my head around this.. but perhaps this might bump you in the right direction
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
Code: Select all
mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
Posted: Sat Apr 01, 2006 2:09 pm
by Todd_Z
Yea, I hoped that that would have done it - tried it already.
The problem is that I'm trying to concat across rows on a table, not fields in a row.
Posted: Sat Apr 01, 2006 6:09 pm
by feyd
not possible without a subquery. You could just make them a comma separated list in PHP though with little problem.
Posted: Sat Apr 01, 2006 8:46 pm
by Todd_Z
i cant do it with a subquery either

Any ideas?
I'm trying to use a sort in the mysql query though, so thats why i want to do it through mysql instead of php
Posted: Sat Apr 01, 2006 8:53 pm
by feyd
why would sorting not allow you to do it in PHP?
Posted: Sat Apr 01, 2006 8:59 pm
by Todd_Z
The MySQL Pagination class that I wrote -> it's gonna be some ugly exception scripting if I need to sort by php.
Posted: Sat Apr 01, 2006 9:11 pm
by feyd
I didn't say anything about sorting it in PHP, nor did you mention anything about pagination.
Why would one need pagination for a list of image extensions in a gallery script?
Posted: Sat Apr 01, 2006 10:18 pm
by Todd_Z
I think you missed the point entirely.
I'm trying to use a mysql pagination script to cycle through the galleries, when I show the list of galleries, i want to include a list of the all the distinct extensions used in the gallery. The way that the pagination script works is that I pass a table field to sort by, but if i want to sort by the extensions, then i would need a subquery to get the list.
Posted: Sat Apr 01, 2006 10:29 pm
by feyd
Todd_Z wrote:I think you missed the point entirely.
Considering you've only talked about wanting MySQL to give you a comma separated string of a search in the thread so far, yeah I did miss that bit.
Todd_Z wrote:I'm trying to use a mysql pagination script to cycle through the galleries, when I show the list of galleries, i want to include a list of the all the distinct extensions used in the gallery. The way that the pagination script works is that I pass a table field to sort by, but if i want to sort by the extensions, then i would need a subquery to get the list.
Sounds like you need to refactor/reorganize the class to work with more liquid data.