public function getAll() { $db = self::$_msql; $this->whereAndLimit(); $limit = $this->pagination ? $this->_limit : ''; $join = ''; $searchFields = $this->searchFields; if ($searchFields['country_id'] !== null || $searchFields['stext'] !== null) { $join .= ' LEFT JOIN `addresses` as a USING (`address_id`)'; } if ($searchFields['stext'] !== null) { $join .= ' LEFT JOIN `customers` as cus ON p.`customer_id` = cus.`customer_id`'; } $sql = "SELECT SQL_CALC_FOUND_ROWS p.`prospect_id`\n FROM `prospects` as p\n {$join}\n WHERE ?p\n ORDER BY p.`prospect_id` DESC\n {$limit}"; $results = $db->getCol($sql, $this->where); if (!$results) { $this->pagination->countItems = 0; return array(); } $countRows = $db->getCol("SELECT FOUND_ROWS()"); $this->pagination->countItems = isset($countRows[0]) ? $countRows[0] : 0; $sql = "SELECT\n p.`prospect_id`,\n p.`created`,\n p.`aff_id`,\n\t\t\t\t\taf.`aff_name`,\n p.`ip`,\n cus.`email`,\n a.`phone`,\n a.`fname`,\n a.`lname`,\n a.`address1`,\n a.`address2`,\n a.`city`,\n a.`state_id`,\n a.`country_id`,\n a.`zip`,\n c.`currency_id`,\n c.`url` as campaign,\n c.`campaign_name`\n FROM `prospects` as p\n\t\t\t\tLEFT JOIN `customers` as cus USING (`customer_id`)\n LEFT JOIN `campaigns` as c ON c.`campaign_id` = p.`campaign_id`\n LEFT JOIN `addresses` as a USING (`address_id`)\n\t\t\t\tLEFT JOIN `affiliates` as af using (`aff_id`)\n WHERE p.`prospect_id` IN(?a)\n ORDER BY p.`prospect_id` DESC"; $prospects = $db->getAll($sql, $results); $models = array(); foreach ($prospects as $prospect) { $prospectModel = new Prospect(); $prospectModel->fillFromEnterArray($prospect); $models[] = $prospectModel; unset($prospectModel); } return $models; /* $sql = "SELECT p.`prospect_id`, p.`created`, p.`aff_id`, af.`aff_name`, p.`ip`, cus.`email`, a.`phone`, a.`fname`, a.`lname`, a.`address1`, a.`address2`, a.`city`, a.`state_id`, a.`country_id`, a.`zip`, c.`currency_id`, c.`url` as campaign, c.`campaign_name` FROM `prospects` as p LEFT JOIN `customers` as cus USING (`customer_id`) LEFT JOIN `campaigns` as c ON c.`campaign_id` = p.`campaign_id` LEFT JOIN `addresses` as a USING (`address_id`) LEFT JOIN `affiliates` as af using (`aff_id`) WHERE ?p ORDER BY p.`prospect_id` DESC"; $sqlParse = $db->parse($sql, $this->where); return $this->getSqlParse($sqlParse); */ }