zend-frameworkzend-db-select

Zend_Db_Select : multiple from clause


Hy, I have some problem vith Zend_Db_Select. I have 2 variable : category and city. These 2 variables may have a value or they may be unset. So I verify:

$status = '`p`.status = 1';
    if($catID){
        $catSQL =  "`p`.parent = {$catID}";
    }else{
        $catSQL = '1=1';
    }

    if($city){
        $citySQL =  "`pm`.`meta_key` = 'oras' and `pm`.`meta_value` = {$city}";
        $citySelect = array('pm' => 'postsmeta');
        $condCity = "`p`.`ID` = `pm`.`parent_id`";
    }else{
        $citySQL = '1=1';
        $citySelect = NULL;
        $condCity = '1=1';
    }

Now here is my query:

 $select = $db->select()
         ->from( array('p' => 'posts'))
         ->from($citySelect)
         ->where($status)
         ->where($catSQL)
         ->where($condCity)
         ->where($citySQL)
         ;

The problem is that if city is empty I have something like

 $select = $db->select()
         ->from( array('p' => 'posts'))
         ->from('')
         ->where(1=1)
         ->where(1=1)
         ->where(1=1)
         ->where(1=1)
         ;

The questions is how can I remove from('') from my query if city is empty. Thank you!


Solution

  • Simply,

    $select = $db->select()
    ->from( array('p' => 'posts'));
    if($someConditionIsTrue) {
        $select->join($table, $condition);
    }
    $select->where('field_value = ?', 'value1');
    if($someConditionIsTrue) {
        $select->where('another_field = ?', 'value 2');
    }
    

    Hope it helps.

    Please use this syntax $select->where('another_field = ?', 'value 2'); for proper escaping values to prevent SQL injections.