Submit a Pull Request Report a Bug

mysql_fetch_field

(PHP 4, PHP 5)

mysql_fetch_field — 从结果集中取得列信息并作为对象返回

警告

本扩展自 PHP 5.5.0 起已废弃，并在自 PHP 7.0.0 开始被移除。应使用 MySQLi 或 PDO_MySQL 扩展来替换之。参见 MySQL：选择 API 指南来获取更多信息。用以替代本函数的有：

说明

mysql_fetch_field(resource $result, int $field_offset = ?): object

返回一个包含字段信息的对象。

mysql_fetch_field() 可以用来从某个查询结果中取得字段的信息。如果没有指定字段偏移量，则下一个尚未被 mysql_fetch_field() 取得的字段被提取。

对象的属性为：

name - 列名
table - 该列所在的表名，which is the alias name if one is defined
max_length - 该列最大长度
not_null - 1，如果该列不能为 null
primary_key - 1，如果该列是 primary key
unique_key - 1，如果该列是 unique key
multiple_key - 1，如果该列是 non-unique key
numeric - 1，如果该列是 numeric
blob - 1，如果该列是 BLOB
type - 该列的类型
unsigned - 1，如果该列是无符号数
zerofill - 1，如果该列是 zero-filled

注意: 此函数返回的字段名大小写敏感。

示例 #1 mysql_fetch_field()

<?php
mysql_connect('localhost:3306', $user, $password)
    or die("Could not connect: " . mysql_error());
mysql_select_db("database");
$result = mysql_query("select * from table")
    or 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);
    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
unique_key:   $meta->unique_key
unsigned:     $meta->unsigned
zerofill:     $meta->zerofill
</pre>";
    $i++;
}
mysql_free_result($result);
?>

参见 mysql_field_seek()。

参数

result: resource 型的结果集。此结果集来自对 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.

返回值

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

name - column name
table - name of the table the column belongs to
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

示例

示例 #2 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
unique_key:   $meta->unique_key
unsigned:     $meta->unsigned
zerofill:     $meta->zerofill
</pre>";
    $i++;
}
mysql_free_result($result);
?>

注释

注意: 此函数返回的字段名大小写敏感。

注意:
If field or tablenames are aliased in the SQL query the aliased name will be returned. The original name can be retrieved for instance by using mysqli_result::fetch_field().

参见

mysql_field_seek() - 将结果指针设置为指定的字段偏移量

＋add a note

User Contributed Notes 13 notes

down

david at vitam dot be ¶

15 years ago

A little function to help coders to distinct the tablename from a multiselect query where some fields has the same name in differents tables.



<?php

public function sql($sql) {

    $T_Return=array();

    $result=@mysql_query($sql);

    

    $i=0;

    while ($i < mysql_num_fields($result)) {            

        $fields[]=mysql_fetch_field($result, $i);

        $i++;

    }

    

    while ($row=mysql_fetch_row($result)) {                

        $new_row=array();

        for($i=0;$i<count($row); $i++) {

            $new_row[ $fields[$i]->table][$fields[$i]->name]=$row[$i];

        }

        $T_Return[]=$new_row;

    }



    

    return $T_Return;

}

?>

down

krang at krang dot org dot uk ¶

21 years ago

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



<?php

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

}

?>

down

jorachim at geemail dot com ¶

15 years ago

If you want the fields in a table, a simple DESCRIBE query will work:

<?php
$query ="DESCRIBE Users";
$result = mysql_query($query);

echo "<ul>";

while($i = mysql_fetch_assoc($result))
     echo "<li>{$i['Field']}</li>";

echo "</ul>";
?>

Should do the trick.

down

Nick Baicoianu ¶

18 years ago

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.

down

-1

lucien at ocia dot nl ¶

11 years ago

Performance Notes!

I used this script for testing, the table has 26 colums.

