mysql - OK or Bad? - Doctrine DBAL Queries in a PHP Loop -

mysql - OK or Bad? - Doctrine DBAL Queries in a PHP Loop -

i need little advice.

the task generate statistics mysql-database-table. im using doctrine dbal class queries.

so have 2 tables here (reduced example):

users-table

user_id | user_name | user_gender | ... --------------------------------------- 1234 | klark kent | 1 | ... 2468 | lex luthor | 1 | ... 3579 | louis lane | 2 | ... ....

checkins-table

checkin_id | checkin_user_id | checkin_month | ... -------------------------------------------------- 1 | 1234 | 1 | ... 2 | 2468 | 1 | ... 3 | 1234 | 1 | ... 4 | 1234 | 2 | ... ....

statistics-result should in end:

month | | male | female | unique ------------------------------------ jan | 312 | 179 | 133 | 224 feb | 295 | 169 | 135 | 218 mar | 301 | 154 | 147 | 230 ....

did way:

for ($i = 1; $i <= 12; $i++) { $qb = $db->createquerybuilder(); $stmt = $qb ->select('c.checkin_id') ->from(checkin_table, 'c') ->innerjoin('c', user_table, 'u', 'c.checkin_user_id = u.user_id') ->where('u.user_gender = 1') ->andwhere('c.checkin_month = :month') ->setparameter('month', (int)$i) ->execute(); ; $stats[$i]['male'] = $stmt->rowcount(); $qb = $db->createquerybuilder(); $stmt = $qb ->select('c.checkin_id') ->from(checkin_table, 'c') ->innerjoin('c', user_table, 'u', 'c.checkin_user_id = u.user_id') ->where('u.user_gender = 2') ->andwhere('c.checkin_month = :month') ->setparameter('month', (int)$i) ->execute(); ; $stats[$i]['female'] = $stmt->rowcount(); $stats[$i]['all'] = $stats[$i]['male']+$stats[$i]['female']; $qb = $db->createquerybuilder(); $stmt = $qb ->select('distinct checkin_user_id') ->from(checkin_table, 'c') ->where('checkin_month = :month') ->setparameter('month', (int)$i) ->execute(); ; $stats[$i]['unique'] = $stmt->rowcount(); }

as can see i'm running 3 queries in 12 times loop. queries relatively simple, might ok, glad hear sentiment if can done better?

maybe select entries , while-fetch loop big number of if clauses?

if ( ($row['checkin_month'] == 1) && ( $row['user_gender'] == 1 ) $stats[1]['male']++; if ( ($row['checkin_month'] == 1) && ( $row['user_gender'] == 2 ) $stats[1]['female']++; if ( ($row['checkin_month'] == 2) && ( $row['user_gender'] == 1 ) $stats[2]['male']++; if ( ($row['checkin_month'] == 2) && ( $row['user_gender'] == 2 ) $stats[2]['female']++;

here, 1 more question. have read using distinct remove duplicate entries not best practice, have no thought how otherwise. have read exists not work querying single table here distinct call, think.

if have hints or ideas allow me know. everybody.

lucky me, found solution myself after reading , searching lot of things.

so might have similar task, here how can done single query. (however have no thought how build using query-builder, works sql-statement.)

$sql = 'select c.checkin_month, count(*) count, count(distinct c.checkin_member_id) uniq, sum(case when u.user_gender = 1 1 else 0 end) male, sum(case when u.user_gender = 2 1 else 0 end) female '.checkin_table.' c, '.user_table.' u c.checkin_user_id = u.user_id grouping c.checkin_month'; $res = $db->fetchall($sql);

less code, less queries, same result.

php mysql doctrine2