Stack Exchange
PHP / MySQL Iterating through database to get counts
In PHP I'm iterating through a table using the following code:
// Alexandria

$a_total_sc = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM offices WHERE hidden = '0' AND choice = '3' AND region = 'Alexandria'"));

$a_b_result_sc = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM offices WHERE status = 'Behind'     AND hidden = '0' AND choice = '3' AND region = 'Alexandria' AND sales_code = 'A024'"));
$a_c_result_sc = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM offices WHERE status = 'Completed'  AND hidden = '0' AND choice = '3' AND region = 'Alexandria' AND sales_code = 'A036'"));
$a_d_result_sc = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM offices WHERE status = 'Delayed'    AND hidden = '0' AND choice = '3' AND region = 'Alexandria' AND sales_code = 'A049'"));
$a_o_result_sc = mysqli_num_rows(mysqli_query($conn,"SELECT * FROM offices WHERE status = 'On Track'   AND hidden = '0' AND choice = '3' AND region = 'Alexandria' AND sales_code = 'A052'"));

// The only differences in the statements are the STATUS and SALES_CODE columns

$a_b_result_pct_sc = ($a_b_result_sc / $a_total_sc)*100;
$a_c_result_pct_sc = ($a_c_result_sc / $a_total_sc)*100;
$a_d_result_pct_sc = ($a_d_result_sc / $a_total_sc)*100;
$a_o_result_pct_sc = ($a_o_result_sc / $a_total_sc)*100;

I'm using while ($row = mysqli_fetch_assoc… etc. to iterate through the data.
The problem is that I have over 700 offices to go through and just ONE of the offices takes more than a minute to process. That seems like a long time and I was trying to see if there was a faster way to get what I need -- which is BOTH the Number and the Percentage.
Is there a faster way?

You most likely could use just the one query here or a small subquery or aliasing. Make sure though that your table/database is properly indexed. It shouldn't take that much time to query.

The only index I have is on the auto increment ID column in the table. What other indices do you suggest?

It should be fine then. What is likely going on here that is slowing things down, is that you're using 2 functions all the time and querying over similar criterias. You could use a subquery as I said earlier, combination of AND|OR if needed also. Here's a tutorial link on subqueries http://www.mysqltutorial.org/mysql-subquery/ if that will help. Edit: Or a CASE.
