php pdo：获取表的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5428262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
php pdo: get the columns name of a table
提问by laukok
How can I get all the column names from a table using PDO?
如何使用 PDO 从表中获取所有列名？
id name age
1 Alan 35
2 Alex 52
3 Amy 15
The info that I want to get are,
我想得到的信息是，
id name age
EDIT:
编辑：
Here is my attempt,
这是我的尝试，
$db = $connection->get_connection();
$select = $db->query('SELECT * FROM contacts');
$total_column = $select->columnCount();
var_dump($total_column);
for ($counter = 0; $counter < $total_column; $counter ++) {
$meta = $select->getColumnMeta($counter);
$column[] = $meta['name'];
}
print_r($column);
Then I get,
然后我得到，
Array
(
[0] => id
[1] => name
[2] => age
...
)
回答by Jesper Grann Laursen
I solve the problem the following way (MySQL only)
我通过以下方式解决问题（仅限 MySQL）
$q = $dbh->prepare("DESCRIBE tablename");
$q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
回答by Will
This will work for MySQL, Postgres, and probably any other PDO driver that uses the LIMITclause.
这将适用于 MySQL、Postgres 以及可能使用该LIMIT子句的任何其他 PDO 驱动程序。
Notice LIMIT 0is added for improved performance:
LIMIT 0添加通知以提高性能：
$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);
回答by ragnar
My 2 cents:
我的 2 美分：
$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.
您将在 var $fields 中将列名作为数组获取。
回答by Pramendra Gupta
$sql = "select column_name from information_schema.columns where table_name = 'myTable'";
$sql = "select column_name from information_schema.columns where table_name = 'myTable'";
PHP function credits : http://www.sitepoint.com/forums/php-application-design-147/get-pdo-column-name-easy-way-559336.html
PHP 函数积分：http: //www.sitepoint.com/forums/php-application-design-147/get-pdo-column-name-easy-way-559336.html
function getColumnNames(){
$sql = "select column_name from information_schema.columns where table_name = 'myTable'";
#$sql = 'SHOW COLUMNS FROM ' . $this->table;
$stmt = $this->connection->prepare($sql);
try {
if($stmt->execute()){
$raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($raw_column_data as $outer_key => $array){
foreach($array as $inner_key => $value){
if (!(int)$inner_key){
$this->column_names[] = $value;
}
}
}
}
return $this->column_names;
} catch (Exception $e){
return $e->getMessage(); //return exception
}
}
回答by Ari
Here is the function I use. Created based on @Lauer answer above and some other resources:
这是我使用的功能。根据上面的@Lauer 回答和其他一些资源创建：
//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.
$columns = getColumns('name_of_table'); // OR getColumns($name_of_table); if you are using variable.
foreach($columns as $col) {
echo $col . '<br/>';
}
回答by Dave
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
只需为您的 pdo 对象和表名放置变量即可。为我工作
回答by William Entriken
This approach works for me in SQLite and MySQL. It may work with others, please let me know your experience.
这种方法在 SQLite 和 MySQL 中对我有用。它可能与其他人一起工作，请告诉我您的经验。
- Works if rows are present
- Works if no rows are present (test with
DELETE FROM table)
- 如果行存在则有效
- 如果不存在行则工作（用 测试
DELETE FROM table）
Code:
代码：
$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.
我不保证这是符合 ANSI 或其他标准的有效 SQL，但它对我有用。
回答by Phil
As Charle's mentioned, this is a statement method, meaning it fetches the column data from a prepared statement (query).
正如查理所说，这是一个语句方法，这意味着它从准备好的语句（查询）中获取列数据。
回答by Carl McDade
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.
SQLite3 的一个非常有用的解决方案。因为 OP 没有特别指出 MySQL，并且尝试在 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)
{
$elements[$convalue['name']] = $convalue['name'];
}
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.
当桌子是空的时，这是特别好的。
回答by centurian
My contribution ONLYfor SQLite:
我仅对SQLite 的贡献：
/**
* Returns an array of column names for a given table.
* Arg. $dsn should be replaced by $this->dsn in a class definition.
*
* @param string $dsn Database connection string,
* e.g.'sqlite:/home/user3/db/mydb.sq3'
* @param string $table The name of the table
*
* @return string[] An array of table names
*/
public function getTableColumns($dsn, $table) {
$dbh = new \PDO($dsn);
return $dbh->query('PRAGMA table_info(`'.$table.'`)')->fetchAll(\PDO::FETCH_COLUMN, 1);
}
