PHP/mySQL get all column names and create list

Question

I'm using this to define the valid keys that can be used to perform a search on my front end:

$validKeys = array('gender','marital_status', 'age');

The rest of my code works great in that it only accepts keys sent to it in an AJAX call that are in this array.

However, I want to be able to make this list ('gender','marital_status', 'age') dynamic. That is, I'd like to include all of the columns from my table in here so that every column is essentially a valid key. I have about 10-15 depending on the table and I'd rather not hard-code it into each PHP file.

How would I go about getting all the column names in my table and arranging them into this variable? So, I need to get all the names, put them in single quotes and comma separate. Unless there's a way to get them in a proper array and skip the array part of defining the $validkeys variable.

If I var_dump $validKeys, I get this:

array(5) {
  [0]=>
  string(6) "gender"
  [1]=>
  string(14) "marital_status"
  [2]=>
  string(3) "age"
  [3]=>
  string(7) "anglers"
  [4]=>
  string(8) "baseball"
}

Any direction would be appreciated.

EDIT: Should have been more explicit that I am using PDO.

score 2 · Answer 1 · answered Jan 15 '15 at 13:34

2

you can try with mysqli's fetch_fields-function.

$finfo = $result->fetch_fields();

Documentation: http://php.net/manual/de/mysqli-result.fetch-fields.php

answered Jan 15 '15 at 13:34

cari

2,251
2
18
27

score 1 · Accepted Answer · answered Jan 15 '15 at 13:33

1

Run a query using the DESCRIBE syntax. That will return all columns to you which you can then place in the array.

DESCRIBE `table_name`;

Another option is SHOW COLUMNS. Either will work for your requirements.

answered Jan 15 '15 at 13:33

Jay Blanchard

34,243
16
77
119

score 1 · Answer 3 · edited May 23 '17 at 10:25

I should have been a bit more explicit that I am using PDO, so Jay's answer above pointed me in the right direction and I found this answer that gave me the details: https://stackoverflow.com/a/7091984/989722

The full code snippet based on my question looks like this:

$q = $dbh->prepare("DESCRIBE full_db2");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
$validKeys = $table_fields;

PHP/mySQL get all column names and create list

3 Answers3