Last updated: Fri, 16 May 2008

mysql_fetch_field

(PHP 4, PHP 5, PECL mysql:1.0)

mysql_fetch_field — Get column information from a result and return as an object

Description

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 #1 mysql_fetch_field() example


<?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.


I created the following function to make creating and updating of mysql tables easier. I placed it here in the hopes that it may be useful for someone else.



//

// mysql_column_exists(<Table Name>,<Column Name>)

// return type: Boolean

//

function mysql_column_exists($TableName='',$ColumnName='')

{

  if(($TableName == '') || ($ColumnName == ''))

  {

    return False;

  }

  $QueryStr = sprintf("SHOW COLUMNS FROM %s;",$TableName);

  $QueryPtr = mysql_query($QueryStr);

  if($QueryPtr === False)

  {

    return False;

  }

  while(($QueryRow = mysql_fetch_assoc($QueryPtr)) !== False)

  {

    if($QueryRow['Field'] == $ColumnName)

    {

      unset($QueryRow);

      mysql_free_result($QueryPtr);

      return True;

    }

  }

  return False;

}

  

- Christopher Mullins

inaxio
04-Nov-2005 03:34


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).';');

   }

}

Nick Baicoianu
15-Sep-2005 07:18


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.

admin at str-atm dot com
12-Sep-2005 06:15


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]."<br>";

  $i++;

}

?>

Hope this will be usefull!



Andre Lebeuf

STR ATM and Terminals

cortalux at gmail dot com
23-Jun-2005 06:09


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;

}

creak at foolstep dot com
17-Jul-2003 11:26


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

blakjak at nospam dot com
27-Mar-2003 10:18


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.

kflam at awc dot net dot au
19-Jun-2002 04:56


#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.

chrisshaffer at bellsouth dot net
06-Jun-2002 07:22


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 at quadmyre dot com
19-Apr-2002 01:00


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...

php at brayra dot com
22-Mar-2002 12:09


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.

krang at krang dot org dot uk
10-Mar-2002 02:12


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'];

}

dave at techweavers dot net
04-Oct-2000 09:48


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

add a note

mysql_fetch_lengths

mysql_fetch_assoc