CODEWITHSUNDEEP
phpmysql
Sunil
Sunil

Reputation: 237

Get Only column names as in array mysql

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.

Upvotes: 6

Views: 22644

Answers (8)

lisandro
lisandro

Reputation: 506

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);

Upvotes: 0

Bob Brunius
Bob Brunius

Reputation: 1380

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";

Upvotes: 0

Wolfack
Wolfack

Reputation: 2769

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;
}

Upvotes: 1

ToBe
ToBe

Reputation: 2681

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

Upvotes: 5

Digital Alchemist
Digital Alchemist

Reputation: 2332

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>';
    ?>

Upvotes: 1

MD SHAHIDUL ISLAM
MD SHAHIDUL ISLAM

Reputation: 14523

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>';
}
?>

Upvotes: 1

silkfire
silkfire

Reputation: 26033

You can do something like this instead with PDO:

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

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15593

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:

Upvotes: 2

Related Questions