Display COLUMN NAMES from database through PHP

Question

I am trying to pull all the column names from my database 'settings' and list them in PHP.

A bit of research has come up with the following:

SHOW COLUMNS FROM settings

but I don't know how to go about listing these off through PHP. it would be helpful if any code posted was a prepared statement format, but not required.

Here is my PHP, it's giving me this error:

Notice: Undefined index: sThumbWidth in /home/demomycm/public_html/subdomains/mercury/eshop/library/classes/class.settings.php on line 204

for every field in my database.

$loadColumns = array();

    $query = "SHOW COLUMNS FROM settings";
    $result = $this->glob->dbConn->query($query);
    if($this->glob->dbConn->errno) {
        trigger_error('An error occurred whilst loading counties from the database.' . PHP_EOL . 'Query: ' . $query . PHP_EOL . 'Error[' . $this->glob->dbConn->errno . ']: ' . $this->glob->dbConn->error, E_USER_WARNING);
    } elseif($result->num_rows) {
        while($row = $result->fetch_assoc()) {
            $loadColumns[$row['Field']];
        }
        return $loadColumns;
    }

$loadColumnsHtml = '';
    $loadColumns = $this->Settings->LoadColumns();
    foreach($loadColumns as $i => $field) {
        $loadColumnsHtml .= '<div class="home-stat-small-link">' . ($i + 1) . '. <strong>' . $field['Field'] . '</strong>.</div>';
    }

you'd echo it out exactly as you would any db select statement the listing code is identical — Dave
– Dave, Commented Mar 21, 2014 at 12:55
You can execute and fetch rows from that query exactly as is from PHP, just as you would any other query. Look at the column names returned. Field is what you need. — Michael Berkowski
– Michael Berkowski, Commented Mar 21, 2014 at 12:55
Make DB Connection and get show values using echo function in PHP — Anand Solanki
– Anand Solanki, Commented Mar 21, 2014 at 12:56
You didnt' assign anything here: $loadColumns[$row['Field']];. Do you mean to do $loadColumns[$row['Field']] = $row['Field']; or $loadColumns[] = $row['Field']; or similar? — Michael Berkowski
– Michael Berkowski, Commented Mar 21, 2014 at 13:05

i'm PosSible · Accepted Answer · 2014-03-21 13:10:50Z

1

use describe

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

answered Mar 21, 2014 at 13:10

i'm PosSible

1,3932 gold badges11 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tucker · Accepted Answer · 2014-03-21 13:06:24Z

In your code you have $loadColumns as an array.
This line does nothing: $loadColumns[$row['Field']];
Basically your getting the value in $loadColumns with the key associated with $row['Field'] and not assigning anything.

You want something like this perhaps for that line:

array_push($loadColumns,$row['Field']);

Full code below

$loadColumns = array();

    $query = "SHOW COLUMNS FROM settings";
    $result = $this->glob->dbConn->query($query);
    if($this->glob->dbConn->errno) {
        trigger_error('An error occurred whilst loading counties from the database.' . PHP_EOL . 'Query: ' . $query . PHP_EOL . 'Error[' . $this->glob->dbConn->errno . ']: ' . $this->glob->dbConn->error, E_USER_WARNING);
    } elseif($result->num_rows) {
        while($row = $result->fetch_assoc()) {
           array_push($loadColumns,$row['Field']);  //<-- this line has been changed
        }
        return $loadColumns;
    }

$loadColumnsHtml = '';
    $loadColumns = $this->Settings->LoadColumns();
    foreach($loadColumns as $i => $field) {
        $loadColumnsHtml .= '<div class="home-stat-small-link">' . ($i + 1) . '. <strong>' . $field['Field'] . '</strong>.</div>';
    }

Simon · Accepted Answer · 2014-03-21 13:08:34Z

0

On MySQL connection (deprecated) you can use:

mysql_list_fields

On PDO (recommend) use:

$query = $dbh->prepare("DESCRIBE tablename");
$query->execute();
$tableFields = $query->fetchAll(PDO::FETCH_COLUMN);

On PDO you can also use PDOStatement::getColumnMeta(). But it has the status: EXPERIMENTAL

answered Mar 21, 2014 at 13:08

Simon

5,0143 gold badges57 silver badges95 bronze badges

Collectives™ on Stack Overflow

Display COLUMN NAMES from database through PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related