Return a list as a field in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Return a list as a field in MySQL

Post 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"?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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'
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

not possible without a subquery. You could just make them a comma separated list in PHP though with little problem.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

i cant do it with a subquery either :cry: 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why would sorting not allow you to do it in PHP?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply