Add prefixes to fields on select with join

Hello,
I have two tables that have a couple of columns with the same name. Now I want to select all of the columns from both tables and add a prefix to each column so I could know which column is from which table.

My solution to this problem was to add two more queries. I used show columns on both tables. Then I’ve used a server side language (PHP in this case) to loop through the results and added the prefix to each result.

The result of this process was this query
select fieldname as prefixfieldname … from table

Now since this process is not very effective I’m looking for an alternative solution.

Thanks in advance,
Kfir

add two more queries???

the correct solution is select fieldname as prefixfieldname

I cant do that without knowing all the fields in the query now can I? My tables are dynamic and I want all the fields in them with a prefix. Something like
select * as prefix*
So a static query like you offered is no good for me…

Thanks again,
Kfir

i am sorry, i guess i did not explain it properly

if you want column aliases, you must code them that way

there is no nice workaround, except insofar as the queries themselves can be generated by application code (although this would be unusual, since the application code has to know how the tables are joined)

Well if that’s how it is maybe there’s another way to deal with two columns with the same name that will be more effective then adding a prefix?

sorry, i don’t think so

two columns in different tables with the same name is very common

usually it’s something like this –


SELECT categories.name AS category_name
     , entries.name AS entry_name
     , ...
  FROM entries
INNER
  JOIN categories
    ON categories.id = entries.category_id

in the FROM clause you can see one of my suggested “best practice” conventions, which is to use “id” for PKs (primary keys), but “entity_id” for FKs (foreign keys)

however, you still ahve to deal with situations like “name” which will be very common

assigning column aliases is the only way to handle this effectively

Well if that is the best solution with the current mysql version then there’s nothing more that I can do…
I think I might submit a request for this to be added on future versions…

Many thanks Rudy,
Kfir

alternatively, you could issue a DESCRIBE TABLE command to get the names of all of the columns in a table. then write your query using that.

That was my idea at the first place but it uses more queries so I was looking for a better one…
But thanks anyway,
Kfir

using DESCRIBE TABLE is a much much better idea than running one query inside a loop.

There seems to be a bit of confusion as for how I’m currently getting the columns so I’ll just share my function for this


function get_columns($table,$as = array("","")) {
	$query = mysql_query("show columns from `{$table}`") or die(mysql_error());
	$str = "";
	$comma = "";
	while($row = mysql_fetch_assoc($query)) {
		$str .= $comma.$as[0]."`".$row['Field']."` as `".$as[1].$row['Field']."`";
		$comma = ", ";
	}
	return $str;
}

Now this is how I use this function


mysql_query("select ".get_columns(`tbl1`,array(`tbl1`,`prefix1`)).", ".get_columns(`tbl2`,array(`tbl2`,`prefix2`)). " from `tbl1` left join (`tbl2`) on (`tbl1`.`group`=`tbl2`.`id`) where `tbl1`.`id`='1' limit 1");

the only confusion i have is why you would want to run 3 queries when you could run only 1

i think there might be a problem here:

My tables are dynamic …

Well as you pointed if my table weren’t dynamic your solution would be good.
The thing is that I use both of these tables for my cms users. One table contains general data on the users and one contains permissions sets. Now since I use modules I would like to have the ability to change the tables if any module needs more data from one of the tables.
If you have a way to do this with a static table then I will be happy to hear about it.

i would have to see your tables :slight_smile:

I must admit that I don’t really understand why, because if my tables are dynamic this means that the current structure of the tables will change but you are the SQL pro here so I shell do as you say :slight_smile:

Here is the current table structure:


CREATE TABLE IF NOT EXISTS `cms_users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(255) NOT NULL,
  `password` varchar(40) NOT NULL,
  `salt` varchar(5) NOT NULL,
  `hash` varchar(40) NOT NULL,
  `registration_date` int(10) unsigned NOT NULL,
  `email` text NOT NULL,
  `group` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `cms_permissions` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `auth` tinyint(1) unsigned NOT NULL,
  `panel_access` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

can you give an example of how these tables will change? what sorts of new columns will you be adding? and when will you be adding them?

also, how are these tables related?

First I will explain how these two tables are related.
The first table contains data on all my users. Now as you can see in the first table I have a column called “group”. Now those groups are set of permissions for a user. For example the field panel_access determines if a user can access the control panel or not. What this means is that when a user logs into my system and I want to know what is he allowed to do I need to get his permissions and I do so with the permissions group.

Now example for a modification for the users table. Lets say I’m adding a module that allows users to put a picture as a user avatar.


ALTER TABLE `cms_users` ADD `picture_path` TEXT NOT NULL ;

Then if I would like to remove this module from the system I would remove any column that this module alone requires and this case the query will be


ALTER TABLE `cms_users` DROP `picture_path`  ;

Now for there very same module lets say I want to give some users the permission to edit pictures of other users. This means I need a new column in the permissions table


ALTER TABLE `cms_permissions` ADD `edit_picture` TEXT NOT NULL ;

And the tables will continue to change as the admin will add or remove modules on the system.

Hope it’s clearer then it was before,
Kfir

so the field group is a foreign key to the id column in the permissions table?

regarding static tables, i would recommend that you not use a separate column for each permission, which will require table maintenance every time you add/drop a permission, but instead, use a table which stores one permission per row

this design process is called normalization

CREATE TABLE IF NOT EXISTS cms_groups
( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, name varchar(255) NOT NULL
);
INSERT INTO cms_groups VALUES
 ( 1, 'normal users' )
,( 2, 'admin users' )
,( 3, 'superuser' )
;
CREATE TABLE IF NOT EXISTS cms_permissions
( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
, name varchar(255) NOT NULL
);
INSERT INTO cms_permissions VALUES
 ( 1, 'auth' )
,( 2, 'panel_access' )
,( 3, 'picture_path' )
,( 4, 'edit_picture' )
;
CREATE TABLE IF NOT EXISTS cms_group_permissions
( groupid INTEGER NOT NULL
, permissionid INTEGER NOT NULL
, PRIMARY KEY (groupid, permissionid)
);

/* NOW ASSIGN PERMISSIONS TO GROUPS */

INSERT INTO cms_group_permissions VALUES
 ( 1, 1 ) -- normal users auth
,( 2, 1 ) -- admin users auth
,( 2, 2 ) -- admin users panel_access
,( 3, 1 ) -- superuser auth
,( 3, 2 ) -- superuser panel_access
,( 3, 3 ) -- superuser picture_path
,( 3, 4 ) -- superuser edit_picture


 

Well when I think about your idea make sense.
I’m having a bit of a problem here on how to get all the needed data on one query.
If you could help me out with this it’ll be great :slight_smile:

Many thanks,
Kfir

i’d be happy to help

what are “all the needed data” please?