Query builder improvment

Question

I've got the following query builder. Some of the cases have the same piece of code (those that have a parameter).

I know it's not good to duplicate code that much if at all.

How would you suggest I improve this loop?

for ($i = 0; $i < $len; ++$i) {
 switch ($conditions[$i]['condition']) {
 case 'version':
 $value_counter++;
 $sql .= ' AND `musers`.`app_version` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'except_version':
 $value_counter++;
 $sql .= ' AND `musers`.`app_version` != :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'from_credits':
 $value_counter++;
 $sql .= ' AND `musers`.`credits` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'to_credits':
 $value_counter++;
 $sql .= ' AND `musers`.`credits` <= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'no_credits':
 $sql .= ' AND `musers`.`credits` = 0';
 break;
 case 'register_atleast':
 $value_counter++;
 $sql .= ' AND DATEDIFF(NOW(), `musers`.`creation_date`) > :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'talked_atleast':
 $value_counter++;
 $sql .= " AND `musers`.`total_talktime` >= :value".$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'never_talked':
 $sql .= " AND `musers`.`total_talktime` = 0";
 break;
 case 'purchase_atleast':
 $value_counter++;
 $sql .= " AND `musers`.`purchase_times` >= :value".$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'never_purchase':
 $sql .= ' AND `musers`.`purchase_times` = 0';
 break;
 case 'from_birthdate':
 $value_counter++;
 $sql .= " AND (`musers`.`birthdate` = '0000-00-00' OR `musers`.`birthdate` >= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'to_birthdate':
 $value_counter++;
 $sql .= " AND (`musers`.`birthdate` = '0000-00-00' OR `musers`.`birthdate` <= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'from_creation_date':
 $value_counter++;
 $sql .= " AND (`musers`.`creation_date` = '0000-00-00' OR `musers`.`creation_date` >= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'to_creation_date':
 $value_counter++;
 $sql .= " AND (`musers`.`creation_date` = '0000-00-00' OR `musers`.`creation_date` <= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'user_id':
 $value_counter++;
 $sql .= ' AND `musers`.`id` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'visit_app_aleast':
 $value_counter++;
 $sql .= ' AND `musers`.`visit_app_times` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'visit_app_less':
 $value_counter++;
 $sql .= ' AND `musers`.`visit_app_times` < :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'total_calls_atleast':
 $value_counter++;
 $sql .= ' AND `musers`.`total_calls` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'total_calls_less':
 $value_counter++;
 $sql .= ' AND `musers`.`total_calls` < :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'hourdiff':
 $value_counter++;
 $sql .= ' AND `hourdiff` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 }
}

• Elias Van Ootegem: Get rid of it, and use an existing querybuilder, that has been proven to work. Doctrine, for example. It'll save you a whole lot of time... clone its repo and edit the code, if you must

Answer 1

I just separated out the cases with no count increase. I am sure somebody more experienced could come up with a better solution.

for ($i = 0; $i < $len; ++$i) {
if 'version' = 'no_credits':
 $sql .= ' AND `musers`.`credits` = 0';
else if 'version' = 'never_talked':
 $sql .= " AND `musers`.`total_talktime` = 0";
else if 'version' = 'never_purchase':
 $sql .= ' AND `musers`.`purchase_times` = 0';
else
 $value_counter++;
 switch ($conditions[$i]['condition']) {
 case 'version':
 $sql .= ' AND `musers`.`app_version` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'except_version':
 $sql .= ' AND `musers`.`app_version` != :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'from_credits':
 $sql .= ' AND `musers`.`credits` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'to_credits':
 $sql .= ' AND `musers`.`credits` <= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'register_atleast':
 $sql .= ' AND DATEDIFF(NOW(), `musers`.`creation_date`) > :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'talked_atleast':
 $value_counter++;
 $sql .= " AND `musers`.`total_talktime` >= :value".$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'purchase_atleast':
 $sql .= " AND `musers`.`purchase_times` >= :value".$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'from_birthdate':
 $sql .= " AND (`musers`.`birthdate` = '0000-00-00' OR `musers`.`birthdate` >= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'to_birthdate':
 $sql .= " AND (`musers`.`birthdate` = '0000-00-00' OR `musers`.`birthdate` <= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'from_creation_date':
 $sql .= " AND (`musers`.`creation_date` = '0000-00-00' OR `musers`.`creation_date` >= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'to_creation_date':
 $sql .= " AND (`musers`.`creation_date` = '0000-00-00' OR `musers`.`creation_date` <= :value".$value_counter." )";
 $values[$value_counter] = date('Y-m-d', strtotime($conditions[$i]['value']));
 break;
 case 'user_id':
 $sql .= ' AND `musers`.`id` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'visit_app_aleast':
 $sql .= ' AND `musers`.`visit_app_times` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'visit_app_less':
 $sql .= ' AND `musers`.`visit_app_times` < :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'total_calls_atleast':
 $sql .= ' AND `musers`.`total_calls` >= :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'total_calls_less':
 $sql .= ' AND `musers`.`total_calls` < :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 case 'hourdiff':
 $sql .= ' AND `hourdiff` = :value'.$value_counter;
 $values[$value_counter] = $conditions[$i]['value'];
 break;
 }
}