代码之家 › 专栏 › 技术社区 › prince vincy

在php mysql中循环两次

mysqli loops mysql php

-1

prince vincy · 技术社区 · 7 年前

我需要一些帮助,我有两张桌子,

主管表 (supervisor_id, name, area_specialty, branch) 和
任务表 (t_id, no_task, supervisor_id)

我正在尝试建立一个查询,该查询可以用给定的 area of specialty 从一个给定的 branch .

在获取了他们的ID之后,我需要通过tasks表循环这些ID,以获得一个任务最少的ID。 (no_task) .

下面是我尝试过的,但似乎没有得到正确的结果

$query2 = mysqli_query($connection, "SELECT * FROM supervisors WHERE area_specialty LIKE '%Embedded systems%' AND branch LIKE '%boston%'");
while($rows =mysqli_fetch_array($query2)) {
    $sid =$rows['supervisor_id'];

    $query1=mysqli_query($connection,"SELECT * from tasks INNER JOIN(select t_id, supervisor_id,MIN(no_task) AS nTask FROM tasks Group By supervisor_id) AS task_1  On task_1.t_id=task.t_id Where task_1.nTask=task.no_task");
    $rm = mysqli_fetch_array($query1);


}
echo $rm['supervisor_id'];

2 回复 | 直到 7 年前

Tien 7 年前

当您可以在查询中简单地使用一些内部联接时,不确定为什么要执行循环。稍后,当您的表很大并且正在循环这些查询和连接时,您将遇到性能问题。如果没有太多细节,我会这样做:

select * from supervisor_table as s
  inner join tasks_table as t
  on t.supervisor_id = s.supervisor_id
  where s.area_specialty like '%Embedded systems%' AND s.branch like '%boston%';

balakrishnan 7 年前

查询将根据您的需求提供任务列表:

SELECT t.* FROM task AS t
INNER JOIN (
   SELECT MIN(no_task) AS MinTask FROM tasks Group By supervisor_id 
   INNER JOIN  supervisor_table AS s ON t.supervisor_id = s.supervisor_id
   WHERE area_specialty LIKE '%Embedded systems%' AND branch LIKE '%boston%'
) as Task1 ON Task1.MinTask = t.no_task

联接中的子查询将为具有给定区域专业和分支的主管获得最少的任务数。

推荐文章