sql table comment

[v3nt]

so i'm trying to automatically create forms from database tables an its going ok but i can't get hold of the comments i've made for each field which i want to use as the fields' explanation text.

the table exports like this;
CREATE TABLE episodes (
episodeId int(11) NOT NULL auto_increment COMMENT 'main id number',
episodeTitle varchar(200) NOT NULL default '' COMMENT 'the title of the series',
episodeInfo text NOT NULL COMMENT 'episode info',
seriesRef int(4) NOT NULL default '0' COMMENT 'series reference',
episodeNo int(4) NOT NULL default '0' COMMENT 'episode number',
timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'last updated',
PRIMARY KEY (episodeId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

and i'm after the COMMENT 'main id number' part of each field too.

and when i use;

$result = mysql_list_tables($db);

if (!$result) {
 echo "DB Error, could not list tables";
 echo 'MySQL Error: ' . mysql_error();
 exit; 
}

 while ($row = mysql_fetch_row($result)) {
 echo "<BR>Table: $row[0] <br>";

 $resultb = mysql_query("SHOW COLUMNS FROM $row[0]");
if(!$resultb) {
	 echo 'Could not run query: ' . mysql_error(); 
	 exit;
 } 
 if (mysql_num_rows($resultb) > 0) {
	 while ($row = mysql_fetch_assoc($resultb)) {
		 print_r($row);
		  echo "<BR>";
	 }
 }


 }

i get all the info except the comment. see here;
http://78.136.37.130/flash/tables.php

Any idea how i might get hold of the comment?

many thanks, D.

[rsmith]

I'm not sure if you can pull the comment. I think the comment is just that, a comment -- to give the person looking at the table an idea of what the column holds. I could be wrong but I've never had it work.

[v3nt]

i think not to. what a pain in the ass.

but how come it appears in the sql dump? Must be usable if it gets exported no?

[rsmith]

You'd think. I think it's a reserved attribute though for informational purposes only. What I would do is create a column called "comments" or "info" where you would store a text string comment instead of using the COMMENT function in SQL. Just a thought if there's no other workaround.

[v3nt]

thanks for your input rsmith.

do you know of any decent sql forums? no worries if not 'n' all the best...

[Joseph_Sliker]

Sorry for the long delay ;-)

I was digging around trying to figure out this very thing and I found this in the MySQL documentation:

"show full columns from tbl_name";

So, instead of simply "show columns...you have to add the word "full" after show...and viola! the comments field is included.

This could come in very handy! (ie. you could put in the code for the type of input control you'd use for each field to facilitate auto-generation of forms, maybe)

[v3nt]

wow - that will come in handy!

will check it out sometime this week.

cheers Joseph.

D.