How to show column names of multiple table in mysql

Question

I have 2 tables person and country. Table person has 3 columns id_person, name_person and id_country. country has 2 columns id_country and name_country.

I want to show "name_person" and "name_country", just the name of column, not record. How can I get this ?

Please have a look at [this](http://stackoverflow.com/a/7091984/6527256) answer. — JK12321, Jun 30 '16 at 07:24
It would be helpful to understand what you're trying to achieve. Why do you want to get the column names, if you already know them? — Stefan Pries, Jun 30 '16 at 07:30
i want to get the name of column to create a string from it. then merge it with the record of table. Finally I have only one string. sorry for my bad english sir :) — Ahmad Budi U, Jun 30 '16 at 07:40
Please read [How does accepting an answer work?](http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work) — Quentin, Jun 30 '16 at 10:21

score 5 · Accepted Answer · answered Jun 30 '16 at 07:24

5

You can access table columns in MySQL's information schema database:

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME` IN ('country', 'person')
    AND `COLUMN_NAME` LIKE 'name_%';

answered Jun 30 '16 at 07:24

Stefan Pries

1,886
2
20
27

cb0 · Answer 2 · 2016-06-30T07:29:30.697

2

This should do it:

$result = $db->query('SELECT p.name_person, c.name_country FROM person AS p LEFT JOIN country as c ON (p.id_country=c.id_country)');
$fields = array_keys($result->fetch(PDO::FETCH_ASSOC));

If you want to get the data an not only the columns names, just remove the array_keys.

$fields = $result->fetch(PDO::FETCH_ASSOC);

edited Jun 30 '16 at 07:29

answered Jun 30 '16 at 07:22

cb0

8,415
9
52
80

that's show the records. I want to show the name of column – Ahmad Budi U Jun 30 '16 at 07:29
The first on using `array_keys` should only print the column names. – cb0 Jun 30 '16 at 07:38

score 0 · Answer 3 · answered Jun 30 '16 at 08:42

0

You can use

    $result = $db->query('SELECT person.name_person, country.name_country FROM person LEFT JOIN country ON (person.id_country=country.id_country)');

    print_r(array_keys($result));

answered Jun 30 '16 at 08:42

Jahid Mahmud

1,136
1
12
32

How to show column names of multiple table in mysql

3 Answers3