在我的PHP脚本中消除这个分层for loop（）

Question

I have "channels" in my Android application being represented by an integer in a MySQL db on my server. Basically there are five zones in the application each with five sub channels. The "zones" increment the integer by multiples of 1 while the sub channels increment by 100. (i.e. zone 1, sub-channel 3 equates to int "300") (i.e. zone 3, sub-channel 3 equates to int "303") Okay so this php returns an array[5] with the total number of users in each zone. How can I make this script more efficient? It works but it takes time.

<?php
ini_set('display_errors',1);
$json=$_POST['user'];
$json=str_replace('\\','',$json);
$user=json_decode($json);
$pdo=new PDO('mysql://hostname=localhost;dbname=database', 'user', 'password');
$channels=array(100,200,300,400,500);
$full_status=array();
$current_status=array();

for($i=0;$i<5;$i++){
    for($j=0;$j<5;$j++){
        $total=$pdo->query("SELECT count(user_id) AS count FROM accounts WHERE channel!='0' AND channel='{$channels[$j]}'");
        $total=$total->fetchAll(PDO::FETCH_ASSOC);
        $total=$total[0]['count'];
        $current_status[$j]=$total;
        $channels[$j]++;
    }
    $full_status[]=array_sum($current_status);
}
echo json_encode(array("data"=>$full_status));
?>

Answer 1

From what I can tell, you're adding up the the total numbers in the channels for channels between 100 and 104, 200 and 204, 300 and 304, 400 and 404, and 500 and 504 as a group. In other words, you want the sum of all entries for channels between 100 and 104 as group 100, for all entries for channels between 200 and 204 as group 200, and so forth.

You can use the following query to do this:

SELECT floor(channel/100)*100 as channelgroup,
    count(user_id) AS count FROM accounts
WHERE
    channel BETWEEN 100 AND 104 OR
    channel BETWEEN 200 AND 204 OR
    channel BETWEEN 300 AND 304 OR
    channel BETWEEN 400 AND 404 OR
    channel BETWEEN 500 AND 504
GROUP BY channelgroup

If you know for a fact that all channels in group 100 fall between 100 and 104, all channels in group 200 fall between 200 and 204, and so forth, you can use the following code instead:

SELECT floor(channel/100)*100 as channelgroup,
    count(user_id) AS count FROM accounts
WHERE channel > 0
# Use this if there are other channels below 100 and above 504
# WHERE channel BETWEEN 100 and 504
GROUP BY channelgroup

If this query seems a little confusing, you can run it without the count and GROUP BY components to better see how it works.

SELECT floor(channel/100)*100 as channelgroup,
    user_id FROM accounts
WHERE channel > 0