The problem I'm having is this: when I attempt to prepare a query that's comprised of DESCRIBE table_name
or SHOW COLUMNS FROM table_name
, I get an error that states the following:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users'' at line 1' in /Applications/MAMP/htdocs/test.php:28 Stack trace: #0 /Applications/MAMP/htdocs/test.php(28): PDOStatement->execute() #1 /Applications/MAMP/htdocs/test.php(6): show_columns_yes_prepared('users') #2 {main} thrown in /Applications/MAMP/htdocs/test.php on line 28
The function show_columns_not_prepared()
returns the following, as expected/hoped:
Array
(
[0] => uid
[1] => name
[2] => pass
[3] => mail
[4] => mode
[5] => sort
[6] => threshold
[7] => theme
[8] => signature
[9] => signature_format
[10] => created
[11] => access
[12] => login
[13] => status
[14] => timezone
[15] => language
[16] => picture
[17] => init
[18] => data
[19] => timezone_name
[20] => timezone_id
)
... while the above error occurs as a result of the function show_columns_yes_prepared()
. Both can be found below along with the rest of the source code (verbatim) used in the file test.php
which lives in my localhost's root directory.
Is preparing a DESCRIBE
SQL query an impossible task? Or am I doing something wrong? Neither? Both?
Database / table creation:
CREATE DATABASE `testdatabaseforpdo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `testdatabaseforpdo`;
CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL DEFAULT '',
`pass` varchar(32) NOT NULL DEFAULT '',
`mail` varchar(64) DEFAULT '',
`mode` tinyint(4) NOT NULL DEFAULT '0',
`sort` tinyint(4) DEFAULT '0',
`threshold` tinyint(4) DEFAULT '0',
`theme` varchar(255) NOT NULL DEFAULT '',
`signature` varchar(255) NOT NULL DEFAULT '',
`signature_format` smallint(6) NOT NULL DEFAULT '0',
`created` int(11) NOT NULL DEFAULT '0',
`access` int(11) NOT NULL DEFAULT '0',
`login` int(11) NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '0',
`timezone` varchar(8) DEFAULT NULL,
`language` varchar(12) NOT NULL DEFAULT '',
`picture` varchar(255) NOT NULL DEFAULT '',
`init` varchar(64) DEFAULT '',
`data` longtext,
`timezone_name` varchar(50) NOT NULL DEFAULT '',
`timezone_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`),
KEY `picture` (`picture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Source code (adjust the connection info inside getConnection()
as necessary):
<?php
$table = 'users';
print_r(show_columns_not_prepared($table));
print_r(show_columns_yes_prepared($table));
function show_columns_not_prepared($table) {
$db = getConnection();
// Not prepared, not safe...
$sql = "DESCRIBE $table";
$stmt = $db->query($sql);
$out = array();
while ($res = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
for ($i = 0; $i < count($res); $i++) {
$out[] = $res[$i]['Field'];
}
}
return $out;
}
function show_columns_yes_prepared($table) {
$db = getConnection();
// Ready to be prepared...
$sql = "DESCRIBE :table";
$stmt = $db->prepare($sql);
$stmt->bindValue(':table', $table, PDO::PARAM_STR);
$stmt->execute();
$out = array();
while($res = $stmt->fetch(PDO::FETCH_ASSOC)) {
for ($i = 0; $i < count($res); $i++) {
$out[] = $res[$i]['Field'];
}
}
return $out;
}
function getConnection() {
$dbhost="localhost";
$dbuser="someuser";
$dbpass="somepass";
$dbname="testdatabaseforpdo";
$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec("set names utf8");
return $dbh;
}