CodeIgniter Forums
Append variable to column name during select statement - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Append variable to column name during select statement (/showthread.php?tid=17899)



Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]mvdg27[/eluser]
Hi guys,

I'm wondering if there is an easier way to achieve a MySQL select statement where a specific variable name is appended to the column names. Right now I add the variable name explicitly to each column, but perhaps this can be done more easy?

Code:
$var = 'my_var';
$this->db->select('id AS id_'.$var.', foo AS foo_'.$var .... etc. ect.);

Also, this would be useful for when I want to do a select all ('*'), statement .. does anyone know of how this is done?

Thanks, in advance!

-Michiel


Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]Dam1an[/eluser]
As far as I know, there is no official way to alias every field in a select statement, one way you could do this (although not recommended) would be to hack the DB class, and do the aliasing there, although this would apply to all your SQL


Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]mvdg27[/eluser]
Hacking the DB class doesn't really sound like something I'd want to do .. I'm not using this technique for all queries and I was basically wondering if there was an existing SQL hack to do this ..


Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]Dam1an[/eluser]
Out of curiosity, why do you want to alias all the columns?
I only find aliasing useful with functions such as count, min, max etc and when you have the same field name and are dealing with multiple tables


Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]mvdg27[/eluser]
Basically I'm using it to feed the template class. I've created some easy to use functions for my web developers to retract data from the system, such as menu, a block or current page info etc. And just to prevent variables from being overwritten, because they have the same name, I've introduced this variable to make the variable names unique. An example:

For the current page I have various info available, such as 'title', 'url' etc. Now if I would want to loop through the menu with the Template class, like this:

Code:
<ul>
{menu}
  <li><a href="{url}">{title}</a><li>
{/menu}
</ul>

That wouldn't work as expected. In this situation it will reuse the url and title of the page, instead of using the values from the loop. So I've decided, that in the function that retrieves the menu in the template, you can pass a variable name, which serves as a suffix for these values and use it as such:

Code:
<ul>
{menu}
  <li><a href="{menu_url}">{menu_title}</a><li>
{/menu}
</ul>

That's in short the context that I'm in ..


Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]Dam1an[/eluser]
I think an easier way might be to feed each section into a seperate array, so you'd have the following in the controller
Code:
$data['menu']['url'] = "my_url";
$data['menu']['title'] = "my_title";

and the following in the view (using the standard notation instead of the template formatting, which I don't know)
Code:
<ul>
{menu}
  <li><a href="$['menu']['url']">$['menu']['title']</a><li>
{/menu}
</ul>



Append variable to column name during select statement - El Forum - 04-19-2009

[eluser]mvdg27[/eluser]
I would agree with you, except that it's very important for me, not to have any "difficult php" in the templates. My target audience are the so called noobs, and I really want to prevent the use of php.

So far my way, is working fine. Just would have been nice, to have an easier way to prefix or suffix the column names with a variable ..


Append variable to column name during select statement - El Forum - 04-20-2009

[eluser]obiron2[/eluser]
Personally, I think your way is quite nice as it definately uses DRY (Don't Repeat Yourself) code.

The only suggestion i would make is to create an array of the field names you want to bring back as well as the $var suffix and I would build the SELECT part of the statement by looping through the array. this way you can pass the $var and $array to a generic query. this saves you from having to store different queries if there are different types of data you require (e.g. Image caption, Alt Text, Copyright, menu link URL)

Obiron