mysql_fetch_assoc
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.
mysql_fetch_field
(PHP 4, PHP 5, PECL mysql:1.0)
mysql_fetch_field — Retourne les données enregistrées dans une colonne MySQL sous forme d'objet
Description
object mysql_fetch_field
( resource $result
[, int $field_offset
] )
Retourne un objet contenant les informations sur les champs. Cette fonction peut être utilisée pour obtenir des informations sur les champs de la requête fournie result .
Liste de paramètres
- result
-
La ressource de résultat qui vient d'être évaluée. Ce résultat vient de l'appel à la fonction mysql_query().
- field_offset
-
La position numérique du champ. Si la position du champ n'est pas spécifiée, le champ suivant qui n'a pas encore été récupéré par cette fonction est alors récupéré. field_offset commence à 0.
Valeurs de retour
Retourne un objet contenant les informations sur les champs. Les propriétés de l'objet sont les suivantes :
- "name" : nom de la colonne
- "table" : nom de la table de la colonne
- "def" : valeur par défaut de la colonne
- "max_length" : taille maximale de la colonne
- "not_null" : 1 si la colonne ne peut pas être NULL
- "primary_key" : 1 si la colonne est une clé primaire
- "unique_key" : 1 si la colonne est une clé unique
- "multiple_key" : 1 si la colonne est une clé non unique
- "numeric" : 1 si la colonne est numérique
- "blob" : 1 si la colonne est BLOB
- "type" : le type de la colonne
- "unsigned" : 1 si la colonne est non signée
- "zerofill" : 1 si la colonne est complétée par des zéro
Exemples
Exemple #1 Exemple avec mysql_fetch_field()
<?php
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$conn) {
die('Impossible de se connecter : ' . mysql_error());
}
mysql_select_db('database');
$result = mysql_query('select * from table');
if (!$result) {
die('Échec de la requête : ' . mysql_error());
}
/* Lecture des méta données de la colonne */
$i = 0;
while ($i < mysql_num_fields($result)) {
echo "Détails sur la colonne $i:<br />\n";
$meta = mysql_fetch_field($result, $i);
if (!$meta) {
echo "Aucun détail disponible<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: Les noms des champs retournés par cette fonction sont sensibles à la casse.
add a note
User Contributed Notesmysql_fetch_field
jorachim at geemail dot com
25-Sep-2008 10:09
25-Sep-2008 10:09
david at vitam dot be
10-Jun-2008 02:57
10-Jun-2008 02:57
A little function to help coders to distinct the tablename from a multiselect query where some fields has the same name in differents tables.
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;
}
dheep
04-Jun-2008 05:56
04-Jun-2008 05:56
Simple PHP script for displaying the field names. Presuming the database is seleected already.
$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.
Christopher Mullins
18-Mar-2008 08:49
18-Mar-2008 08:49
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 04:34
04-Nov-2005 04: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 08:18
15-Sep-2005 08: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 07:15
12-Sep-2005 07: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 07:09
23-Jun-2005 07: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
18-Jul-2003 12:26
18-Jul-2003 12: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 11:18
27-Mar-2003 11: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 05:56
19-Jun-2002 05: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 08:22
06-Jun-2002 08: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 02:00
19-Apr-2002 02: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 01:09
22-Mar-2002 01: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 03:12
10-Mar-2002 03: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'];
}