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 = 0): object

フィールド情報を含むオブジェクトを返します。特定のクエリー結果の中のフィールドに関する情報を得るために使用可能です。

パラメータ

result: 評価された結果リソース。この結果は、mysql_query() のコールにより得られたものです。
field_offset: 数字で表したフィールドの位置です。もし指定されなければ、まだこの関数で情報を取得していないフィールドのうち最初のものが選択されます。field_offset は、 0 からはじまります。

戻り値

フィールド情報を含むobjectを返します。オブジェクトのプロパティは次のとおりです。

name - カラム名
table - カラムが属しているテーブルの名前。エイリアスを定義している場合はエイリアスの名前
max_length - カラムの最大長
not_null - カラムが null 値をとることができない場合 1
primary_key - カラムが主キーであれば 1
unique_key - カラムがユニークキーであれば 1
multiple_key - カラムが非ユニークキーであれば 1
numeric - カラムが数値(numeric)であれば 1
blob - カラムがBLOBであれば 1
type - カラムの型
unsigned - カラムが符号無し(unsigned)であれば 1
zerofill - カラムがゼロで埋められている(zero-filled)場合に 1

例

例1 mysql_fetch_field() の例

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

注意

注意: この関数により返されるフィールド名は 大文字小文字を区別 します。

注意:
SQL クエリでフィールド名やテーブル名のエイリアスを設定している場合は、エイリアスを返します。本来の名前を取得するには、 mysqli_result::fetch_field() を使いましょう。

参考

mysql_field_seek() - 結果ポインタを指定したフィールドオフセットにセットする

Improve This Page

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 12 notes

down

david at vitam dot be ¶

16 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 ¶

22 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

Nick Baicoianu ¶

19 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 ¶

12 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 ¶

12 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

-1

jorachim at geemail dot com ¶

16 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

-2

Jonathan ¶

14 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 ¶

12 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 ¶

15 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 ¶

22 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 ¶

16 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 ¶

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

＋add a note