How to get mysqli to allow SQL-based User-Defined Variables as columnaliases?

June 10, 2021 05:22 (Philip Schlesinger)
Hi all,

I’m able to get mysqli to set a user-defined variable and the use it later on either in a column value definition or a conditional, I.e. (semi-pseudocode since I’m not in front of my work computer)

First do mysqli $conn->query(‘Set @foo = “bar”;’);
…which will prepare the database session with the variable.

Then do a $stmt = $conn->stmt_init()

Then do a $stmt->prepare(‘Select @foo as “baz”;’);

Finally do a $stmt->execute();

That results in:


_however_ if I want to use a SQL user-defined variable as a column alias, mysqli throws an error.  This does not work:

$conn->query(‘set @acolname = “aCol”;’);

$stmt = $conn->stmt_init()

$stmt->prepare(‘Select “data” as @acolname;’);


If I ran the straight SQL in a SQL application, making the select statement a variable as well, and did a prepare and execute, it would work just fine

Mysqli however throws an error with the column alias being a SQL user-defined variable.


Get Outlook for iOS<>