Jump to content

Archived

This topic is now archived and is closed to further replies.

nadeemshafi9

get a mysql feild type

Recommended Posts

hi again

I 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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
here is the show colums syntax

SHOW [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 array

i dont understand how i can pull the type out is the result multi dimensional if so please can somone give me an example

i just need the type of the column so my software can check if its an image or audio feild ect and compensate.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
http://us2.php.net/manual/en/function.mysql-field-type.php

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
really? well then maybe you should add a note to the manual then, correcting the other notes.

http://us3.php.net/mysql_field_type

Share this post


Link to post
Share on other sites
I've no need to. The information is readily available because others have posted it ;)

Share this post


Link to post
Share on other sites
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 blob
date d 10 binary
int e 6
int f 9
int g 11
int h 20
real i 12
real j 22
unknown k 11
datetime l 19 binary
timestamp m 19 unsigned zerofill binary
time n 8 binary
year o 4 unsigned zerofill
string p 255
blob q 255 blob binary
blob r 255 blob
blob s 65535 blob binary
blob t 16777215 blob binary
Array
(
    [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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.