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

Question

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?

You might want to try fetching an associative array to keep column names as the keys of your array. Are you using PDO? `fetch(PDO::fetch_assoc)` — showdev, Feb 04 '13 at 21:33
@showdev I have gotten too much harrasment about mysql_* functions to use anything but ;) — Nick, Feb 04 '13 at 21:35
Look at the solution given at: http://stackoverflow.com/questions/1853094/how-to-get-the-columns-names-along-with-resultset-in-php-mysql — shacharsol, Feb 04 '13 at 21:37
I would advise against using $owner in query() like that, for security reasons.. if it's originating from user input, this is a textbook SQL injection.. — omercnet, Feb 04 '13 at 21:42
@omercnet i thought about that, but that variable is being passed from ajax via a js onclick event, no actual user input. does that still need sanitized? — Nick, Feb 04 '13 at 21:44
@JohnDoe yes of course, anything that comes from the browser can be manipulated, even though it's JS, your script still gets that input from a $_POST or $_GET which is controlled by the user — omercnet, Feb 04 '13 at 22:00
you should avoid using unnecessary queries.. please see solution by @showdev — omercnet, Feb 04 '13 at 22:01

score 3 · Accepted Answer · answered Feb 04 '13 at 21:34

3

Try:

SHOW COLUMNS FROM mytable

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

answered Feb 04 '13 at 21:34

Nir Alfasi

53,191
11
86
129

That gives me A LOT of information about the columns, any chance you know a way i can single out just the solumn names? – Nick Feb 04 '13 at 21:39
Figured out a solution to my first issue with this solution, posted the solution in my original question. – Nick Feb 04 '13 at 21:57
you should avoid using unnecessary queries.. please see solution by @showdev – omercnet Feb 04 '13 at 21:58
@omercnet I did try it, but it did not work for me in the end, just adding a new SQL query did. – Nick Feb 04 '13 at 22:01
In addition, this way made it easier to create my list. – Nick Feb 04 '13 at 22:05

score 3 · Answer 2 · edited Feb 04 '13 at 21:56

3

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

edited Feb 04 '13 at 21:56

omercnet

737
5
16

answered Feb 04 '13 at 21:41

showdev

28,454
37
55
73

1

great solution! just added parameterized query support for improved security against SQL injections – omercnet Feb 04 '13 at 21:54
Awesome, definitely a good idea. Look into `prepare` if you haven't already. – showdev Feb 04 '13 at 21:54
I edited your answer to include prepare, please approve the edit – omercnet Feb 04 '13 at 21:55

omercnet · Answer 3 · 2013-02-04T21:40:26.193

2

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.

edited Feb 04 '13 at 21:40

answered Feb 04 '13 at 21:34

omercnet

737
5
16

That was the first thing i thought of! but i would rather not use mysql_* functions... I'm trying to get out of that habbit and use PDO – Nick Feb 04 '13 at 21:41
@JohnDoe, omercnet said `mysqli` not `mysql`. There is nothing wrong with `mysqli`. It is recommended for new projects along with `PDO`. – Mike Feb 04 '13 at 22:00
@mike I think he wants to use PDO to enable support for multiple databases, and not just MySQL – omercnet Feb 04 '13 at 22:02
@Mike you're right, after hours of code, it all just start to blend together.. my bad :P – Nick Feb 04 '13 at 22:03
@omercnet, even though that's a valid reason to choose PDO, I don't see anything that would indicate that this was one of the influencing factors in this particular case. The question is tagged only with `mysql` and no other DBMS. Correct me if I'm wrong. – Mike Feb 04 '13 at 22:12

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

3 Answers3