TOUGH - select table structure?

[Anon]

Writing an application with a user-definable table structure, IE the application will have to deal with tables of dynamic size and structure.

So the question is: without respect to the information IN the fields, is there a way to query the table structure directly?

Normally, "select * from table" results in an array passed back, with a key of the table name and the data field being the value stored.

Is there a way to query an EMPTY database to get JUST THE STRUCTURE of the table, IE field names and that's it?

-Ben

[Anon]

You didn't say which database, but assuming MySQL (since I don't speak postgres, and with something like Oracle you could just query the reference user_tables or whatever directly) there are several function used for this. Among them mysql_list_tables ( to find all the table names ) and mysql_list_fields (to find the fields in the table).

At any rate, give another read through the manual, these functions and others are very straigh forward and not tough at all.

[Anon]

The database in use is Postgres - but I don't want to use database-specific code if I can help it!

-Ben

[Anon]

The database in use is Postgres

This is easy, then.

but I don't want to use database-specific code if I can help it!

You can't help it, as the SQL standard does not specify any specific way to retrieve this information. You can--and certainly should--hide the specifics inside a function so that if you move databases all you have to change is the internals of that one function!

The best way to find out the queries needed to access the table information is to run psql with the -E switch, which will then show you the queries it is using for the various \d commands.

[Anon]

You can get close.

With postgresql, like mysql, there is a fetch_array type statement.

What you do is connect to the database, and run a single query of:

$result=pg_exec($conn,"select * from table limit 1;");

If there's at least one row in the database, then you can grab the names of the fields like so:

$array=pg_fetch_array($result,$row);

But if not, then you'll have to enumerate them one by one with the pg_numfields, pg_fieldtype, and pg_fieldname commands, like so:

for ($i=0;$i<pg_numfields($result);$i++){
print "Field $i name: ".pg_fieldname($result,$i);
print "Field $i type: ".pg_fieldtype($result,$i);
print "<BR>\n";

stuff them in an array:

$fields[$i][name]=pg_fieldname($result,$i);
$fields[$i][type]=pg_fieldtype($result,$i);
}

[Anon]

THANKS!

This is EXACTLY what I needed - just kicking myself for not coming up with this myself...

-Ben