Get Only column names as in array mysql

Question

I am new in both php and mysql. I want to create a dynamic table that will have all fields from table in mysql database. I am trying to get all column names in an array but fail to do. I am trying following code:

<?php
    $fields = mysql_query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'mytablename' ");
    $res = mysql_fetch_array($fields);
    foreach ($res as $field)
    {
        echo '<tr>';
        echo '<td>';
        echo $field;
        echo '</td>';
        echo '<td>';
        echo "<input type='text' class='' name='tags' id='tags' value=''>";
        echo '</td>';
        echo '</tr>';
    }
    ?>

Any assistance would be appreciated.

Hypochondrium

And what do you get instead? – Prosecutor

use print_r($fields) to debug!... a very useful command in php debugging – Bedmate

FYI, mysql_* is deprecated you should be using mysqli or PDO. Changing from mysql_ to PDO – Enzyme

I am getting Applied Mathematics Field 1 Name, Field 1 Name, , int(3), int(3), NO, NO and textfield in front of each value – Hypochondrium

Answer 1

You dont need an additional SQL query just to get your field names. You can use your normale SELECT query and just get your field names (and definition) from that query. Better performance this way!

Deprecated MySQL Solution:

The MySQL Library is deprecated. It can be used as in this link, btu you should switch to the mysqli Library which is nearly identical when used proceduraly (second sample).

htttp://www.php.net/manual/en/function.mysql-field-name.php

The OOP MySQLi Solution:

$query = "SELECT Name, SurfaceArea from Country ORDER BY Code LIMIT 5";

if ($result = $mysqli->query($query)) {
/* Get field information for all columns */
    while ($finfo = $result->fetch_field()) {
        printf("Name:     %s\n", $finfo->name);
        printf("Table:    %s\n", $finfo->table);
        printf("max. Len: %d\n", $finfo->max_length);
        printf("Flags:    %d\n", $finfo->flags);
        printf("Type:     %d\n\n", $finfo->type);
    }
    $result->close();
}    

The Procedural MySQLi Solution:

$query = "SELECT Name, SurfaceArea from Country ORDER BY Code LIMIT 5";
if ($result = mysqli_query($link, $query)) {
    /* Get field information for all fields */
    while ($finfo = mysqli_fetch_field($result)) {
        printf("Name:     %s\n", $finfo->name);
        printf("Table:    %s\n", $finfo->table);
        printf("max. Len: %d\n", $finfo->max_length);
        printf("Flags:    %d\n", $finfo->flags);
        printf("Type:     %d\n\n", $finfo->type);
    }
    mysqli_free_result($result);
}

http://www.php.net/manual/en/mysqli-result.fetch-field.php

Czardas

Please do not suggest mysql_* library functions. They're deprecated. – Electrocardiogram

Yep, i added the newer mysqli version. Tnx for notice. – Czardas

Answer 2

Use this:

echo $field['COLUMN_NAME'];

instead of $field

This will give the column name.

After executing the query, query returns these fields:

           TABLE_CATALOG: def
            TABLE_SCHEMA: schema_name
              TABLE_NAME: table_name
             COLUMN_NAME: column_name /*use this column name*/
        ORDINAL_POSITION: 49
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 255
  CHARACTER_OCTET_LENGTH: 765
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
             COLUMN_TYPE: varchar(255)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 

Nightdress

@user2806222 use the fetch_assoc and while instead of foreach. then try it. – Jehanna

Answer 3

Please note that this code uses the mysqli_* php library, because the legacy mysql_* library is deprecated and should never be used.

This might do the trick

<?php
            $table = 'tableName';
            $query = "SHOW COLUMNS FROM $table";
            if($output = mysqli_query($query)):
                $columns = array();
                while($result = mysqli_fetch_assoc($output)):
                    $columns[] = $result['Field'];
                endwhile;
            endif;
            echo '<pre>';
            print_r($columns);
            echo '</pre>';
    ?>

Bedmate

-1: NEVER; EVER suggest questions using deprecated libraries. mysql_* lib is deprecated in PHP, even if the author is using that library. At least put a disclaimer. – Electrocardiogram

Updated partially ;) check it again – Electrocardiogram

I'm happy to remove my -1 now. I've added a small disclaimer on top, just to warn blind copy-pasters. – Electrocardiogram

@STTLCU thanks for the birds' eye view, it helped me and hopefully the poster of the question. God bless You – Bedmate

Is there a way of making this work without mysqlnd. mysqli_fetch_assoc only works with mysqlnd? – Talley

Answer 4

Use:

<?php
$fields = mysql_query("SHOW columns FROM mytablename");
while($row = mysql_fetch_array($fields))
{
    echo '<tr>';
    echo '<td>';
    echo $row["Field"];
    echo '</td>';
    echo '<td>';
    echo "<input type='text' class='' name='tags' id='tags' value=''>";
    echo '</td>';
    echo '</tr>';
}
?>

Mordant

It worked Salim, Thanks to all for your help. Really Appreciate it. Reg, – Hypochondrium

mysql_* functions are deprecated. Please do not follow this answer. – Electrocardiogram

mysql_* is dangerous but it's still widely used. We should try to make PHP developing a better place, and everything, even little, must be done. – Electrocardiogram

Thanks @STTLCU, What should I use in place of mysql_query and mysql_fetch_array? – Hypochondrium

@user2806222 Refactor your code to use PDO library. You'll never regret it, I swear. start here: php.net/manual/en/book.pdo.php – Electrocardiogram

Answer 5

Try the following function:

function mysql_field_array($query) {

    $field = mysql_num_fields($query);

    for ($i = 0; $i < $field; $i++) {
        $names[] = mysql_field_name($query, $i);
    }
    return $names;
}

Ezequieleziechiele

Answer 6

You can do something like this instead with PDO:

var_dump(array_keys($dbh->query('SELECT * FROM `mytablename` LIMIT 1')->fetch(PDO::FETCH_ASSOC)));

Prosecutor

Answer 7

Using the PDO library to create a table row with field names:

$db = new PDO('mysql:host=localhost;dbname=databasename','user','password');

echo "<tr>";
$query = $db->prepare("SHOW COLUMNS FROM `table`");
$query->execute();
$fields = array();
while($result = $query->fetch(PDO::FETCH_ASSOC)){
    echo "<td style='font-weight:bold; padding:5px'>$result[Field]</td>";
    $fields[] = $result['Field'];
}
echo "</tr>\n";

Australopithecus

Answer 8

PHP mysqli has mysqli_fetch_fields which returns an object with all the fields data (name, type, length), just iterate that object and put the name value into your array.

$result = mysqli_query($conn , $sql)
$fieldinfo = mysqli_fetch_fields($result);
$fields = [];
foreach ($fieldinfo as $val) //iterate Object
        $fields[] = $val->name; //populate array
print_r($fields);

Devoice