nadeemshafi9 Posted August 28, 2006 Share Posted August 28, 2006 hi againI just need to know how to get a mysql feild type eg varchar, image, date, text etc. how to pull it out of a feild using SQL or some way in PHP.thanks again Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 28, 2006 Share Posted August 28, 2006 Use the "SHOW COLUMNS FROM tablename" MySQL query, then loop through the result and look at the Type column.http://dev.mysql.com/doc/refman/5.0/en/show-columns.html Quote Link to comment Share on other sites More sharing options...
redarrow Posted August 28, 2006 Share Posted August 28, 2006 install phpmyadmin ok Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 29, 2006 Author Share Posted August 29, 2006 here is the show colums syntaxSHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']ok i can pull out the colums names using this by taking the info out of the result in to an arrayi dont understand how i can pull the type out is the result multi dimensional if so please can somone give me an examplei just need the type of the column so my software can check if its an image or audio feild ect and compensate. Quote Link to comment Share on other sites More sharing options...
.josh Posted August 29, 2006 Share Posted August 29, 2006 [code]<?php $sql = "show columns from test"; $result = mysql_query($sql); // formatted for your pleasure echo "<table>"; while ($list = mysql_fetch_row($result)) { echo "<tr><td align = 'right'><b>name:</b></td>"; echo "<td align = 'left'>$list[0]</td>"; // the 0 position in the array is the name echo "<td align = 'right'><b>type:</b>"; echo "<td align = 'left'>$list[1]</td></tr>"; // the 1 position in the array is the type } echo "</table>";?>[/code]... though, if you are basing a filetype on the column's type.. that doesn't make a whole lot of sense. that opens up lots of potential bugs. Quote Link to comment Share on other sites More sharing options...
Jenk Posted August 29, 2006 Share Posted August 29, 2006 http://us2.php.net/manual/en/function.mysql-field-type.php Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 29, 2006 Author Share Posted August 29, 2006 the reason i needed the type was to see if the feild is blob etc so i can compensate on the input form that is generated by my software. Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 29, 2006 Author Share Posted August 29, 2006 Thanx alot RESOLVED Quote Link to comment Share on other sites More sharing options...
.josh Posted August 29, 2006 Share Posted August 29, 2006 jenk- just so you know, mysql_field_type() returns what php thinks the column type is, based on the data retrieved from the column. This does not necessarily make it the same data type as what you actually have in your database. Your column type could be something else and php could get it wrong. therefore you should do it my way, as you are getting the datatype directly from sql, not php. Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 31, 2006 Author Share Posted August 31, 2006 yeh because im trying to make a peice of software that makes the website so i dont wana get it wrong because the database is what the software bases the generated site on Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 31, 2006 Author Share Posted August 31, 2006 thnx has gone a long way since last week i got it in a object know and am building it Quote Link to comment Share on other sites More sharing options...
nadeemshafi9 Posted August 31, 2006 Author Share Posted August 31, 2006 a class that is Quote Link to comment Share on other sites More sharing options...
Jenk Posted August 31, 2006 Share Posted August 31, 2006 [quote author=Crayon Violent link=topic=105876.msg424127#msg424127 date=1156871329]jenk- just so you know, mysql_field_type() returns what php thinks the column type is, based on the data retrieved from the column. This does not necessarily make it the same data type as what you actually have in your database. Your column type could be something else and php could get it wrong. therefore you should do it my way, as you are getting the datatype directly from sql, not php. [/quote]Actually, no it doesn't. It is a direct shortcut to the C function which is used within MySQL. It returns whatever value is in the TYPE column when you run a DESC `table` statement. Quote Link to comment Share on other sites More sharing options...
.josh Posted August 31, 2006 Share Posted August 31, 2006 really? well then maybe you should add a note to the manual then, correcting the other notes. http://us3.php.net/mysql_field_type Quote Link to comment Share on other sites More sharing options...
Jenk Posted August 31, 2006 Share Posted August 31, 2006 I've no need to. The information is readily available because others have posted it ;) Quote Link to comment Share on other sites More sharing options...
.josh Posted August 31, 2006 Share Posted August 31, 2006 where? i'm not being snide. i just don't see it.. Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 31, 2006 Share Posted August 31, 2006 It is, without a doubt, at best - a guess by php using the mysql_field_type function. MySQL has several data types...like int, tinyint, bigint, and so forth...that mysql_field_type will generalize into one...in the listed examples, they area all listed as "int".Here is the exact C code from the php source code:[code]switch(field_type) { case FIELD_TYPE_STRING: case FIELD_TYPE_VAR_STRING: return "string"; break;#ifdef MYSQL_HAS_TINY case FIELD_TYPE_TINY:#endif case FIELD_TYPE_SHORT: case FIELD_TYPE_LONG: case FIELD_TYPE_LONGLONG: case FIELD_TYPE_INT24: return "int"; break; case FIELD_TYPE_FLOAT: case FIELD_TYPE_DOUBLE: case FIELD_TYPE_DECIMAL:#ifdef FIELD_TYPE_NEWDECIMAL case FIELD_TYPE_NEWDECIMAL:#endif return "real"; break; case FIELD_TYPE_TIMESTAMP: return "timestamp"; break;#ifdef MYSQL_HAS_YEAR case FIELD_TYPE_YEAR: return "year"; break;#endif case FIELD_TYPE_DATE:#ifdef FIELD_TYPE_NEWDATE case FIELD_TYPE_NEWDATE:#endif return "date"; break; case FIELD_TYPE_TIME: return "time"; break; case FIELD_TYPE_SET: return "set"; break; case FIELD_TYPE_ENUM: return "enum"; break;#ifdef FIELD_TYPE_GEOMETRY case FIELD_TYPE_GEOMETRY: return "geometry"; break;#endif case FIELD_TYPE_DATETIME: return "datetime"; break; case FIELD_TYPE_TINY_BLOB: case FIELD_TYPE_MEDIUM_BLOB: case FIELD_TYPE_LONG_BLOB: case FIELD_TYPE_BLOB: return "blob"; break; case FIELD_TYPE_NULL: return "null"; break; default: return "unknown"; break;[/code]As you can see, it is a guess. The only way to get a 100% accurate answer as to what type of field it is is to use "SHOW COLUMNS FROM tablename" and see what the "type" column in the result contains.If you still don't beleive me, use this SQL:[code]CREATE TABLE `proof` ( `a` varchar(255) default NULL, `b` tinyint(4) default '0', `c` text, `d` date default NULL, `e` smallint(6) default '0', `f` mediumint(9) default '0', `g` int(11) default '0', `h` bigint(20) default '0', `i` float default '0', `j` double default '0', `k` decimal(10,0) default '0', `l` datetime default NULL, `m` timestamp NULL default NULL, `n` time default NULL, `o` year(4) default NULL, `p` char(255) default NULL, `q` tinyblob, `r` tinytext, `s` blob, `t` mediumblob) ENGINE=InnoDB DEFAULT CHARSET=latin1;[/code]Once you have that table in your db, execute this php code:[code]$conn = mysql_connect("hostname", "username", "password");mysql_select_db("dbname");$query = "SELECT * FROM proof";$result = mysql_query($query);$fields = mysql_num_fields($result);$rows = mysql_num_rows($result);$table = mysql_field_table($result, 0);echo "<pre>Your '" . $table . "' table has " . $fields . " fields and " . $rows . " record(s)\n";echo "The table has the following fields:\n";for ($i=0; $i < $fields; $i++) { $type = mysql_field_type($result, $i); $name = mysql_field_name($result, $i); $len = mysql_field_len($result, $i); $flags = mysql_field_flags($result, $i); echo $type . " " . $name . " " . $len . " " . $flags . "\n";}$query = "SHOW COLUMNS FROM proof";$result = mysql_query($query);while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { print_r($row);}[/code]Which will return:[code]Your 'proof' table has 20 fields and 0 record(s)The table has the following fields:string a 255 int b 4 blob c 65535 blobdate d 10 binaryint e 6 int f 9 int g 11 int h 20 real i 12 real j 22 unknown k 11 datetime l 19 binarytimestamp m 19 unsigned zerofill binarytime n 8 binaryyear o 4 unsigned zerofillstring p 255 blob q 255 blob binaryblob r 255 blobblob s 65535 blob binaryblob t 16777215 blob binaryArray( [Field] => a [Type] => varchar(255) [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => b [Type] => tinyint(4) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => c [Type] => text [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => d [Type] => date [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => e [Type] => smallint(6) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => f [Type] => mediumint(9) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => g [Type] => int(11) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => h [Type] => bigint(20) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => i [Type] => float [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => j [Type] => double [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => k [Type] => decimal(10,0) [Null] => YES [Key] => [Default] => 0 [Extra] => )Array( [Field] => l [Type] => datetime [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => m [Type] => timestamp [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => n [Type] => time [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => o [Type] => year(4) [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => p [Type] => char(255) [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => q [Type] => tinyblob [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => r [Type] => tinytext [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => s [Type] => blob [Null] => YES [Key] => [Default] => [Extra] => )Array( [Field] => t [Type] => mediumblob [Null] => YES [Key] => [Default] => [Extra] => )[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.