Set random values from an array into database

Question

I'm working on a PHP project with MYSQL database. I have a table of groups of students. Each group has an examiner. What i want to do is that i want to set two examiners for each group randomly. How to do it?

MySQL Code:

create table groups (
    groupID             int(10)               not null,
    nbStudents          int                   not null,
    avgGPA              DOUBLE                NOT NULL,
    projectName         varchar(50)           not null,
    advisorID           int,                  
    examiner1ID         int,    
    examiner2ID         int,
    adminID             int                   not null,
    primary key (groupID)
);

create table faculty (
    name                varchar(30)         not null,
    facultyID           int(10)                 not null,
    email               varchar(30)           not null,
    mobile              int(15)              not null,            
    primary key (facultyID)
);

examiner1ID and examiner2ID are foreign keys from the table faculty.

Edit your question with sample data and desired results.

Gordon Linoff
– Gordon Linoff

2014-07-24 02:09:41 +00:00
Commented Jul 24, 2014 at 2:09 — Gordon Linoff
– Gordon Linoff, Commented Jul 24, 2014 at 2:09
And throw in some source code while you're at it

Deryck
– Deryck

2014-07-24 02:10:33 +00:00
Commented Jul 24, 2014 at 2:10 — Deryck
– Deryck, Commented Jul 24, 2014 at 2:10
Do you have an Examiners table?

Kyle Emmanuel
– Kyle Emmanuel

2014-07-24 02:13:47 +00:00
Commented Jul 24, 2014 at 2:13 — Kyle Emmanuel
– Kyle Emmanuel, Commented Jul 24, 2014 at 2:13
can the same faculty member be assigned to multiple groups?

Fabricator
– Fabricator

2014-07-24 02:21:46 +00:00
Commented Jul 24, 2014 at 2:21 — Fabricator
– Fabricator, Commented Jul 24, 2014 at 2:21

Fabricator · Accepted Answer · 2014-07-24 04:36:18Z

1

Here is a very convoluted way to do it. It uses 2 subqueries to pick faculty members, and insert .. on duplicate key to update the examiners IDs.

insert into groups
(groupID, examiner1ID, examiner2ID)
select groupID, 
    @x:=(select facultyID from faculty order by rand() limit 1),
    (select facultyID from faculty where facultyID <> @x order by rand() limit 1)
from groups
on duplicate key update examiner1ID=values(examiner1ID), examiner2ID=values(examiner2ID);

@x is a user-defined-variable. In this case, it is used to store the first random faculty member. <> @x makes sure we don't pick the same faculty member in both slots.

Since groupID is a unique key, when we try to insert a row with an existing unique key, it will update the existing row instead of inserting it. That's what on duplicate key update clause is used for.

set different examiners for each group:

insert into groups
(groupID, examier1ID, examier2ID)
select a.groupID, max(if(b.id%2, b.facultyID, 0)), max(if(b.id%2, 0, b.facultyID))
from (
    select @row:=@row+1 id, groupID 
    from groups a
    join (select @row:=0) b) a
join (
    select @row:=@row+1 id, facultyID
    from (
        select facultyID
        from faculty a
        order by rand()) a
    join (select @row:=0) b) b on a.id = ceil(b.id/2)
group by a.groupID
on duplicate key update examiner1ID=values(examiner1ID), examiner2ID=values(examiner2ID);

edited Jul 24, 2014 at 4:36

answered Jul 24, 2014 at 2:35

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

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

3 Comments

onlyforthis Over a year ago

Thanks. Can you please explain what you just did? What is @x: and <> ? What is on duplicate key?

onlyforthis Over a year ago

Thanks dude. Last thing. How to set different examiners for each group? No duplicates. Thanks.

Fabricator Over a year ago

@hzjw, you mean the same faculty member cannot be assigned to multiple groups?

Collectives™ on Stack Overflow

Set random values from an array into database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related