Sql select including columntype

Question

I want, for my form in php, a result including columntype. Is there a way to make a query and including the column type?

select * from TableA 
+
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName'

possible duplicate of [SQL statement to get column type](http://stackoverflow.com/questions/13405572/sql-statement-to-get-column-type) — Orel Eraki, Jun 28 '14 at 12:45
I want to combine if possible. So select * from TableA + SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' — Bas, Jun 28 '14 at 12:53
A. You just changed your question. B. Still.. the duplicate post can answer it with slight modification. — Orel Eraki, Jun 28 '14 at 12:55
I wondered if this was possible. Thanks @sndesign! Thats a good solution! — Bas, Jun 28 '14 at 13:00
@Bas - The proper way to add a solution in Stack Overflow is the "Post Your Answer" button. — Álvaro González, Jun 28 '14 at 13:31
@Álvaro G. VicarioI see only the answer your question button — Bas, Jun 28 '14 at 14:01

Charlie · Answer 1 · 2014-06-28T13:00:59.640

There's probably no way to select both data and column type because the column type is typically stored in a separate table (this would depend on the database). For example, in MySql:

Query to column type: SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'

You would then to need to parse the results of the 2 queries and join them using php (I don't think there's an easy way to do this using SQL because you would be joining a 1xN results with a Nx1 result).

More info about where the column type is stored: http://dev.mysql.com/doc/refman/5.7/en/information-schema.html

score 0 · Answer 2 · answered Jun 28 '14 at 13:28

Those are inherently different data sets so I bet you'd need to write a possibly rather messy stored routine.

You don't explain why you need it but I suspect you'll be fine with the column types of the result set (rather than the source tables). That will include exactly the same information plus the type of calculated columns.

The exact mechanism depends on your DBMS and database library. You don't give any clue about that but your sample code looks like MySQL so the options include:

If you want to stick to your original spec, at least save yourself some trouble and just run two queries.

score 0 · Accepted Answer · answered Jun 28 '14 at 14:13

0

I found this. So now i can combine the array!

    $i = 0; 
    while ($column = $query->getColumnMeta($i++)) { 
        print_r($column); 
    }

answered Jun 28 '14 at 14:13

Bas

137
11

Sql select including columntype

3 Answers3