Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Functions : mysql_fetch_field

mysql_fetch_field

Get column information from a result and return as an object (PHP 4, PHP 5, PECL mysql:1.0)
object mysql_fetch_field ( resource result [, int field_offset] )

Example 1431. mysql_fetch_field() example

copy to clipboard
<?php
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$conn) {
   die('Could not connect: ' . mysql_error());
}
mysql_select_db('database');
$result = mysql_query('select * from table');
if (!$result) {
   die('Query failed: ' . mysql_error());
}
/* get column metadata */
$i = 0;
while ($i < mysql_num_fields($result)) {
   echo "Information for column $i:<br />\n";
   $meta = mysql_fetch_field($result, $i);
   if (!$meta) {
       echo "No information available<br />\n";
   }
   echo "<pre>
blob:         $meta->blob
max_length:   $meta->max_length
multiple_key: $meta->multiple_key
name:         $meta->name
not_null:     $meta->not_null
numeric:      $meta->numeric
primary_key:  $meta->primary_key
table:        $meta->table
type:         $meta->type
default:      $meta->def
unique_key:   $meta->unique_key
unsigned:     $meta->unsigned
zerofill:     $meta->zerofill
</pre>";
   $i++;
}
mysql_free_result($result);
?>

Related Examples ( Source code ) » mysql_fetch_field

Examples ( Source code ) » Get column name, type and max length

Code Examples / Notes » mysql_fetch_field

krang
The field type returns what PHP classifies the data found in the field, not how it is stored in the database; use the following example to retrieve the MySQL information about the field....
$USERNAME = '';
$PASSWORD = '';
$DATABASE = '';
$TABLE_NAME = '';
mysql_connect('localhost', $USERNAME, $PASSWORD)
   or die ("Could not connect");
$result = mysql_query("SHOW FIELDS FROM $DATABASE.$TABLE_NAME");
$i = 0;
while ($row = mysql_fetch_array($result)) {
 echo $row['Field'] . ' ' . $row['Type'];
}

creak
Sorry, the last pattern doesn't work with type whithout length, so it would be better if you change it with this one :
   "^([a-zA-Z]+)\(?([^\)]*)\)?$"
It's still $type[1] for the type and $type[2] for the length.
Creak

chrisshaffer
Slight error in the above comment:
$fieldLen = split("','",substr(1,-1,$fieldLen));
should read:
$fieldLen = split("','",substr($fieldLen,1,-1));
oops! ;)
I did take the above code (which saved me at least two hours worth of work), and massaged it into a function:
function mysql_enum_values($tableName,$fieldName)
{
 $result = mysql_query("DESCRIBE $tableName");
 //then loop:
 while($row = mysql_fetch_array($result))
 {
   //# row is mysql type, in format "int(11) unsigned zerofill"
   //# or "enum('cheese','salmon')" etc.
   ereg('^([^ (]+)(\((.+)\))?([ ](.+))?$',$row['Type'],$fieldTypeSplit);
   //# split type up into array
   $ret_fieldName = $row['Field'];
   $fieldType = $fieldTypeSplit[1];// eg 'int' for integer.
   $fieldFlags = $fieldTypeSplit[5]; // eg 'binary' or 'unsigned zerofill'.
   $fieldLen = $fieldTypeSplit[3]; // eg 11, or 'cheese','salmon' for enum.
   if (($fieldType=='enum' || $fieldType=='set') && ($ret_fieldName==$fieldName) )
   {
     $fieldOptions = split("','",substr($fieldLen,1,-1));
     return $fieldOptions;
   }
 }
 //if the funciton makes it this far, then it either
 //did not find an enum/set field type, or it
 //failed to find the the fieldname, so exit FALSE!
 return FALSE;
}
The most useful thing that I can think to do with this is to populate a HTML Dropdown box with it:
echo "<SELECT NAME=\"Select\" SIZE='1'>";
foreach($fieldOptions as $tmp)
{
 echo "<OPTION>$tmp";
}
Hope this helps  :D

justin
Same problem, slightly different solution.
$result = mysql_query("DESCRIBE tablename");
# or SHOW COLUMNS FROM
# or SHOW FIELDS FROM
then loop:
$row = mysql_fetch_array($result);
# row is mysql type, in format "int(11) unsigned zerofill"
# or "enum('cheese','salmon')" etc.
ereg('^([^ (]+)(\((.+)\))?([ ](.+))?$',$row['Type'],$fieldTypeSplit);
# split type up into array
$fieldType = $fieldTypeSplit[1]; # eg 'int' for integer.
$fieldFlags = $fieldTypeSplit[5]; # eg 'binary' or 'unsigned zerofill'.
$fieldLen = $fieldTypeSplit[3]; # eg 11, or 'cheese','salmon' for enum.
You might then like to:
if ($fieldType=='enum' or $fieldType=='set')
 $fieldLen = split("','",substr(1,-1,$fieldLen));
So for enum or set types, $fieldLen becomes an array of possible values.
Hope that helps someone out there...

blakjak
mysql_fetch_field will fail if your result set's internal pointer has advanced past the end of the result.
So, if you use ...
$result = mysql_query("select * from person where id=1"); // returns 1 row
$row = mysql_fetch_row($result);
while($field = mysql_fetch_field){
echo $field->name;
}
... you won't see any fields.  If you need to use mysql_fetch_field, you need to do it before you've iterated through all the rows in the result set.

