[RESOLVED] Getting column names

phpSimon

Hi everyone

I have a table with 4 columns:

ID
Type
SubType
Index

The table may have new columns added in future. When I report from this table in a PHP page, how do I do it when I don't know how many columns there are, and what they're called?

Thank you

lilRachie

it's not widely liked because of possible security issues but

SELECT * FROM `tablename` WHERE `columnname` = '$x'";

lilRachie

phpSimon

Thanks

If I don't know what the columnnames are though, I can't set $x to anything.

Or have I missed the point?

lilRachie

you can do with out a column name but you need to know some for most things ... ie inserting and updating tables

phpSimon

OK, yes I'm going to need the column names themselves rather than just the data from them.

This ALMOST works.

SELECT column_name 
  FROM information_schema.columns 
 WHERE table_name = 'tableName'

However, there are 5 databases in the DBMS. Each schema is the same, so there are 5 tables called 'tablename' and I get the column headings returned multiple times.

Using SELECT DISTINCT won't work in this case, should the schema happen to change between the 5 databases.

I thought specifying the databasename would fix that:

... WHERE table_name = 'databaseName.tableName''

... but it doesn't.

Bonesnap

Check out mysql_field_name.

phpSimon

Perfect!

This way, I can also easily ignore position 0 since I always know what that is.

I think I can also use:

$numberfields = mysql_num_fields($result)

... to know how many fields have been returned in the query.

I'll test that. Thank you!

bradgrafelman

phpSimon;10999568 wrote:
This way, I can also easily ignore position 0 since I always know what that is.

How do you know that? What if the table schema changes in the future?

As for your actual issue, what's wrong with doing a SELECT * and then using [man]mysql_fetch_assoc/man to retrieve the rows? Not only would that give you the data at the same time, but it would also give you both the number of columns and their names.

Derokorian

Also your query was almost there:

SELECT column_name 
  FROM information_schema.columns 
WHERE table_name = 'tableName' 
  AND table_schema = 'databaseName'

phpSimon

Derokorian, thank you for fixing my query, that really helps (and will help others, I'm sure)

bradgrafelman, thanks also.

I know the first column wont change because these tables - if they change - will only be changed by me, using RazorSQL or phpMyAdmin.

Maybe I misunderstood mysql_field_name.

I thought I could do:

$query = "SELECT * FROM tableName";
$result = mysql_query($query);

// pseudo code - something like
for each (count(mysql_field_name($result)){
// ignore the first item
// print the column header
}

I hadn't really thought about how I'd read the data - as I wrote the pseudo code I realised that I'd probably have to reset the pointer or something.

I'll read up on mysq_fetch_assoc to see how to use it in this way.

Thanks again.

Weedpacket

Maybe I misunderstood mysql_field_name.

Yes; [man]mysql_field_name[/man] needs two arguments, not just one.

I'd go with the INFORMATION_SCHEMA lookup, seeing as it's the standard interface for this sort of thing.

nagaraju

hai I want to create image gallery in php by using code igniter by user session id based gallery .please anybody helpme

phpSimon

Thanks again; it's clear there's more than one approach (as usual!).