Hi All,

I'm new to PHP4, but am enjoying some limited success integrating it with a MS-Access (ODBC) database.

I am creating a PHP function which will automatically analyze the columns in a given table and generate .php pages which allow HTML administration of the table (add, delete, edit DB records).

I'd like to make this as flexible as possible, and to this end, I want to query a specific table and retrieve the specific column information. When I code:

$allColumns = odbc_columns($db);

I get a database dump of ALL columns in ALL tables ($db is my database connection).

Here's what I don't understand: How do I use the optional parameters in the odbc_columns function to restrict the output to only one table? I've tried:

$allColumns = odbc_columns($db,"","","thistable","");

but I keep getting SQL errors:

SQL error: , SQL state 00000 in SQLColumns....

Can anyone please offer any insights? I'm sure this is possible....

Thanks,

michael geary

    3 months later

    $result_id = odbc_exec($db_id, "SELECT * FROM ". $TABLE );

    	if ($result_id) {

		$num_fields = odbc_num_fields( $result_id );

		for ($field_count = 1; $field_count <= $num_fields; $field_count++ )  {

				$arr_columns[] = new Column( odbc_field_name($result_id, $field_count), odbc_field_type($result_id, $field_count) );

		}

		odbc_free_result($result_id);
	} else {
		echo "<HR> Column info not retrievable <HR>";
	}

	odbc_close($db_id);

    /*
    odbc_columns not supported on MySQL via ODBC, this however works on Access & MySQL via ODBC.

    Same as using fields collection in ADO.

    Meta information retrieved via cursor engine.
    */

      5 months later

      Hi Bart,

      I don't work with databases too much.

      I'm just wondering if "SELECT *" will build a view of the result set in memory before the rows are fetched or if it will build the rows your want when you fetch them.

      I'm just concerned about using the solution you posted to the odbc_columns() problem if it has to build the entire result set before you query for the column names.

      Thanks,
      Andrew

        18 days later

        Andrew,

        Sorry for the late reply.

        The trick is to select no data, an empty resultset.

        There are 2 ways to do that.

        1. Select on a primary key for a value that doesn't exist.

        Drawback is: you have to know the PKs Name.

        1. Add a "LIMIT 0" to your select statement

        see http://www.mysql.com/doc/S/E/SELECT.html

        This is like adding a WHERE ROWNUM < 0 in Oracle. That is what i would do. LIMIT would seem to do the same on Mysql.

        Hope this note is usefull,

        Bye,

        Bart Debersaques,

          Write a Reply...