<?php
$t_start = microtime(true);
$sql = mysql_query("SELECT * FROM `table` LIMIT 1") or trigger_error(mysql_error(), E_USER_WARNING);
for ($i = 0; $i < mysql_num_fields($sql); $i++) {
    $meta = mysql_fetch_field($sql, $i);
    echo "Information for column ".$meta->name.":\n";
    echo 
"\tblob:         $meta->blob
\tmax_length:   $meta->max_length
\tmultiple_key: $meta->multiple_key
\tname:         $meta->name
\tnot_null:     $meta->not_null
\tnumeric:      $meta->numeric
\tprimary_key:  $meta->primary_key
\ttable:        $meta->table
\ttype:         $meta->type
\tunique_key:   $meta->unique_key
\tunsigned:     $meta->unsigned
\tzerofill:     $meta->zerofill
";
}
$t_stop = microtime(true);
$t_proc = $t_stop - $t_start;
echo "processing time query 1: ".number_format($t_proc * 1000, 3)." ms\n";
unset($t_start);
unset($t_stop);
unset($t_proc);
$t_start = microtime(true);
$sql = mysql_query("DESCRIBE `table`");
while ($res = mysql_fetch_array($sql, MYSQL_ASSOC)) {
    print_r($res);
}
$t_stop = microtime(true);
$t_proc = $t_stop - $t_start;
echo "processing time query 2: ".number_format($t_proc * 1000, 3)." ms\n";
?>

Query 1 => 0.444 ms
Query 2 => 1.146 ms

So for easy usage, Query 2 is advised... But if your a performance-geek, you should use Query 1.

down

-1

mwwaygoo AT hotmail DOT com ¶

11 years ago

Using mysql_fetch_field you can produce a more robust version of mysql_fetch_assoc.



When querying 2 tables with the same field names, generally you would need to use mysql_fetch_row to get an integer key'ed array rather than an associated key'ed array. This is because fields of the same name in the second table will over-write the data returned from the first table.

However this simple function will insert the table name prior to the field name for the key and prevent cross-overs.



ie SELECT *, 'test' AS test 4 FROM table AS T_1, table AS T_2 WHERE T_1.a=T_2.b



could produce:



mysql_fetch_assoc() returns

array(

'index'=>2,

'a'=>'pear',

'b'=>'apple',

'test'=>'test',

4=>4

)



mysql_fetch_table_assoc() returns

array(

'T_1.index' =>1,

'T_1.a'=>'apple',

'T_1.b'=>'banana',

'T_2.index'=>2,

'T_2.a'=>'pear',

'T_2.b'=>'apple',

'test'=>'test',

4=>4

)



<?php

function mysql_fetch_table_assoc($resource)

{

    // function to get all data from a query, without over-writing the same field

    // by using the table name and the field name as the index

    

    // get data first

    $data=mysql_fetch_row($resource);

    if(!$data) return $data; // end of data

    

    // get field info

    $fields=array();

    $index=0;

    $num_fields=mysql_num_fields($resource);

    while($index<$num_fields)

    {

        $meta=mysql_fetch_field($resource, $index);

        if(!$meta)

        {

            // if no field info then just use index number by default

            $fields[$index]=$index;

        }

        else

        {

            $fields[$index]='';

            // deal with field aliases - ie no table name (SELECT T_1.a AS temp)

            if(!empty($meta->table)) $fields[$index]=$meta->table.'.';

            // deal with raw data - ie no field name (SELECT 1)

            if(!empty($meta->name))  $fields[$index].=$meta->name; else $fields[$index].=$index;

        }

        $index++;

    }

    $assoc_data=array_combine($fields, $data);

    return $assoc_data;

}

?>

down

-2

Jonathan ¶

13 years ago

It should be noted that the primary_key member variable is only set to 1 if the primary key on the table is only on that 1 field. If you have a table that has a multiple column primary key, then you will not get what you might expect.

For example:
CREATE TABLE `line_item_table` (
  `liForeignKey1` int(11) unsigned not null,
  `liForeignKey2` int(11) unsigned not null,
  PRIMARY KEY (`liForeignKey1`, `liForeignKey2`)
) ENGINE=MyISAM;

While you might expect that primary_key == 1 for both columns; var_dump() will show you that you get the following for both fields:
["primary_key"]=>int(0)

This is as of PHP 5.2.13 and MySQL 5.0.51

down

-3

Daniel B ¶

11 years ago

Simple function to display all data in a query...

