PHP & MySql - array foreach - PHP & MySql - array foreach

我想从数据库运行一个foreach循环，但我不知道如何开始。我有一个从while循环生成的数组:

    /* mysql query for geting leave_type ID */
    $leaveType = mysql_query("
    SELECT `leave`.leave_type_id_leave_type,
      `leave`.staff_leave_application_staff_id_staff,
      `leave`.date,
      `leave`.date_updated,
      `leave`.active
    FROM `leave`
    WHERE `leave`.staff_leave_application_staff_id_staff = $iid
    GROUP BY `leave`.leave_type_id_leave_type   
    ");
    /* Now put all leave Type ID in an array */
   echo "<table>";
   $types = array();
    while($leaveFW = mysql_fetch_array( $leaveType )){
        $types[] = $leaveFW['leave_type_id_leave_type'];
    }
print_r($types);

现在我想运行一个foreach循环，它将在数组中查询下面的代码中的每个ID。:

$leaveQ = mysql_query("SELECT Count(*) as total, monthname(date) as 
month FROM `leave`
WHERE `leave`.staff_leave_application_staff_id_staff = $iid 
and `leave`.leave_type_id_leave_type = $type");

我想显示foreach循环中的$leaveQ['month']和$leaveQ['total']。

可能是我的梦想，但如何得到$type['month']和$type['total']:

foreach ($types as $type)
{
    $leaveQ = mysql_query("SELECT Count(*) as total, monthname(date) as month
    FROM `leave` WHERE `leave`.staff_leave_application_staff_id_staff = $iid 
    and `leave`.leave_type_id_leave_type = $type");
}

你可能想要这样的东西

foreach($types as $result)
{
    $iid  = $result['staff_leave_application_staff_id_staff'];
    $type = $result['leave_type_id_leave_type'];
    $leaveQ = mysql_query("SELECT Count(*) as total, monthname(date) as 
    month FROM `leave`
        WHERE `leave`.staff_leave_application_staff_id_staff = $iid 
     and `leave`.leave_type_id_leave_type = '".$leaveFW['leave_type_id_leave_type']."'");

    while($row = mysql_fetch_assoc($leaveQ))
    {
       echo $row['month']."<br>";
       echo $row['total']."<br>";
    }
 }

不要这样做!

mysql_函数在PHP 5.5中被弃用!
这样做(获得一个查询，然后在foreach循环中为每一行运行另一个查询)是一种糟糕的方法，通常在许多代码中看到。
另外，我认为你想要得到每个月的数量;在这种情况下，你必须使用GROUP BY子句

正确的方法是使用JOIN操作，而不是对每行进行查询，只需一次查询即可获得所有数据。

盲目地遵循这个建议，不做太多改变，只是合并两个查询，你的查询应该是这样的:

SELECT Count(*) as total, monthname(date) as month, types.leave_type_id_leave_type
FROM `leave`
JOIN (SELECT DISTINCT `leave`.leave_type_id_leave_type
    FROM `leave`
    WHERE `leave`.staff_leave_application_staff_id_staff = $iid) as types 
  ON leave.leave_type_id_leave_type = types.leave_type_id_leave_type
WHERE `leave`.staff_leave_application_staff_id_staff = $iid 
GROUP BY monthname(date), types.leave_type_id_leave_type

不同的方法:

内部查询几乎与您的第一个查询相同，但是
- 而不是GROUP BY，我使用了DISTINCT——在这种情况下，它是一样的，但我认为这更容易阅读——这是一个重要的方面!
- 我只为它选择了相关的列(leave_type_id_leave_type)
我稍微改变了一下外部查询
- JOIN取代了"foreach"方法
- 只有那些适合内部查询
返回每个月和每种类型的计数。

让它变得更好:

使用适当的参数化预处理语句:更好的性能，并习惯它使您避免SQL注入的情况下，适用…
- 你可以使用PDO，它不被弃用…

SELECT Count(*) as total, monthname(date) as month, leave_type_id_leave_type
FROM `leave`
WHERE `leave`.staff_leave_application_staff_id_staff = $iid
GROUP BY monthname(date), leave_type_id_leave_type

差异:

的代码少得多，而可读的代码却很多

编辑

   foreach($types as $type){
    $leaveQ = mysql_query("SELECT Count(*) as total, monthname(date) as 
    month FROM `leave`
    WHERE `leave`.staff_leave_application_staff_id_staff = $iid 
    and `leave`.leave_type_id_leave_type = $type"); 
    while($leave = mysql_fetch_assoc($leaveQ)){
    var_dump($leave['total'] , $leave['month']);
    }
   }

如果我理解正确的话，你想循环遍历从代码块中找到的类型，然后发出更多的数据库查询并提取更多的数据。

你基本上可以再次重用你的第一个代码块，用foreach循环包装:

foreach($types as $type){
    $query = mysql_query("YOUR_SQL_USING_$TYPE");
    while($row = mysql_fetch_array( $query )){
        print_r($row);
        // or print($row['COLUMN_NAME']);...
    }    
}