doujiao3016
2013-07-26 17:19 阅读 31
已采纳

下拉菜单和where语句

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 );
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doupingdiao3546 doupingdiao3546 2013-07-26 17:58

    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);
    
    点赞 评论 复制链接分享

相关推荐