[SOLVED] Finding Column Types

[hukadeeze]

I'm trying to populate an array with all the tables, columns, and column types from a database.

 

I know to get the table names I can use:

 

show tables from database

 

I can then use those results to loop over another query for column names:

 

show columns from $table

 

What I don't know is how to query for specific information about a column. I'm looking for column types and ranges. Such as a text type with a max of 40 chars. I would like to know not only the query string, but also the format in which it will be returned.To put this into context, I will be using this array in a form validation script. I will check that the incoming form variables match the format of the corresponding fields in the database.

 

 

[fenway]

You can't... at least not until 5.0, with the information_schema tables... you have to parse it yourself.

[hukadeeze]

Parse it yourself? I don't understand what you mean.

[fenway]

SHOW FULL COLUMNS has more detailed information.

[hukadeeze]

SHOW FULL COLUMNS was exactly what I was looking for. In case anybody is curious, if you run SHOW FULL COLUMNS on a table these will be your results.

 

If you use:

$row = $result->fetch_row();

 

Then:

 

$row[0] = Column Name

$row[1] = Column Type (Example: varchar(255))

$row[2] = Collation (Example: utf8_bin)

$row[3] = Accept Null Value? (Yes or No)

$row[4] = If column is primary key this value is PRI. If not then there is no value.

$row[5] = There was no value for this in any of the columns in the table I used.

$row[6] = Extras (Example: auto_increment)

$row[7] = Actions (select,insert,update,references)

 

Thanks for the help fenway!