CODEWITHSUNDEEP
phpmysql
Nick
Nick

Reputation: 639

How to can I get the names of my mysql table columns

RESOLVED I have used the answer from alfasin, but as that gave me WAY too much information, i wrote a little script to just get the field names. As the field names apeared first, it was rather simple:

  $here = array();
  $SQL = "SHOW COLUMNS FROM User";
    foreach($conn->query($SQL) as $row) {
      $here[] = $row[0];
    }
  echo '<pre>';print_r($here);echo '<pre>';

This left me with the new array $here containing the column names, hope this helps someone in the future :)

Original question:
Let me clarify a bit, I have a mysql table and I'm trying to select * from it, and display the result in an html list <ol>. I can manage to grab the row data JUST FINE, but I cannot for the life of me figure out how to grab the table column names, in order to match them up with the row, respectively. this is my code that is grabbing the row data:

//get those results
 $sql = "SELECT DISTINCT *
 FROM User
 WHERE Owner = '".$owner."'";
  foreach($conn->query($sql) as $row) {
  //split array in half
  $hax = count($row);
  $halfHax = $hax / 2;
   //set up a for loop to give results
    $u = 1;
      for($i = 2; $i <= $halfHax; $i++){
        echo $row[$u].'<br>';
        $u++;
      }
   }

this is giving me all the result where Owner == $owner just like it should, but I would like the column names to list with those, I could hard-code it out, but more columns may be added/changed so I would rather not. Any ideas?

Upvotes: 5

Views: 229

Answers (3)

showdev
showdev

Reputation: 29188

Fetch rows as associative arrays in order to keep your column names as array keys. Here's my suggestion:

$sql = "SELECT DISTINCT * FROM User WHERE Owner = :owner";
$sth = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':owner' => $owner);

while($row=$sth->fetch(PDO::fetch_assoc) as $row) {
  //split array in half
  $hax = count($row);
  $halfHax = $hax / 2;
  //set up a for loop to give results
  foreach ($row as $key => $value) {
    echo $key.'='.$value.'<br />';
  }

}

To just list the column names:

array_keys($row);

Upvotes: 3

omercnet
omercnet

Reputation: 737

Please refer to SHOW COLUMNS at the MySQL Reference if you want more information about the columns.

But I'd suggest using mysqli_fetch_assoc and then using foreach (array_expression as $key => $value) to get the column name and it's value, for each row.

Upvotes: 2

Nir Alfasi
Nir Alfasi

Reputation: 53565

Try:

SHOW COLUMNS FROM mytable

http://dev.mysql.com/doc/refman/5.0/en/show-columns.html

Upvotes: 3

Related Questions