cialis levitra viagra
I’m working on a bit of code that prepends database table prefixes to a string of SQL. For example, I would take the following SQL:
select User.UserID, Permission.PermissionID from User
left join UserRole
on User.UserID = UserRole.UserID
left join RolePermission
on UserRole.RoleID = RolePermission.RoleID
left join Permission
on RolePermission.PermissionID = Permission.PermissionID
and Permission.Name = 'can_sign_in'
where User.Name = 'mark'
and User.Password = md5('password')
… and turn it into …
select LUM_User.UserID, LUM_Permission.PermissionID from LUM_User
left join LUM_UserRole
on LUM_User.UserID = LUM_UserRole.UserID
left join LUM_RolePermission
on LUM_UserRole.RoleID = LUM_RolePermission.RoleID
left join LUM_Permission
on LUM_RolePermission.PermissionID = LUM_Permission.PermissionID
and LUM_Permission.Name = 'can_sign_in'
where LUM_User.Name = 'mark'
and LUM_User.Password = md5('password')
I’ve got 99% of it working properly, but I am having difficulty with the “on” clause of the join. In a simple join, like the first join above, the following code works perfectly:
$DbPrefix = 'LUM_';
$On = 'User.UserID = UserRole.UserID';
$On = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $DbPrefix . "$1$2" . $DbPrefix . "$3", $On);
// Returns: LUM_User.UserID = LUM_UserRole.UserID
But the regex I have here is only good for one join. So, for example, this would be a problem:
$DbPrefix = 'LUM_';
$On = "RolePermission.PermissionID = Permission.PermissionID and Permission.Name = 'can_sign_in' ";
$On = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $DbPrefix . "$1$2" . $DbPrefix . "$3", $On);
// Returns: LUM_RolePermission.PermissionID = LUM_Permission.PermissionID and Permission.Name = 'can_sign_in'
As you can see, it only prepends the prefix on the first two. I’m afraid I’m at a loss as to how I can get it to keep matching on subsequent joins for that table. In this example you might say that I should put the second part of my join into the where clause; but that’s not what I’m trying to accomplish; so just pretend that it’s a regular table-to-table join and needs to be here.
Anyone regex-ey enough to conquer this one?
cialis levitra viagra
Thanks to Jeff, who solved it with:
$DbPrefix = 'LUM_';
$On = "RolePermission.PermissionID = Permission.PermissionID and Permission.Name = 'can_sign_in' ";
$On = preg_replace('/(\\w+\\.)/', $DbPrefix.'$1', $On);
// Returns: LUM_RolePermission.PermissionID = LUM_Permission.PermissionID and LUM_Permission.Name = 'can_sign_in'