inaxio
just another option to get all enum|set values from table definition. values are stored into arrays using the same field name
$result = mysql_query("SHOW COLUMNS FROM [table_name]");
while($row = mysql_fetch_object($result)){
  if(ereg(('set|enum'), $row->Type)){
      eval(ereg_replace('set|enum', '$'.$row->Field.' = array', $row->Type).';');
  }
}

dave
If you want to get the max length of a column not just the data use this:
$result = mysql_query ("SELECT * FROM table");
$fields = mysql_num_fields ($result);
$i = 0;
while ($i < $fields) {
$len   = mysql_field_len   ($result, $i);
$i++;
}
Or refer to http://www.php.net/manual/function.mysql-field-type.php

admin
If you need to get separated field size you should use
this part of code (I also included a database request function):
!!!!! Take care of warped lines
----------------
// Function to call to perform a database request
<?
function sql_request($sql_query,$db_name)
 {
   global $rows_count;
   $db_link = mysql_connect("localhost","username","password")
or die("MySQL connect failed");
   @mysql_select_db($db_name) or
die("unable to select: $db_name");
   $query_answer = mysql_query($sql_query);
   $rows_count = mysql_num_rows($query_answer);
   mysql_close($db_link);
   return $query_answer;
 }
$editing_db = "mydb";
$editing_table = "mytable";
$query_answer = sql_request("SHOW FIELDS FROM $editing_table",$editing_db);
$i = 0;
while ($row = mysql_fetch_array($query_answer))
{
 $table_structure[$i][0] = $row['Field'];
 $first_parenthesis = strpos($row['Type'],"(");
 $last_parenthesis = strpos($row['Type'],")");
 if ($first_parenthesis AND $last_parenthesis)
 {
   $table_structure[$i][1] = substr($row['Type'],0,$first_parenthesis);
   $table_structure[$i][2] = substr($row['Type'],$first_parenthesis+1,
$last_parenthesis-$first_parenthesis-1);
 }
 if ($row[Key] == "PRI") $table_structure[$i][3] = 1;
 echo $i." ".$table_structure[$i][0]." ";
 echo $table_structure[$i][1]." ";
 echo $table_structure[$i][2]." ".$table_structure[$i][3]."
";
 $i++;
}
?>
Hope this will be usefull!
Andre Lebeuf
STR ATM and Terminals

php
I needed to get the field information and the enum/set values. Here is the function I created to expand the object returned by mysql_fetch_field. I also, decided to return all the fields for a table in an array of field objects by "name" and position much like mysql_fetch_array does.
You could test it by using:
$myfields = GetFieldInfo('test_table');
print "<pre>";
print_r($myfields);
print "</pre>";
The field objects now have 'len', 'values' and 'flags' parameters.
NOTE: 'values' only has data for set and enum fields.
//This assumes an open database connection
//I also use a constant DB_DB for current database.
function GetFieldInfo($table)
{
 if($table == '') return false;
 $fields = mysql_list_fields(DB_DB, $table);
 if($fields){
   $columns = mysql_query('show columns from ' . $table);
   if($columns){
     $num = mysql_num_fields($fields);
     for($i=0; $i < $num; ++$i){
       $column = mysql_fetch_array($columns);
       $field = mysql_fetch_field($fields, $i);
       $flags = mysql_field_flags($fields, $i);
       if($flags == '') $flags=array();
       else $flags = explode(' ',$flags);
       if (ereg('enum.(.*).',$column['Type'],$match))
         $field->values = explode(',',$match[1]);
       if (ereg('set.(.*).',$column['Type'],$match))
         $field->values = explode(',',$match[1]);
       if(!$field->values) $field->values = array();
       $field->flags = $flags;
       $field->len = mysql_field_len($fields, $i);
       $result_fields[$field->name] = $field;
       $result_fields[$i] = $field;
     }
     mysql_free_result($columns);
   }
   mysql_free_result($fields);
   return $result_fields;
 }
 return false;
}
hope someone else finds this useful.

nick baicoianu
Be sure to note that $max_length is the length of the longest value for that field in the returned dataset, NOT the maximum length of data that column is designed to hold.

cortalux
A far easier way of getting information upon an enum field, is this.
function enumget($field="",$table="") {
$result=mysql_query("SHOW COLUMNS FROM `$table` LIKE '$field'");
if(mysql_num_rows($result)>0){
$row=mysql_fetch_row($result);
$options=explode("','", preg_replace("/(enum|set)\('(.+?)'\)/","\\2", $row[1]));
} else {
$options=array();
}
return $options;
}

kflam
#Input: the table name and the enum field
#Output: an array that stores all options of the enum field or
#false if the input field is not an enum
function getEnumOptions($table, $field) {
  $finalResult = array();
  if (strlen(trim($table)) < 1) return false;
  $query  = "show columns from $table";
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result)){
       if ($field != $row["Field"]) continue;
       //check if enum type
       if (ereg('enum.(.*).', $row['Type'], $match)) {
           $opts = explode(',', $match[1]);
           foreach ($opts as $item)
               $finalResult[] = substr($item, 1, strlen($item)-2);
       }
       else
               return false;
  }
  return $finalResult;
}
The function could be handy when making a selection option without typing all the options items respectively.

Change Language


Follow Navioo On Twitter
mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_set_charset
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker