CODEWITHSUNDEEP
mysqljoomlaaliasbackticks
Claudio Ferraro
Claudio Ferraro

Reputation: 4721

How to assign aliases to an array of column names with Joomla's quoteName()?

I want to use the AS statement for Aliases in a query. I use this piece of code:

$query->select($db->quoteName(array('NameInQ as nin', 'Name')));

Anyway I get this error:

'Unknown column 'NameInQ as nin' in 'field list'

NameInQ does exist as a column name in the table. nin should be the alias.

What am I doing wrong?

Upvotes: 1

Views: 475

Answers (1)

mickmackusa
mickmackusa

Reputation: 48031

When you tell Joomla:

$query->select($db->quoteName(array('NameInQ as nin', 'Name')));

echo $query->dump(); will tell you:

SELECT `NameInQ as nin`,`Name`

See how it doesn't know how to differentiate an aliased column name from a string with spaces in it?

The Docs: https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_quoteName

If you want to assign aliases to column names in Joomla from within the qn() / quoteName() method, you will need to nominate corresponding aliases for all columns.

$query->select($db->quoteName(array('NameInQ', 'Name'), array('nin', 'Name')));

Renders as:

SELECT `NameInQ` AS `nin`,`Name` AS `Name`
//     ^-------^----^---^-^----^----^----^-- everything aliased, everything backtick wrapped

Or, of course you could individualize the quoteName() calls, you can avoid aliasing every column.

$query->select(array($db->quoteName('NameInQ', 'nin'), $db->quoteName('Name')));

Renders as:

SELECT `NameInQ` AS `nin`,`Name`

Finally, the truth is: You don't even need to quote any of your sample column names because the query will be stable/secure without the extra method call(s). *I recommend leaving them out to minimize query bloat and developer eye-strain.

$query->select(array('NameInQ AS nin', 'Name'));

or even in raw form:

$query->select('NameInQ AS nin, Name');

For the record, Name (MYSQL is case-insensitive) IS a KEYWORD, but it is not a RESERVED KEYWORD.

See the MySQL Doc: https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-N (there is no "(R)" beside Name

Upvotes: 1

Related Questions