PHP MySQL 查询

Question

目前我有 3 个 MySQL 表，一张记录了用户信息，一张记录了他们的类(class)历史记录，最后一张记录了他们的奖励历史记录。

如果您愿意的话，我正在尝试使用这两个数据库和 PHP 脚本创建一个“奖励”计划。就奖励要求而言，我为两种类型的奖励设置了 2 个数组:

//Pen Reward with course A & B required
$pen=array("a","b")

//Cup Reward with course B & C required
$cup=array("b","c")

我不知道如何首先查询所有用户 ID，然后查看他们的历史记录。如果他们符合上面设定的要求，那么将奖励表更新为1(以表明他们获得了奖励)

用户数据库

userid    name
----------------
1         bill
2         john
3         steve

历史数据库

userid    courseid
------------------
1         a
1         b
2         b
3         a
3         c

RewardHistoryDB

userid    pen    cup  
---------------------
1         0      0
2         0      0
3         0      0

理想情况下，PHP 脚本运行后，它会更新 RewardHistoryDB 以反射(reflect)用户 1 收到了一支笔，用户 3 收到了一个杯子。(数据库看起来像这样:)

RewardHistoryDB(之后)

userid    pen    cup  
---------------------
1         1      0
2         0      0
3         0      1

这是我现在使用的 PHP 代码(不完整):

$userid=array();
$i=0;

//Find All users
$result = mysqli_query($con,"SELECT userid FROM usersdb")
while ($row = mysqli_fetch_array($result)){
    $universityid[$i]=$row['universityid'];
    $i++;
}

//Find History
$courseid=array();
foreach ($userid as $userid1){
$result = mysqli_query($con,"SELECT courseid FROM historydb WHERE userid='".$userid1."'");
    while ($row = mysqli_fetch_array($result)){
        $courseid[]=$row;
    }
}

//Update Reward DB
//Don't even know where to start...

任何帮助将不胜感激。如果您有任何疑问，请告诉我。

提前谢谢您!

Answer 1

http://sqlfiddle.com/#!2/1a96b/1/0

您可以像查询一样执行此操作，而无需在 PHP 中执行任何逻辑

update RewardHistoryDB
   set Pen = 1 
   where UserID in ( 
         select  UserID 
         from    HistoryDB 
         where   CourseID in ('A','B')
         group   by UserID
         having  count(UserID)=2);


update RewardHistoryDB
   set Cup = 1 
   where UserID in ( 
         select  UserID 
         from    HistoryDB 
         where   CourseID in ('B','C')
         group   by UserID
         having  count(UserID)=2);

<小时/>

或者你甚至可以在一个大的语句中完成它......

update RewardHistoryDB   

      left outer join (
            select  UserID as Pens_UserID
            from    HistoryDB 
            where   CourseID in ('A','B')
            group   by UserID
            having  count(UserID)=2 ) as Pens
         on RewardHistoryDB.UserID = Pens.Pens_UserID 

      left outer join (
            select  UserID as Cups_UserID
            from    HistoryDB 
            where   CourseID in ('B','C')
            group   by UserID
            having  count(UserID)=2 ) as Cups
         on RewardHistoryDB.UserID = Cups.Cups_UserID 

set Pen = case when Pens.Pens_UserID is not null then 1 else 0 end,
    Cup = case when Cups.Cups_UserID is not null then 1 else 0 end