php - 将数组中的随机值设置到数据库中

Question

我正在使用 MYSQL 数据库开发一个 PHP 项目。我有一张学生组表。每组有一名考官。我想做的是，我想为每个组随机设置两个考官。怎么做？

MySQL代码:

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 和 examiner2ID 是来自表 faculty 的外键。

Answer 1

这是一个非常复杂的方法。它使用 2 个子查询来选择教员，以及 insert .. on duplicate key更新考官 ID。

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是 user-defined-variable .在这种情况下，它用于存储第一个随机教员。 <> @x确保我们不会在两个位置选择同一位教员。

自 groupID是唯一键，当我们尝试插入具有现有唯一键的行时，它将更新现有行而不是插入它。这就是on duplicate key update子句用于。

为每个组设置不同的考官:

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);