function dumpquery($query) {
    $numfields = mysql_num_fields($query);
    echo '<table border="1" bgcolor="white"><tr>';
    for ($i = 0; $i<$numfields; $i += 1) {
        $field = mysql_fetch_field($query, $i);
        echo '<th>' . $field->name . '</th>';
    }
    echo '</tr>';
    while ($fielddata = mysql_fetch_array($query)) {
        echo '<tr>';
        for ($i = 0; $i<$numfields; $i += 1) {
            $field = mysql_fetch_field($query, $i);
            echo '<td>' . $fielddata[$field->name] . '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';    
}

down

-3

TALU ¶

14 years ago

XML generation.

Bit of a security risk allowing parameters to select db and table on live server (unless user is restricted or replace the $_GET with fixed value.)

Outputs xml with standard format for <config> part to generate forms in flash.

<?php
    //
    //    makeXML.php?db=dbname&table=tablename
    //
    
    set_time_limit(300);
        
    $host = "localhost";
    $user = "root";
    $password = "root";
    
    $database = $_GET['db'];    
    $table = $_GET['table'];
    
    mysql_connect($host,$user,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    

    $querytext="SELECT * FROM ".$table;  
    $result=mysql_query($querytext);
    
    if ($result){
        $num=mysql_num_rows($result);
    }else{
        $num=0;
    }
    
?>
<?php 
    header('Content-Type: text/xml');
     echo "<?xml version='1.0'?>"; 
     
     if ($num > 0){
 ?>
<<?php  echo $table?>>
    <config>
        <?php
            // Display number of fields
            echo "<numFields>".mysql_num_fields($result)."</numFields>";
            $i = 0;
            $primaryKey = "";
            $nameArray = array(); 
            $maxLengthArray = array(); 
            $typeArray = array(); 
            while ($i < mysql_num_fields($result)) {
                $meta = mysql_fetch_field($result, $i);
                $nameArray[$i] = $meta->name;
                $maxLengthArray[$i] = $meta->max_length;
                $typeArray[$i] = $meta->type;
                if ($meta->primary_key){
                    $primaryKey = $meta->name;
                }
                $i++;
            }
            $i = 0;
            echo "<fieldNames>";
            while ($i < count($nameArray)) {
                echo "<field".$i.">".$nameArray[$i]."</field".$i.">";
                $i++;
            }
            echo "</fieldNames>";
            $i = 0;
            echo "<fieldMaxLength>";
            while ($i < count($maxLengthArray)) {
                echo "<field".$i.">".$maxLengthArray[$i]."</field".$i.">";
                $i++;
            }
            echo "</fieldMaxLength>";
            $i = 0;
            echo "<fieldType>";
            while ($i < count($typeArray)) {
                echo "<field".$i.">".$typeArray[$i]."</field".$i.">";
                $i++;
            }
            echo "</fieldType>";
        ?>
        <primaryKey><?php  echo $primaryKey?></primaryKey>
        <numRecords><?php  echo $num?></numRecords>
    </config>
<?php  
    $i=0;
    while ($i < $num) {
        $ID=mysql_result($result,$i,"ID");
        $value=mysql_result($result,$i,"value");
        $title=mysql_result($result,$i,"title");
        $description=mysql_result($result,$i,"description");
?>
    <row>
        <ID><?php  echo $ID?></ID>
        <weighting><?php  echo $value?></weighting>
        <title><?php  echo $title?></title>
        <description><?php  echo $description?></description>
    </row>
<?php 
        $i = $i + 1;
    } 
?>
</<?php  echo $table?>>

<?php 
    } 
?>

down

-3

php at brayra dot com ¶

21 years ago

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:



<?php

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



<?php

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

down

-2

php [spat] hm2k.org ¶

15 years ago

An improvement on the earlier mysql_column_exists function.

<?php

function mysql_column_exists($table_name, $column_name, $link=false) {
    $result = @mysql_query("SHOW COLUMNS FROM $table_name LIKE '$column_name'", $link);
    return (mysql_num_rows($result) > 0);
}

?>

down

-5

eviltofu at gmail dot com ¶

12 years ago

MYSQLI_TYPE_BLOB indicates the field is a BLOB or a TEXT. I think you would need to check the blob value. If its true then it's a BLOB, otherwise it's a TEXT. Can someone confirm?

down

-6

dheep ¶

15 years ago

Simple PHP script for displaying the field names. Presuming the database is seleected already.



<?php

$sql = "SELECT * FROM table_name;";

$result = mysql_query($sql);

$i = 0;

while($i<mysql_num_fields($result))

{

  $meta=mysql_fetch_field($result,$i);

  echo $i.".".$meta->name."<br />";

  $i++;

}

?>



OUTPUt:

0.id

1.todo

2.due date

3.priority

4.type

5.status

6.notes



hope this is useful.

＋add a note