Jump to content

get a mysql feild type


nadeemshafi9

Recommended Posts

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.