mysql_fetch_field

(PHP 4, PHP 5)

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

说明

object mysql_fetch_field ( resource $result [, int $field_offset ] )

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

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

对象的属性为：

name - 列名
table - 该列所在的表名
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

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

Example #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 ());
 
 $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

范例

Example #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 ());
}
 
 $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 );
 ?>

注释

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

参见

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

用户评论:

[#1] mwwaygoo AT hotmail DOT com [2012-05-30 10:20:02]

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

[#2] Daniel B [2012-03-26 02:56:15]

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

[#3] lucien at ocia dot nl [2012-02-04 13:08:11]

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.

[#4] eviltofu at gmail dot com [2011-07-08 17:58:35]

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?

[#5] Jonathan [2010-07-30 17:45:35]

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

[#6] TALU [2009-02-04 08:16:14]

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

[#7] php [spat] hm2k.org [2008-11-04 15:22:32]

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

[#8] jorachim at geemail dot com [2008-09-25 13:09:38]

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.

[#9] david at vitam dot be [2008-06-10 05:57:22]

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

[#10] dheep [2008-06-03 20:56:46]

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.

[#11] Nick Baicoianu [2005-09-15 11:18:20]

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.

[#12] php at brayra dot com [2002-03-21 16:09:30]

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.

[#13] krang at krang dot org dot uk [2002-03-10 06:12:38]

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