PHP autmatically enter column headings at start of table from ODBC

[scriggs]

I am extracting some data from an odbc datasource and presenting in a table.

How can I automatically extract the number of column and their headings, and then print them at the top of the table.

<?php 
     
    //connect to the database 
    $connectionstring = odbc_connect("timeandfees_db", "", ""); 
     
    //Define SQL query 
    $Query = "SELECT clientkey, coyname FROM clients WHERE (clientkey LIKE 'AC%') ORDER BY ClientKey"; 
     
    //execute query 
    $queryexe = odbc_do($connectionstring, $Query); 

   $num = better_odbc_num_rows($connectionstring,$Query);
   echo $num;     

    //query database 
    while(odbc_fetch_row($queryexe)) 
    { 
    $clientkey = odbc_result($queryexe, 1); 
    $coyname = odbc_result($queryexe, 2); 
     
    //format results 
    print ("<tr>"); 
    print ("<td>$clientkey</td>"); 
    print ("<td>$coyname</td>"); 
    print ("</tr>"); 
    } 
     
    //disconnect from database 
    odbc_close($connectionstring); 

    ?>

 

[sleipnir214]

In the case of your example code, this part of your query:

SELECT clientkey, coyname FROM

tells me there will be two columns and their names will be "clientkey" and "coyname".


For a more general case, I recommend that you look into odbc_fetch_array(), which will fetch the values from the database into a PHP associative array. So the count() of the row-return array is the number of columns and the column names are returned in the keys of the row-array:

while($row = odbc_fetch_array($queryexe)) 
{ 
    //format results 
    print '<tr>
              <td>' . $clientkey . '</td>
              <td>' . $coyname . '</td>
           </tr>'; 
}

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[scriggs]

Thanks for the reply.

I know in my example there are only 2 columns, but this will extend and vary, so I want to program it in for the future.

I will look into odbc_fetch_array and see what I can come up with.

 

[kenrbnsn]

To generalize the code posted above, do something like:

while($row = odbc_fetch_array($queryexe))
{
    $tmp = array();
    //format results
    $tmp[] = '<tr>';
    foreach ($row as $k=>$v)
      $tmp[] = '<td>' . $v . '</td>';
    $tmp[] = '</tr>';
    echo implode("\n",$tmp)."\n";
}

This will loop through each row printing all the fields. If you don't want one or two of the fields printed, just put an IF statement before the "$tmp[] = '<td>' . $v . '</td>';" line testing for the field name in the variable "$k".

If you need special formatting for certain columns, use a switch statement within the foreach() block.

Ken

 

[scriggs]

Ken

Thanks for the code which I have tried, but I get an error

Fatal error: Call to undefined function: odbc_fetch_array() in c:\inetpub\[URL unfurl="true"]wwwroot\php\feesmerge.php[/URL] on line 62

Any ideas why that would be?

 

[scriggs]

For interest (and comment if it is poor!?!) I have found a method that works for me:

 for($a=1;$a<=$num_cols;$a++)
 {
 echo odbc_field_name($queryexe, $a);
 }

 

[sleipnir214]

scriggs:
What version of PHP are you running?

Want the best answers? Ask the best questions!

TANSTAAFL!!