MySQL query to get column names?

Question

I'd like to get all of a mysql table's col names into an array in php?

Is there a query for this?

Answer 1

I have done this in the past.

SELECT column_name
FROM information_schema.columns
WHERE table_name='insert table name here'; 

Answer 2

This question is old, but I got here looking for a way to find a given query its field names in a dynamic way (not necessarily only the fields of a table). And since people keep pointing this as the answer for that given task in other related questions, I'm sharing the way I found it can be done, using Gavin Simpson's tips:

//Function to generate a HTML table from a SQL query
function myTable($obConn,$sql)
{
    $rsResult = mysqli_query($obConn, $sql) or die(mysqli_error($obConn));
    if(mysqli_num_rows($rsResult)>0)
    {
        //We start with header. >>>Here we retrieve the field names<<<
        echo "<table width=\"100%\" border=\"0\" cellspacing=\"2\" cellpadding=\"0\"><tr align=\"center\" bgcolor=\"#CCCCCC\">";
        $i = 0;
        while ($i < mysqli_num_fields($rsResult)){
           $field = mysqli_fetch_field_direct($rsResult, $i);
           $fieldName=$field->name;
           echo "<td><strong>$fieldName</strong></td>";
           $i = $i + 1;
        }
        echo "</tr>"; 
        //>>>Field names retrieved<<<

        //We dump info
        $bolWhite=true;
        while ($row = mysqli_fetch_assoc($rsResult)) {
            echo $bolWhite ? "<tr bgcolor=\"#CCCCCC\">" : "<tr bgcolor=\"#FFF\">";
            $bolWhite=!$bolWhite;
            foreach($row as $data) {
                echo "<td>$data</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }
}

This can be easily modded to insert the field names in an array.

Using a simple: $sql="SELECT * FROM myTable LIMIT 1" can give you the fields of any table, without needing to use SHOW COLUMNS or any extra php module, if needed (removing the data dump part).

Hopefully this helps someone else.

Answer 3

i no expert, but this works for me..

$sql = "desc MyTable";
$result = @mysql_query($sql);
while($row = @mysql_fetch_array($result)){
    echo $row[0]."<br>"; // returns the first column of array. in this case Field

      // the below code will return a full array-> Field,Type,Null,Key,Default,Extra    
      // for ($c=0;$c<sizeof($row);$c++){echo @$row[$c]."<br>";}    

}

Answer 4

The simplest solution out of all Answers:

DESC `table name`

or

DESCRIBE `table name`

or

SHOW COLUMNS FROM `table name`

Answer 5

Seems there are 2 ways:

DESCRIBE `tablename`

or

SHOW COLUMNS FROM `tablename`

More on DESCRIBE here: http://dev.mysql.com/doc/refman/5.0/en/describe.html

Answer 6

Try this one out I personally use it:

SHOW COLUMNS FROM $table where field REGEXP 'stock_id|drug_name'