CODEWITHSUNDEEP
pythonmysqlmetadata
Henrov
Henrov

Reputation: 1610

Get list columnnames + datatypes from query

I have several (pretty standard) (MySQL) queries that look like this:

select *
from table_one a
left join table_two on a.id = b.id
left join table_three c on b.id = c.id.

table_one looks like this:

id (int)
label (varchar(15)

table_two looks like this:

id (int)
name (varchar(20)

table_three looks like this:

id (int)
some_field_name (varchar(25)

So the result would be this:

id|label|id|name|id|some_field_name|
..|.....|..|....|..|...............|

Now, what I would like to have is a list with the columnnames (and preferably also) columntypes that his query will produce

like this

id(int)
label (varchar(15)
id (int)
name varchar(20)
id (int)
some_field_name (varchar(25)

I know that I can use the INFORMATION_SCHEMA.COLUMNS table to get this info but then I still have to manually run this query for each table and 'compose' the list per query myself.

I would prefer a function that I can feed a query and that will then return the list of fieldnames + fieldtypes.

Upvotes: 0

Views: 71

Answers (2)

Raja vikraman
Raja vikraman

Reputation: 169

We have $stmt->getColumnMeta() in php to get metadata of column

<?php
$user = 'root';
$pass = 'infiniti';
$DB = 'test';
$host = 'localhost';

$c = new \PDO('mysql:host='.$host.';dbname='.$DB, $user, $pass);
$c->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$dataQuery = 'select * from info limit 3';

$stmt = $c->prepare($dataQuery);
$stmt->execute();
$rs = $stmt->fetchAll(\PDO::FETCH_ASSOC);

echo $stmt->columnCount();
for($i = 0; $i < $stmt->columnCount(); $i++)
{
    print_r($stmt->getColumnMeta($i));
}


output

Array
(
    [native_type] => LONG
    [pdo_type] => 2
    [flags] => Array
        (
            [0] => not_null
            [1] => primary_key
        )

    [table] => info
    [name] => id
    [len] => 11
    [precision] => 0
)
Array
(
    [native_type] => VAR_STRING
    [pdo_type] => 2
    [flags] => Array
        (
        )

    [table] => info
    [name] => fname
    [len] => 60
    [precision] => 0
)
Array
(
    [native_type] => VAR_STRING
    [pdo_type] => 2
    [flags] => Array
        (
        )

    [table] => info
    [name] => lname
    [len] => 60
    [precision] => 0
)

Upvotes: 1

Raja vikraman
Raja vikraman

Reputation: 169

I guess only the data of the column (not the data type and data length) will be returned to any scripting language.
if it takes long time to get datatype of col's used in queries, you could retrieve all col's datatype and store it in a variable, or in a CSV. 
This will work as long as you have few tables with minimum columns.

select 
COLUMN_NAME
DATA_TYPE
from
information_schema.columns c
where
c.TABLE_SCHEMA = 'DATABASE_NAME';

Upvotes: 0

Related Questions