(PHP 4, PHP 5, PHP 7, PHP 8)
odbc_field_name — Get the columnname
Gets the name of the field occupying the given column number in the given result identifier.
statementThe result identifier.
fieldThe field number. Field numbering starts at 1.
Returns the field name as a string, or false on error.
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.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']);
}
?>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>";
?>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.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.Note that there is a known limitation with this which truncates the length of the returned field name to 31 characters without warning.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;
}
}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>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>";
}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.comI 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;
}