How to extract column names and and entires on php from mysql with column names displaying only once

Question

I am trying to extract a table from mysql in php with the column names to be used as headers and the values in the column to be displayed as values.

| id | firstname | lastname |
|  1 | Joe       | Jones    |
|  2 | Cal       | Clark    |
|  3 | Rob       | Robin    |

problem is i am getting back

| id | firstname | lastname |
|  1 | Joe       | Jones    |
| id | firstname | lastname |
|  2 | Cal       | Clark    |
| id | firstname | lastname |
|  3 | Rob       | Robin    |

please help with what i am doing wrong.

P.S I am quite new to programming also don't mind the poor html.

$query = "SELECT * ";
$query .= "FROM {$selected_table} ";
$query .= "LIMIT 0, 30";

$confirmed_query = confirm_query($query);
$query = mysql_query($confirmed_query);

echo "<table>";
while ($query_result = mysql_fetch_assoc($query)) {
    echo "<tr>";
    foreach ($query_result as $columns => $rows) {
        echo "<th>{$columns}</th>";
    }
    echo "</tr><tr>";
    foreach ($query_result as $colums => $rows) {
        echo "<td>$rows</td>";
    }
    echo "</tr>";

}
echo "</table>";

is there any other way to get the column names out of the array with out using a foreach which will cause it to return the column names for each record in the array?

Answer 1

This is a job for do...while!

$query_result = mysql_fetch_assoc($query);
if ($query_result) {
  echo "<table>";
  echo "<tr>";
  foreach ($query_result as $columns => $rows) {
      echo "<th>{$columns}</th>";
  }
  echo "</tr>";
  do {
    echo "<tr>";
    foreach ($query_result as $colums => $rows) {
        echo "<td>$rows</td>";
    }
    echo "</tr>";
  } while ($query_result = mysql_fetch_assoc($query));
  echo "</table>";
}

But I also give +1 to @outis's answer because we should all be using PDO -- not the antiquated and inferior mysql extension.

Answer 2

Move the table header section outside of your while loop.

Answer 3

You have to write the table headers only once and not for each record. Use something like this:

$headerWritten = false;

echo "<table>";
while ($query_result = mysql_fetch_assoc($query)) {
    // Write header
    if(!$headerWritten) {
        echo "<tr>";
        foreach ($query_result as $columns => $rows) {
            echo "<th>{$columns}</th>";
        }
        echo "</tr>";

        $headerWritten = true;
    }

    // Write rows
    echo "<tr>";
    foreach ($query_result as $colums => $rows) {
        echo "<td>$rows</td>";
    }
    echo "</tr>";

}
echo "</table>";

Answer 4

Use PDO and PDOStatement::getColumnMeta to get the column names before the loop.

A full solution is a little involved, as it should also separate data access and display. The following is intended more as an illustration rather than anything that should be used in production code.

<?php
abstract class Model {
    abstract function fields();
    function __get($name) {
        /* Won't work with overloaded methods (`__call`) by design. Accessing
           non-nullary methods as properties will cause warnings. To prevent this
           at a time-cost, use ReflectionMethod to check the arity of the method.
         */
        if (method_exists($this, $name)) {
            return $this->$name();
        }
    }
    ...
}

class ModelCollection extends Model {
    // use the delegate pattern, rather than inheritance
    protected $_delegate, $_fields;

    function __construct($result) {
        $this->_delegate = $result;
    }

    function __call($name, $args) {
        if (is_callable($this->_delegate, $name)) {
            return call_user_func_array(array($this->_delegate, $name), $args);
        } else {
            $delegateClass = get_class($this->_delegate);
            $myClass = get_class($this);
            throw new BadMethodCallException("Method {$delegateClass}::{$name} (called as {$myClass}::{$name}) doesn't exist.");
        }
    }

    /* Here's where we get the column names */
    function fields() {
        if (is_null($this->_fields)) {
            $columnCount = $this->_delegate->columnCount();
            for ($i=0; $i < $columnCount; ++$i) {
                $this->_fields[] = $this->_delegate->getColumnMeta($i);
                // perhaps massage data into some other format
            }
        }
        return $this->_fields;
    }
}

abstract class View {
    public $data;

    abstract function display();
    ...
}

class TableView extends View {
    function display() {
        ?>
        <table>
          <?php 
           $this->_display_head();
           $this->_display_body();
          ?>
        </table>
        <?php
    }
    /* Here's the first place we use the column names */
    protected function _display_head() {
        ?>
          <thead>
            <tr>
              <?php foreach ($this->data->fields as $fields) { ?>
                <th><?php echo $fields['name'] ?></th>
              <?php } ?>
            </tr>
          </thead>
        <?php
    }
    protected function _display_body() {
        ?>
          <tbody>
            <?php
             foreach ($this->data as $row) {
                $this->_display_row($row);
             }
            ?>
          </tbody>
        <?php
    }
    /* Here's the second place we use the column names */
    protected function _display_row(&$row) {
        ?>
            <tr>
              <?php foreach ($this->data->fields as $fields) { ?>
                <th><?php echo $row[$fields['name']] ?></th>
              <?php } ?>
             </tr>
        <?php
    }
}