mysqli prepared statement doesn't return value

Question

Considering the table email_templates:

CREATE TABLE IF NOT EXISTS `email_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` tinytext NOT NULL DEFAULT '',
  `display_name` tinytext NOT NULL DEFAULT '',
  `from` text NOT NULL DEFAULT '',
  `to` text NOT NULL DEFAULT '',
  `subject` tinytext NOT NULL DEFAULT '',
  `body` mediumtext NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

id  name                          display_name  from    to  subject body
1   maintenance_request_customer                                    lorem ipsum

The following code:

$sql = "SELECT ? FROM `email_templates` WHERE `name` = ?";
$stmt = mysqli_prepare($link, $sql);
$params = array('body', 'maintenance_request_customer');
mysqli_stmt_bind_param($stmt, "ss", ...$params);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $body);
mysqli_stmt_fetch($stmt);
error_log($body);

Outputs:

'body'

And the following code:

$sql = "SELECT ? FROM `email_templates` WHERE `name` = ?";
$stmt = mysqli_prepare($link, $sql);
$params = array('body', 'maintenance_request_customer');
mysqli_stmt_bind_param($stmt, "ss", ...$params);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($row = $result->fetch_assoc()) {
    error_log(var_export($row, true));
}

Outputs:

array (
  '?' => 'body',
)

In both cases I'm expecting to get the value lorem ipsum in some fashion. In either case I'm not getting the value. I'd prefer to be using the method that uses mysqli_stmt_get_result however I'm wondering why neither method is working as expected. Much appreciated!

You cannot bind a column name. So just select *, then fetch a row and then have your value from $row[$name] — Your Common Sense, Jun 27 '20 at 19:42
I'm just curious (as the comments above deal with the question) why do you want to do this in the first place? Is this just a learning exercise or are you trying to create some kind of flexible query builder? I would have thought binding col names in a query is risky as invalid data will case an SQL error — imposterSyndrome, Jun 27 '20 at 19:59
thx y'all & @jameson2012 yep trying to create a wrapper to more easily craft & execute prepared statements. — hot_barbara, Jun 27 '20 at 20:07
I used to work on an EdTech platform and we tried dynamically building queries but it soon becomes messy and hard to see whats going on, you end up with no end of conditionals and start breaking solid rules. These day's I'd sooner have a couple of extra functions in my DAO which have specific jobs, even if it means a little duplication. It ends up being easier to manage and extend in the future. That's just my own opinion though :) — imposterSyndrome, Jun 27 '20 at 20:23

mysqli prepared statement doesn't return value

0 Answers0