MySql Query result only first column-CodePudding

My query that i use is

SELECT item1, item2, item3, item4 FROM table  WHERE id ='99'

should result 1 2 3 4 5 6 7 8, but i got only 1 , can i get them all in 1 column like Items ?

 ------- -------- ---------- --------- --------- 
| id    | item1  |   item2  |  item3  |  item4  |   
 ------- -------- ---------- --------- --------- 
|  99   |    1   |     2    |    3    |    4    | 
|  99   |    5   |     6    |    7    |    8    | 
|  92   |    1   |     2    |    3    |    4    | 
|  92   |    1   |     2    |    3    |    4    | 
 ------- -------- ---------- --------- ---------

Function

function getInv($id)
    {
        global $database;
        $stmt = $dbh->user("SELECT CONCAT(item1,', ',item2,', ',item3,', ',item4) Items
        FROM table  WHERE id= ? ORDER BY Items");
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        if($result)
        return convert_number($result[0]);
        else {
        return '---';
    }

View Page

<?php print getInv($user['id']); ?>

CodePudding user response：

To produce your expected result you could use:

select group_concat(CONCAT_WS(' ',item1, item2, item3, item4) SEPARATOR ' ')  as my_column
FROM my_table  WHERE id =99
group by id;

Result:

my_column
1 2 3 4 5 6 7 8

Demo

CodePudding user response：

You could use GROUP_CONCAT and CONCAT to get theresult, but it would be better to have some kind of order for the line, so that the result is more deterministc

CREATE TABLE item (
  `id` INTEGER,
  `item1` INTEGER,
  `item2` INTEGER,
  `item3` INTEGER,
  `item4` INTEGER
);

INSERT INTO item
  (`id`, `item1`, `item2`, `item3`, `item4`)
VALUES
  ('99', '1', '2', '3', '4'),
  ('99', '5', '6', '7', '8'),
  ('92', '1', '2', '3', '4'),
  ('92', '1', '2', '3', '4');

SELECT GROUP_CONCAT(CONCAT(`item1`, ',', `item2`,',',  `item3`,',' , `item4` ) ) AS intes FROM item WHERE `id` = 99

| intes           |
| :-------------- |
| 1,2,3,4,5,6,7,8 |

db<>fiddle here

CodePudding user response：

You can use:

select group_concat(item1 ",",",",item2,",",item3,",",item4) as result from my_table where (id="99") order by id

|result          |
|1,2,3,4,5,6,7,8 |