mysql_fetch_field

(PHP 4, PHP 5)

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

Warning

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide. Alternatives to this function include:

Description

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

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, which is the alias name if one is defined
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
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.

Note:
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().

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 ¶

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 ¶

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 ¶

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

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

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 ¶

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

mysql_fetch_field

Description

Parameters

Return Values

Examples

Notes

See Also

Improve This Page

User Contributed Notes 12 notes