MySQL CONCAT Syntax

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
kwong
Forum Newbie
Posts: 10
Joined: Thu Mar 15, 2007 1:17 am

MySQL CONCAT Syntax

Post by kwong »

Hello everyone,

I need help in constructing mysql query syntax.
Suppose I have a table with auto increment id as primary key.

What is the syntax to query all id and result in 1,2,3,4,5,6,7,8,9,...

I tried to use CONCAT_WS(",", id) but it doesn't do.

Any suggestion?

thanks for any answer.

:P
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: MySQL CONCAT Syntax

Post by onion2k »

Do you mean you want to get all the records where the id is in 1,2,3,4,5,6,7,8,9 like..

Code: Select all

SELECT `table`.`id`
FROM `table`
WHERE 1 
AND `id` IN (1,2,3,4,5,6,7,8,9)
Or do you mean you want the result to be a comma separated list of all the ids in the database table?
kwong
Forum Newbie
Posts: 10
Joined: Thu Mar 15, 2007 1:17 am

Re: MySQL CONCAT Syntax

Post by kwong »

I mean the latter, I'd like to get all id in a table concatenated by ",".
Is that possible?

thanks for your answer :D
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: MySQL CONCAT Syntax

Post by onion2k »

Use group_concat.. You'll need to be using MySQL 4.1 or above:

Code: Select all

SELECT GROUP_CONCAT(`id` SEPARATOR ',') AS ids 
FROM `table`
What it returns depends on how much data there is.. it's either a BLOB or a VARCHAR. And note that it's limited by a couple of MySQL's internal settings... http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat
kwong
Forum Newbie
Posts: 10
Joined: Thu Mar 15, 2007 1:17 am

Re: MySQL CONCAT Syntax

Post by kwong »

Thanks heaps onion2k,
I know there must be a syntax, Thank for pointing me out.

Cheers

Kal
Post Reply