Select concat(*)

Discussion in 'Databases' started by flamy, Oct 25, 2009.

  1. #1
    Specifying individual fields to concatenate in a select statement works fine:

    SELECT CONCAT(field1,field2) as single FROM tbl_name

    How can I do this to concatenate all fields? ie SELECT CONCAT(*) as single FROM tbl_name?
     
    Last edited: Oct 25, 2009
    flamy, Oct 25, 2009 IP
  2. flamy

    flamy Peon

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I can't specify each and every field in the statement because I am using this statement for various tables.

    Edit: This doesn't work but I'd have thought something along these lines would work:

    SELECT concat(SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'tbl_name')
    as single FROM tbl_name

    ??
     
    Last edited: Oct 25, 2009
    flamy, Oct 25, 2009 IP
  3. heavydev

    heavydev Peon

    Messages:
    33
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    i think you would have to do this in two queries:

    $column_list = mysql_fetch_assoc(mysql_query("select group_concat(column_name) as columns from information_schema.columns where table_name='tbl_name'"));
    $concat_query = mysql_query("select concat({$column_list['columns']}) as single from tbl_name");

    or something similar via php
     
    heavydev, Oct 25, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    SHOW COLUMNS FROM `my_table`;

    or...

    DESCRIBE `my_table`;
     
    jestep, Oct 25, 2009 IP
  5. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #5
    heavydev's method is appropriate and can be used for this purpose. Just make sure to also add table_schema as well otherwise if same table is available in more than 1 database, you will get list of columns from all the tables :)

    modified query will be something like this...

    
    $column_list = mysql_fetch_assoc(mysql_query("select group_concat(column_name) as columns from information_schema.columns where table_schema='DB_NAME' table_name='tbl_name'"));
    
    PHP:
     
    mastermunj, Oct 26, 2009 IP