0

How to join many fields with different ID's in only one? I have this MySQL table:

--------------------------------
| *UDH*        |     *Text*     |
--------------------------------
| 050003B90301 | Hi my name is A|
--------------------------------
| 050003B90302 | rmin and I wan |
--------------------------------
| 050003B90303 | t be your frien |
--------------------------------

The UDH field is different but I need join the text field to copy to other table, the result must to be like this:

______________________________________________________________
| UDH          |     Text                                     |
--------------------------------------------------------------
| 1            | Hi my name is Armin and I want be your frien |
---------------------------------------------------------------

Do you know a PHP sentence or other method to make something like this?

Synapsido
  • 140
  • 3
  • 12

1 Answers1

0

Use GROUP_CONCAT(). Like this:

select '1' as UDH, group_concat(`Text` separator '') as `Text` from myTable;

If you need to order by the UDH column, you can do that within the group_concat() call like this:

select '1' as UDH, group_concat(`Text` order by UDH separator '') as `Text` from myTable;

I just validated this query with the following test code based on your example:

mysql> create table myTable (UDH varchar(32), `Text` text) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into myTable (UDH, `Text`) values ('050003B90301', 'Hi my name is A'), ('050003B90302', 'rmin and I wan'), ('050003B90303', 't be your frien');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from myTable;
+--------------+-----------------+
| UDH          | Text            |
+--------------+-----------------+
| 050003B90301 | Hi my name is A |
| 050003B90302 | rmin and I wan  |
| 050003B90303 | t be your frien |
+--------------+-----------------+
3 rows in set (0.00 sec)

mysql> select '1' as UDH, group_concat(`Text` order by UDH separator '') as `Text` from myTable;
+-----+----------------------------------------------+
| UDH | Text                                         |
+-----+----------------------------------------------+
| 1   | Hi my name is Armin and I want be your frien |
+-----+----------------------------------------------+
1 row in set (0.01 sec)
Asaph
  • 159,146
  • 25
  • 197
  • 199
  • You're missing a ORDER BY argument – Strawberry Dec 21 '16 at 00:31
  • Many thanks, this works, and now, how to limit to 2 or 3 fields? – Synapsido Dec 21 '16 at 14:02
  • You're welcome. If this answer resolved your problem as stated in your question, please mark it correct by clicking the checkbox to the left of the answer. As for your follow-up question, see http://stackoverflow.com/questions/1522509/how-to-hack-mysql-group-concat-to-fetch-a-limited-number-of-rows – Asaph Dec 21 '16 at 17:52
  • Thank you @Asaph, but I forget write, there are empty UDH fields in table, so if the UDH field = 0 is not necessary concatenate the data on the *Text* field... Do you know how I do that? – Synapsido Dec 21 '16 at 21:04
  • You can simply filter out those records with a WHERE clause appended to the end of the query. Please mark this answer correct if it solves your problem. – Asaph Dec 21 '16 at 21:08