jonny
(Jonny)
1
Hi
How would I execute this command
$query->select('i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info,.. rest of query');
Manually in my console it works, but I think due to quoting it fails in Yii2. and says
"your MySQL server version for the right syntax to use near \'AS info
"
Jonny
Tsunami
(Erik)
2
$query->select('i.id, i.title, i.slug, IF(i.display = \'1\', \'Public\', \'Private\') as info,.. rest of query');
Or
$query->select('i.id, i.title, i.slug, IF(i.display = "1", "Public", "Private") as info,.. rest of query');
Or
$query->select("i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info,.. rest of query");
jonny
(Jonny)
3
I actually tried all 3 types of escaping to no avail before posting. It always generates this as the query:
IF(i.display = '1', `'Public'`, `'Private')`
When nothing works on escaping characters and you are confused… the HEREDOC syntax can sometime help… 
$sql = <<< EOT
i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info, /* rest of the query */
EOT;
/* or */
$sql = <<< EOT
i.id, i.title, i.slug, IF(i.display = "1", "Public", "Private") as info, /* rest of the query */
EOT;
$query->select($sql);
jonny
(Jonny)
5
Thanks for that, I am still getting the same error though. I’ll keep looking into this
qiang
(Qiang Xue)
6
Use an array when your select contains expression: select([column1, column2, ...])