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] )

Returns an object containing field information. This function can be used to obtain information about fields in the provided query result.

Parameters

result

The result resource that is being evaluated. This result comes from a call to mysql_query().

field_offset

The numerical field offset. If the field offset is not specified, the next field that was not yet retrieved by this function is retrieved. The field_offset starts at 0.

Return Values

Returns an object containing field information. The properties of the object are:

  • name - column name
  • table - name of the table the column belongs to
  • def - default value of the column
  • max_length - maximum length of the column
  • not_null - 1 if the column cannot be NULL
  • primary_key - 1 if the column is a primary key
  • unique_key - 1 if the column is a unique key
  • multiple_key - 1 if the column is a non-unique key
  • numeric - 1 if the column is numeric
  • blob - 1 if the column is a BLOB
  • type - the type of the column
  • unsigned - 1 if the column is unsigned
  • zerofill - 1 if the column is zero-filled

Examples

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);
?>


Notes

Note:

Field names returned by this function are case-sensitive.

See Also
mysql_field_seek()

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