php pdo: get the columns name of a table
Do you want the column names in a table, or the column names in the record set from a query? These are two different things done in two different ways. Phil’s answer does the former, JapanPro’s answer does the latter!
@charles: I think I only want to get the column names in a table. I don’t quite understand what u mean by column names in the record set from a query. but see my answer in my edit above. thanks.
@lauthiamkok: You’re doing that in your updated post — you’re making a query, which returns a set of records, then you’re grabbing the column names for that specific set. It just happens that the column names in your result set are identical to those in your table. Remember, SQL columns can be aliased.
@lauthiamkok, if you were trying to get the column names from a table, then @JapanPro’s answer below is the best way to do that — using the information_schema method. What you are doing does the job but isn’t the «right» way.
when you have an empty table your method fails, because there’s no records to fetch the columns from.
14 Answers 14
I solve the problem the following way (MySQL only)
$table_fields = $dbh->query("DESCRIBE tablename")->fetchAll(PDO::FETCH_COLUMN); IMHO, while this answer may be useful to many, it doesn’t match the need expressed in the question, i.e. «Is there a way to retrieve the column name from the rowset array while I’m iterating data?» The user wants to see the key in the array while iterating values. The key($array) function may help.
This will work for MySQL, Postgres, and probably any other PDO driver that uses the LIMIT clause.
Notice LIMIT 0 is added for improved performance:
$rs = $db->query('SELECT * FROM my_table LIMIT 0'); for ($i = 0; $i < $rs->columnCount(); $i++) < $col = $rs->getColumnMeta($i); $columns[] = $col['name']; > print_r($columns); Thanks Will! Hello reader, that is the generic PDO solution! The Lauer solution only supported by MySQL — and PDOStatement::getColumnMeta() is complex, is not supported for all PDO drivers, and is listed as experimental. PS: the query runs also with empty tables.
Perhaps simplify with something like $columns = array_keys($rs->fetchAll(PDO::FETCH_ASSOC)); . I not try.
@DaviddCeFreitas, sorry the method getColumnMeta have a 6+ years old «is EXPERIMENTAL» alert. The only way is using a non-empty table. You can use something like SELECT * FROM my_table WHERE > and populate all tables with a no-data row.
$result = $db->query('select * from table limit 1'); $fields = array_keys($result->fetch(PDO::FETCH_ASSOC)); And you will get the column names as an array in the var $fields.
This produces a Warning if the table is empty! ===> «Warning: array_keys() expects parameter 1 to be array, bool given in. «
function getColumnNames($pdo, $table) < $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = ?"; $stmt = $pdo->prepare($sql); $stmt->execute([$table]); return $stmt->fetchAll(PDO::FETCH_COLUMN); > @Charles Isn’t information_schema MySQL specific? This question isn’t tagged MySQL so it could be anything. Mind you, the method in my answer probably doesn’t work on every DB either 😉
@Phil, information_schema is part of the ANSI standard. It’s been implemented in MySQL, PostgreSQL and MSSQL. Unless the OP is using Oracle or SQLite, it should work fine for him (assuming this answer was what he wanted).
@PawełStawarz there is a TABLE_SCHEMA column that indicates what schema the table and column belong to. However, the downsides to using information_schema is that it is possible not all users/accounts will have access to this system table. This is also slower than getColumnMeta().
Here is the function I use. Created based on @Lauer answer above and some other resources:
//Get Columns function getColumns($tablenames) < global $hostname , $dbnames, $username, $password; try < $condb = new PDO("mysql:host=$hostname;dbname=$dbnames", $username, $password); //debug connection $condb->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $condb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // get column names $query = $condb->prepare("DESCRIBE $tablenames"); $query->execute(); $table_names = $query->fetchAll(PDO::FETCH_COLUMN); return $table_names; //Close connection $condb = null; > catch(PDOExcepetion $e) < echo $e->getMessage(); > > Usage Example:
$columns = getColumns(‘name_of_table’); // OR getColumns($name_of_table); if you are using variable.
This is an old question but here’s my input
function getColumns($dbhandle, $tableName) < $columnsquery = $dbhandle->query("PRAGMA table_info($tableName)"); $columns = array(); foreach ($columnsquery as $k) < $columns[] = $k['name']; >return $columns; > just put your variable for your pdo object and the tablename. Works for me
This approach works for me in SQLite and MySQL. It may work with others, please let me know your experience.
$calendarDatabase = new \PDO('sqlite:calendar-of-tasks.db'); $statement = $calendarDatabase->query('SELECT *, COUNT(*) FROM data LIMIT 1'); $columns = array_keys($statement->fetch(PDO::FETCH_ASSOC)); array_pop($columns); var_dump($columns); I make no guarantees that this is valid SQL per ANSI or other, but it works for me.
As Charle’s mentioned, this is a statement method, meaning it fetches the column data from a prepared statement (query).
I needed this and made a simple function to get this done.
function getQueryColumns($q, $pdo)< $stmt = $pdo->prepare($q); $stmt->execute(); $colCount = $stmt->columnCount(); $return = array(); for($i=0;$i<$colCount;$i++)< $meta = $stmt->getColumnMeta($i); $return[] = $meta['name']; > return $return; > A very useful solution here for SQLite3. Because the OP does not indicate MySQL specifically and there was a failed attempt to use some solutions on SQLite.
$table_name = 'content_containers'; $container_result = $connect->query("PRAGMA table_info(" . $table_name . ")"); $container_result->setFetchMode(PDO::FETCH_ASSOC); foreach ($container_result as $conkey => $convalue) This returns an array. Since this is a direct information dump you’ll need to iterate over and filter the results to get something like this:
Array ( [ccid] => ccid [administration_title] => administration_title [content_type_id] => content_type_id [author_id] => author_id [date_created] => date_created [language_id] => language_id [publish_date] => publish_date [status] => status [relationship_ccid] => relationship_ccid [url_alias] => url_alias ) This is particularly nice to have when the table is empty.
How can I get columns name from select query in php?
How can I know which columns to select? «I am currently designing a site for the execute query by users. So when the user executes this query, I won’t know which columns selected. But when I want to show the results and draw a table for the user I should know which columns selected.»
SELECT name_of_yourcolumn FROM table OR .. SELECT * (as in everything) FROM table, or if you really want the column names, do SHOW COLUMNS FROM table
@AndréCatita It looks like the OP knows SQL syntax. I think instead he is asking how to discover the database schema.
@Farshad Your latest edit has confused me. Are you designing a site that accepts arbitrary SQL queries from users, like sqlfiddle.com? If so, the user’s query already contains all the information you need.
7 Answers 7
For unknown query fields, you can just use this code.
It gives you every row fields name=>data. You can even change the key to » to get ordered array columns by num following the columns’ order in the database.
$data = array(); while($row = mysql_fetch_assoc($query)) < foreach($row as $key =>$value) < $data[$row['id']][$key] = $value; >> print_r($data); First, understand exactly what data you want to retrieve. Then look at the database schema to find out which tables the database contains, and which columns the tables contain.
The following query returns a result set of every column of every table in the database:
SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS; In this sqlfiddle, it returns the following result set (truncated here for brevity):
TABLE_NAME COLUMN_NAME ----------------------- CHARACTER_SETS CHARACTER_SET_NAME CHARACTER_SETS DEFAULT_COLLATE_NAME CHARACTER_SETS DESCRIPTION CHARACTER_SETS MAXLEN COLLATIONS COLLATION_NAME COLLATIONS CHARACTER_SET_NAME COLLATIONS ID COLLATIONS IS_DEFAULT COLLATIONS IS_COMPILED COLLATIONS SORTLEN Now I know that I can select the column CHARACTER_SET_NAME from the table CHARACTER_SETS like this:
SELECT CHARACTER_SET_NAME FROM CHARACTER_SETS; Use mysqli::query to execute these queries.
Obtain column names from MySQL query object (PHP)
Currently, I’m storing MySQL query results in an array of objects, where each object represents a row that matches the query. Later, I run through two foreach loops to extract the results — in the example below, they are outputted as a dynamically-generated HTML table. My question: Is it possible to obtain the column names from the query result object? My goal is to be able to dynamically generate the table headings, which my HTML table is currently lacking. Thanks!
$data = array(); $result = db_query("SELECT column1, column2 FROM table"); while ($obj= db_fetch_object($result)) < array_push($data, $obj); >$ret = ""; foreach ($data as $row) < $ret .= ""; foreach ($row as $field) < $ret .= "$field "; > $ret .= " "; > $ret .= "
"; return $ret; 3 Answers 3
There is function to retreive column name. Please check with this link :
Thanks for your response. It looks like the database has to be queried twice using the above-mentioned method — once to get the rows and once to the column information. Ideally, I’d like to query the database only once.
not really, how did you came to this?? by this method you can fetch additional info from a query that already ran.
Yes, you are right — I apologize for misunderstanding the documentation. The column information is extracted from the query result object that is returned. In effect, the database is only queried once to obtain the query result object. Thanks!
Get table column names in MySQL?
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'; SHOW COLUMNS FROM my_table; Or to get column names with comma in a line:
SELECT group_concat(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table'; I would vote for the Information_Schema version, since this syntax is supported by most major databases. Best to only learn 1 way if you have to.
+1 I wanted to select table names where a column exist I did SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘db_name’ AND COLUMN_NAME = ‘col_name’;
DESC for describe can be easily confused with DESC for descending. The nominal amount of bytes you save for the lack of readability is not worth it.
The following SQL statements are nearly equivalent:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild'] SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE 'wild'] In my quick test, SHOW COLUMNS returns a table containing the column names, types, etc, while SELECT COLUMN NAME returns just the column names.
I made a PDO function which returns all the column names in an simple array.
public function getColumnNames($table)< $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = :table"; try < $core = Core::getInstance(); $stmt = $core->dbh->prepare($sql); $stmt->bindValue(':table', $table, PDO::PARAM_STR); $stmt->execute(); $output = array(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) < $output[] = $row['COLUMN_NAME']; >return $output; > catch(PDOException $pe) < trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR); > > The output will be an array:
Array ( [0] => id [1] => name [2] => email [3] => shoe_size [4] => likes . ) Sorry for the necro but I like my function 😉
P.S. I have not included the class Core but you can use your own class.. D.S.