下拉菜单和where语句

Question

I have 4 dropdown menus that on submit may or may not contain values that would be a where statement in a mysql query. Basically with out writing and if statement for every possible combination of dropdown I'd like to figure out a way to insert the where statement if $_POST['dropdownitem'] isn't empty. In the below block essentially anything with $_POST may or may not be there.

$select = dbz( )
->select( )
->from( array( 'l' => 'logs' ) )
->joinLeft( array( 'd' => 'dealers' ), 'l.dealerID = d.id' )
->joinLeft( array( 'p' => 'prospects' ), 'l.dealerID = p.id', array( 'id', 'name AS pname' ) )
->where( 'l.id = ?', $currentUser[ 'id' ] )
->where( 'l.dealerId = ?', $_POST[ 'dealerid' ] )
->where( 'logData LIKE ?', '%' . $_POST[ 'activitytype' ] . '%' );
->where( 'logTime >= ?', $today )
->where( 'logTime < ?', strtotime( $tomorrow ) );

$userLogs = dbz( )->fetchAll( $select );

Answer 1

There is no avoiding your need to validate each posted value differently, so you will need to at least have some conditional checks to ensure your query is constructed correctly.

Encapsulating it within a function, such as the below, might offer you better flexibility should the values change. I personally think a switch statement will also make it easier to read and again change in the future.

$select = dbz()
  ->select()
  ->from( array( 'l' => 'logs' ) )
  ->joinLeft( array( 'd' => 'dealers' ), 'l.dealerID = d.id' )
  ->joinLeft( array( 'p' => 'prospects' ), 'l.dealerID = p.id', array( 'id', 'name AS pname' ) )

public function addConditionals($query, $userId, array $data = array())
{
  $select->where('l.id = ?', $userId);
  foreach($data as $column => $value) {
    switch(strtolower($column)) {
      case 'dealerid':
        $select->where('l.dealerId = ?', intval($value));        
      break;
      case 'activitytype':
        $select->where('logData = ?', $value));        
      break;
      // etc...
    }
  }
}
addConditionals($select, $currentUser['id'], $_POST);