Mysql - Conditional inserting after select query

Question

I have an old database to refactor and have faced this table:

Member

id | Name  |    Role    
1  | Tyler |      6    
2  | Jane  |     16    
3  | Jack  |     20

Role

id | role            |    Level    
1  | Visitor         |      1    
2  | Member          |      2    
3  | Monthly Member  |      4

First, I was confused because role in member table didn't match with any data in role table. After analyzing source codes, I've learned that this programmer had come up with his own way of doing it...

selected roles = 2^(role's level).

ie) Jack is a Member & a monthly Member

Jack's role = 2^(member level) + 2^(monthly member level) = 2^2 + 2^4 = 20.

Well, Here is I want to do. I will create three tables representing many-to-many relationship.

Member

id | Name      
1  | Tyler    
2  | Jane    
3  | Jack

Role

id | role          
1  | Visitor       
2  | Member        
3  | Monthly Member

Member_Role

member_id | role_id          
    1     |    1       
    1     |    2        
    3     |    2

Now, I want to select all members and insert appropriate member_id & rold_id to Member_Role table but not sure how to code this query. My general idea is this...

//Select all members from member table
    //if role is 16
         // insert his member id and monthly memberid to member_role table
    //if role is 20
         // insert his member id and member(role) id to member_role table
         // insert his member id and monthly member id to member_role table
    // and so on....

The important thing is I want to manually insert values depending on its condition. This is because I will rearrange the role's order and this "selected roles = 2^(role's level)" formula will not work anymore. Thus, I prefer it to be hardcoded instead of generic. It's one-time use code anyway...

Thanks !!

realistically, how many actual permutations of old data do you have before this is grossly over-engineered, 9 ? — Drew
– Drew, Commented Jul 19, 2015 at 16:15
Why 2^2+4^2 as opposed to just 2+4?? I suppose we'll never know. — Strawberry
– Strawberry, Commented Jul 19, 2015 at 16:16
@DrewPierce I've found 10 different combinations. Luckily, less enough to manually code it ;) — Raccoon
– Raccoon, Commented Jul 19, 2015 at 16:17
i would create that ugly script (if old= 20 then insert the following) etc and forget the einstein approach — Drew
– Drew, Commented Jul 19, 2015 at 16:18
This code is from the time before foreign key relations in databases? The 1950s? — Norbert
– Norbert, Commented Jul 19, 2015 at 16:19

Gordon Linoff · Accepted Answer · 2015-07-19 16:23:46Z

2

You can do this using the bitwise "and" operator. I would expect following to get the matches:

select m.*, r.*
from member m join
     role r
     on (r.level & m.role) > 0;

However, your example is suggesting something more like:

from member m join
     role r
     on ((1 << r.level) & m.role) > 0;

Do some investigation to see what the situation really is.

The logic for the insertion into the new table is a bit more complicated. Assuming you have a new_roles table with the same names:

insert into member_roles(member_id, role_id)
    select m.id, nr.id
    from member m join
         role r
         on (r.level & m.role) > 0 join
         new_roles nr
         on nr.role = r.role;

The first on clause might be:

     on ((1 << r.level) & m.role) > 0;

answered Jul 19, 2015 at 16:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raccoon Over a year ago

sounds a bit tricky so i will stick to a simpler way ;) but thank you for your suggestion !

Drew · Accepted Answer · 2015-07-19 16:26:58Z

1

There, you asked for an example of the ugly approach (non-einstein approach)

For that one permutation

create table oldstuff
(
    id int not null,
    yuck int not null
);

create table newstuff_junction
(
    userId int not null,
    roleId int not null
);

insert oldstuff (id,yuck) values (1,20),(2,20);

insert newstuff_junction (userId,roleId) select id,2 from oldstuff where yuck=20;
insert newstuff_junction (userId,roleId) select id,4 from oldstuff where yuck=20;

select * from newstuff_junction order by userId,roleId; 

+--------+--------+
| userId | roleId |
+--------+--------+
|      1 |      2 |
|      1 |      4 |
|      2 |      2 |
|      2 |      4 |
+--------+--------+
4 rows in set (0.00 sec)

answered Jul 19, 2015 at 16:26

Drew

25k10 gold badges47 silver badges81 bronze badges

2 Comments

Drew Over a year ago

i am ashamed to even show that, in middle of facepalm

Raccoon Over a year ago

hahahaha, Now I feel sorry for showing this old 'OMG' code to you guys... thanks anyway!

Strawberry · Accepted Answer · 2015-07-19 16:30:50Z

1

You didn't ask for it, but just for fun, here's an example in PHP using the bitwise logic of the original data set.

<?php
//e.g.hostpath/bitfun.php?memberbit=6

$input = $_GET['memberbit'];

$member_types = array('visitor', 'monthly', 'monthly_member');

for( $i=0; $i<count($member_types); $i++ ) {
    $memberbit = pow(2,$i);
    if( $input & $memberbit ) {
        echo $member_types[$i] . '<br>';
    }
}

?>

answered Jul 19, 2015 at 16:30

Strawberry

34k14 gold badges43 silver badges57 bronze badges

1 Comment

Raccoon Over a year ago

Now I see what he was trying to do... tho his approach seems wrong

Collectives™ on Stack Overflow

Mysql - Conditional inserting after select query

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related