Get column names from a MySQL table via PHP

Hi,
is there a way of producing a list of column names from a MySQL table via PHP.

CREATE TABLE IF NOT EXISTS `signup` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(60) default NULL,
  `email` varchar(100) default NULL,
  `phone` varchar(80) default NULL,
  `comments` text,
  `send` char(3) default 'Yes',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

for example:
id
name
email
phone
column
send

Thanks in advance for your help.
sabecsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, run the query DESCRIBE , the result set returned should give you the information
0
 
JoachimMartinsenCommented:
0
 
TILLCommented:
Here is something that we're using:

Select * from information_schema.COLUMNS where TABLE_NAME='table' and TABLE_SCHEMA='DATABASE_NAME';

After you run this query, all you have to do is to iterate through the results and extract the info you need.
The benefit of using this query instead of mysql_field_name is that you can collect additional information about the table structure (ex. Field description) which for instance we're using for labeling certain behaviors (call it Aspect Oriented Programming at database level).

If you need more info, I'll be glad to give you more insights on this.
            
0
 
Hube02Commented:
I use the following code snippet to get a list of fields and the data associated with each field: it builds an array with the table name as a subscript. This way I can get columns from more than one table and store them in the same array if I need to.



$query = 'SHOW COLUMNS FROM '.$table;
if ($results = mysql_query($query)) {
  $columns[$table] = array();
  if (mysql_num_rows($results) > 0) {
    while($row = mysql_fetch_row($results)) {
      $columns[$table][] = $row[0];
    }
  }
}

Open in new window

0
 
sabecsAuthor Commented:
Thanks for your help, I ended up using the following:
mysql_select_db($database_conn_data, $conn_data);
$query_rs_order= sprintf("SELECT * FROM orders where email='%s' LIMIT 1", $email);
$rs_order= mysql_query($query_rs_order, $conn_data) or die(mysql_error());
$row_rs_order= mysql_fetch_row($rs_order);
$numfields = mysql_num_fields($rs_order);
                                    for ($i=0; $i < $numfields; $i++) {
      echo mysql_field_name($rs_order, $i)."";
      } while ($row_rs_order= mysql_fetch_row($rs_order));
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.