(PHP 4, PHP 5)
odbc_field_name — Get the columnname
$result_id
, int $field_number
)Gets the name of the field occupying the given column number in the given result identifier.
result_id
The result identifier.
field_number
The field number. Field numbering starts at 1.
Returns the field name as a string, or FALSE on error.
anuga at anuga dot se (2009-04-27 00:46:41)
I've been toying around with this for awhile to make it as simple and fast as possible.
<?php
/* Start The Connection */
if(!$odbc['connection'] = odbc_connect('DNS','USER','PASS'))
{
exit("Connection Failed<br />\n");
}
else
{
echo("Connected<br />\n");
}
/* Select Which Table */
$odbc['table'] = "table";
/* Fetch The Fieldnames into an Array */
if($result = odbc_exec($odbc['connection'],"select * from $odbc['table'];"))
{
for($i = 1;$i <= odbc_num_fields($result);$i++)
{
$odbc['rows']['fields'][$i] = odbc_field_name($result,$i);
}
unset($i);
odbc_free_result($result);
}
else
{
exit("Error in SQL Query");
}
/* Close The Connection */
if(odbc_close($odbc['connection']))
{
odbc_close($odbc['connection']);
}
/* Print The Array */
if(!empty($odbc['rows']))
{
print_r($odbc['rows']);
}
?>
sica at wnet dot com dot br (2008-11-05 10:39:16)
Hi hayes029, your idea was very helpful.
Here go a adaptation for select form field.
<?php
$consulta = "select * from schema.table";
$resposta = odbc_exec($con, $consulta);
for($i=1; $i <= odbc_num_fields($resposta); $i++)
echo "<option value=".odbc_field_name($resposta, $i).">".odbc_field_name($resposta, $i)."</option>";
?>
marco (2008-02-14 06:08:09)
we faced the 31 char limit using cakephp dbo_odbc.php class (version 1.1.18.5850)
at the end, it calls odbc_fetch_row giving the problem (i tracked it may be due to the php implementation of a ODBCv2 model, which has hard coded 4bytes, 32 bits file names length, while ODBCv3 seems to have a separate specification of that value, fwiw)
we learned from another user (having similar problems with dbo_mssql.php class) how to workaround this problem,
we solved using inheritance, redefining the method (locally to our application) in order to
- create a "map" of the query fields (as an array) before executing it
- execute the query in a non-associative way
- after the results are returned, restore the real field names applying the previous created "map"
another way could be use the COM like:
new COM("ADODB.Connection")
which does not show the 31 chars problem but it could be slower and bound to the windows platform.
NoEgzit (2007-11-22 06:23:40)
If you really need more than 31 characters here's what you can do:
open php5.x.x/ext/odbc/php_odbc_includes.h in an editor like notepad++
change 32 by a greater value in
typedef struct odbc_result_value {
char name[32];
char *value;
SDWORD vallen;
SDWORD coltype;
} odbc_result_value;
and recompile php.
I did it with char name[64] cause I have column names like "0214_1_VD_Type d'exploitation Type d'opération (Niveau 1)" [I didn't choose this stupid name it comes from Eccairs project]
and rebuild php with the method given here http://www.php.net/manual/en/install.windows.building.php and http://elizabethmariesmith.com/2006/11/09/
compiling-php52-on-windows-with-net-toolchain-is-it-even-possible/
with visual C++ express edition.
jezzghost (2006-02-21 10:32:36)
Note that there is a known limitation with this which truncates the length of the returned field name to 31 characters without warning.
hayes029 at bama dot ua dot edu (2004-04-13 10:01:59)
In search of a function that would simply return an array with the names of the fields in a result identifier, the only thing I could find was the odbc_field_name function. So, for anyone else looking for such a function, here's the (very simple) function I wrote:
function odbc_field_names ($result) {
for ($i=1; $i <= odbc_num_fields($result); $i++) $return_array[$i-1] = odbc_field_name($result, $i);
return $return_array;
}
Very simple, I know, but I thought it might be helpful.
andrea dot galli at acotel dot com (2003-04-29 08:12:31)
Example: function field name.
$Link_ID = odbc_connect("DSN", "user", "pass");
$query = "SELECT * FROM products";
$Query_ID = odbc_exec($Link_ID, $query);
while($field = $field_name($Query_ID ))
{
echo("Field: $field<br />\n");
}
---------------------
function field_name($PrQuery_ID)
{
if($Column < odbc_num_fields($PrQuery_ID))
{
$Column += 1;
$FieldName = odbc_field_name($PrQuery_ID, $Column);
return $FieldName;
}
else
{
return 0;
}
}
aleckzandr at yahoo dot com (2003-04-17 00:04:38)
Well, I've been into PHP for four hours and thanks to "my predecessors before me" (gold163, curt, et al.) I've managed the following. The first thing I try to learn with any web scripting language is to build a dynamic table from a data source. (One thing you didn't have to do gold -previous post- is build an array for the field value.) Cheers! Alex
<html>
<head>
<title>PHP Database Example</title>
</head>
<style type="text/css">
<!--
body {font: 10pt/12pt Tahoma, Verdana, Helvetica, sans-serif; color: indigo; margin: .25in .5in }
table {color:Navy; background-color:AntiqueWhite; border-color:Maroon; border-style:Solid; border-width: 2px; }
th {color: blue; font-weight: bold; }
td {font-size: smaller; }
.mytable {color:Maroon; background-color:White; border-color:Navy; border-style:Solid; border-width: 1px; }
th.mytable {background-color:#C0C0C0; }
//-->
</style>
<body>
<p><?php echo date("j F, Y"); ?></p>
<?php
$db = odbc_connect("eSell22MDB","","");
$result = odbc_exec($db, "select ProductID, ProductName, Description1 from Products");
// cool function - returns table
odbc_result_all($result, "border=\"1\" class=\"def\"");
$result = odbc_exec($db, "select * from Products") or die("Select failed");
$myUtil = new Utilities();
$myUtil->standard_table($result,"mytable");
class Utilities {
function standard_table($result,$class="")
{
// To format your table if you want to use cascading style sheets
if ($class == "")
{
$css_table = " border=\"1\"";
$css_tr = "";
$css_th = "";
$css_td = "";
}
else
{
$css_table = " class=\"$class\"";
$css_tr = " class=\"$class\"";
$css_th = " class=\"$class\"";
$css_td = " class=\"$class\"";
}
// Create field names for table header row
$i = 0;
$fieldCount = odbc_num_fields($result);
echo " <table$css_table>\n";
echo " <tr$css_tr>\n";
while ($i < $fieldCount)
{
$i++;
$fieldName = odbc_field_name($result, $i);
echo " <th$css_th>$fieldName</th>\n";
}
echo " </tr>\n";
# Create table data rows for query result
while (odbc_fetch_row($result))
{
$i = 0;
echo " <tr$css_tr>\n";
while ($i < $fieldCount)
{
$i++;
$fieldData = trim(odbc_result($result, $i));
if ($fieldData == "")
echo " <td$css_td> </td>\n";
else
echo " <td$css_td>$fieldData</td>\n";
}
echo " </tr>\n";
}
echo " </table>";
}
} // class Utilities
?>
</body>
</html>
gold163 at lisco dot com (2003-03-25 21:10:57)
Using your code, and taking it a step further, I can create a standard table from a single line of code by calling a function from my include file - the bonus is, that I can optionally provide a parameter for the name of my style sheet class - further simplifying my formatting of the table.
What synergy you find in these forums - eh?
$Conn = odbc_connect('dsn','user','pass');
$query = "SELECT * FROM yourtable";
$result = odbc_exec($Conn, $query) or die('Select failed!');
standard_table($result);
Function standard_table($result,$class='')
{
# To format your table if you want to use cascading style sheets
if ($class == '')
{
$css_table = ' border=1';
$css_tr = '';
$css_th = '';
$css_td = '';
}
else
{
$css_table = ' class=\"$class\"';
$css_tr = ' class=\"$class\"';
$css_th = ' class=\"$class\"';
$css_td = ' class=\"$class\"';
}
# Create field names for table header row
$i = 0;
$fCount = odbc_num_fields($result);
echo "<table$css_table><tr>";
while ($i < $fCount)
{
$i++;
$fName = odbc_field_name($result, $i);
echo "<th>$fName</th>";
}
echo "</tr>";
# Create table data rows for query result
$i = 0;
$fCount = odbc_num_fields($result);
while (odbc_fetch_row($result))
{
echo "<tr>";
while ($i < $fCount)
{
$i++;
$fName = odbc_field_name($result, $i);
$job[$fName] = odbc_result($result, $i);
echo "<td>$job[$fName]</td>";
}
echo "</tr>";
$i = 0;
}
echo "</table>";
}
curt at digmo dot com (2001-07-31 18:58:36)
I turned Jason's code into a function to roughly mimic the mysql_fetch_array function. I'm not a programmer and I've been messing with PHP less than a week, so I imagine there's a more efficient method that what I've come up with.
function odbc_fetch_array($rownum, $res)
{
$i = 0;
$fCount = odbc_num_fields($res);
odbc_fetch_row($res, $rownum);
while ($i < $fCount)
{
$i++;
$fName = odbc_field_name($res, $i);
$myrow[$fName] = odbc_result($res, $i);
}
$i=0;
return $myrow;
}
(2001-03-10 16:11:49)
Wow, I finally have something to contribute.
If you, like me, have been seeking a way to name and fill your variables with the appropriate names an values, rather than naming every variable and using odbc_result($result, 1), odbc_result($result, 2), etc...Then this little loop is for you! It would probably be nice to use as function, but I'm sure you can do that on your own, eh?
<?php
$query = "SELECT * FROM TableName";
$result = odbc_exec($conn, $query) or die('Select failed!');
$i = 0;
$fCount = odbc_num_fields($result);
while (odbc_fetch_row($result)) {
while ($i < $fCount) {
$i++;
$fName = odbc_field_name($result, $i);
$job[$fName] = odbc_result($result, $i);
}
$i=0;
}
?>
This should be pretty simple code to follow, you can address your variables at any time later using the column names from your table. For now I am addressing them with their real values and using this simply to avoid having to type out all the variable names in the top of my code. Have fun.
Jason/ArtHacker.com