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 ?

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
– Stefan Pries, Commented Jun 30, 2016 at 7: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
– Ahmad Budi U, Commented Jun 30, 2016 at 7:40

Stefan Pries · Accepted Answer · 2016-06-30 07:24:17Z

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, 2016 at 7:24

Stefan Pries

2,0153 gold badges21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cb0 · Accepted Answer · 2016-06-30 07:29:30Z

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, 2016 at 7:29

answered Jun 30, 2016 at 7:22

cb0

8,6739 gold badges57 silver badges83 bronze badges

2 Comments

Ahmad Budi U Over a year ago

that's show the records. I want to show the name of column

cb0 Over a year ago

The first on using array_keys should only print the column names.

Jahid Mahmud · Accepted Answer · 2016-06-30 08:42:41Z

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, 2016 at 8:42

Jahid Mahmud

1,1361 gold badge12 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to show column names of multiple table in mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related