PHP＆MySql - 数组foreach

I want to run a foreach loop from database, but I dont know how to start. I have an array which I have generated from a while loop:

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

Now I want to run a foreach loop which will query below code for each ID in array. :

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

I want to show $leaveQ['month'] and $leaveQ['total'] in foreach loop.

May be my foreach like this, but how to get $type['month'] and $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");
}

May youwant something like this

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>";
    }
 }

   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']);
    }
   }

Don't do this!

• mysql_ functions are deprecated as of PHP 5.5!
• doing this (get one query, then run another query for each rows in a foreach loop) is a bad approach, commonly seen in a lot of code flying around the 'net...
• Also, I think you want to get the count for each month; in that case you have to use the GROUP BY clause for that

The proper way to do this is using JOIN operations, and instead of a query for each line, only one query to get all the data.

Blindly following that advice, not changing too much, just merging the two queries, your query should look like this:

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

Differences to your approach:

• the inner query is almost the same as your 1st query, but
  • instead of GROUP BY, I used the DISTINCT - in this case, it is the same, but I think this is easier to read - and that is an important aspect!
  • I only selected the relevant column for it (leave_type_id_leave_type)
• I altered the outer query a bit more
  • the JOIN does what replaces the "foreach" approach
  • only those rows are "taken into count" (in this case, literally :) ), that are appropriate for the inner query
• this will return the count for each month and each type.

To make this even better:

• Use properly parametrized prepared statements: better performance, and getting used to it makes you avoid SQL injection in situations where that applies...
  • you can use PDO for that, it is not deprecated...
• Looking at the resulting query, it is easy to see that this can be further simplified, and does not need the inner query, also getting rid of one filter on $iid

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

Differences now:

• huge performance jump...
• a lot less, and a lot readable code

EDIT

Here is the SQL fiddle to see how this works

If I understand correctly you want to loop thru the types found from your block of code and then issue more database queries and extract more data.

You can basically reuse your first block of code again, wrapped with a foreach loop:

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']);...
    }    
}