(PHP, MySQL, SQLite3) テーブルのフィールド名一覧を取得するには
SELECT文で全フィールドを取得したい場合、普通は"*(アスタリスク)"を使います。
が、"CakePHP + SQLite3 + query()"の組み合わせで、生のSQLの中でアスタリスクを使うとなぜかエラーにorz
そんなわけで、フィールド名一覧を取得する必要に迫られました。
SQLite3とMySQLそれぞれの方法を紹介します。
SQLite3
PRAGMA table_info(table_name);【実際のコード】
$rows = $db->query('PRAGMA table_info(posts)');
while ($row = $rows->fetchArray(SQLITE3_ASSOC)) {
echo $row['name'];
}
MySQL
SHOW COLUMNS FROM table_name;【実際のコード】
$rows = mysql_query('SHOW COLUMNS FROM posts', $db);
while ($row = mysql_fetch_array($rows, MYSQL_ASSOC)) {
echo $row['Field'];
}
それぞれのSQLの取得結果の丸ごと全て($row)は下記。
(SQLite)
Array
(
[0] => Array
(
[cid] => 0
[name] => id
[type] => INTEGER
[notnull] => 0
[dflt_value] =>
[pk] => 1
)[1] => Array
(
[cid] => 1
[name] => name
[type] => CHAR
[notnull] => 0
[dflt_value] => ''
[pk] => 0
)[2] => Array
(
[cid] => 2
[name] => created
[type] => DATETIME
[notnull] => 0
[dflt_value] => ''
[pk] => 0
)[3] => Array
(
[cid] => 3
[name] => modified
[type] => DATETIME
[notnull] => 0
[dflt_value] => ''
[pk] => 0
))
【参考】
(MySQL)
Array
(
[0] => Array
(
[Field] => id
[Type] => int(11)
[Null] => NO
[Key] => PRI
[Default] =>
[Extra] => auto_increment
)[1] => Array
(
[Field] => name
[Type] => varchar(63)
[Null] => NO
[Key] =>
[Default] =>
[Extra] =>
)[2] => Array
(
[Field] => created
[Type] => datetime
[Null] => NO
[Key] =>
[Default] =>
[Extra] =>
)[3] => Array
(
[Field] => modified
[Type] => datetime
[Null] => NO
[Key] =>
[Default] =>
[Extra] =>
)
)
MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.4 SHOW COLUMNS 構文
http://dev.mysql.com/doc/refman/5.1/ja/show-columns.html
sqlite - How to get a list of column names on sqlite3 / iPhone? - Stack Overflow
http://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-iphone
