Hello everybody,

I have a piece of code built by somebody who worked before me on a website, and I cannot figure out why the count does not return the right result.

Here is the code:

if ($fields == self::SEARCH_FIELDS) {
//company name, location, open positions
$fields = array(
'c' => array('id'),
'cd' => array('name'),
'cn' => array('name' => 'AS location'),
'cs' => array('sector_id'),
'sc' => array('name' => 'AS sector_name')
);
$loc = true; $count_open_pos = true; $descr = true; $info = true;
} else if(is_null($fields)) $fields = "*";

if ( isset($filter['region']) ) $loc = true;

//TODO: check here oreder by, may not joined tables
$filter['sector']=1;
return $DB->selectPage(
$total,"
SELECT ?# { , COUNT(?#) AS `open_positions` }
FROM ?_companies c
{ LEFT JOIN ?_company_description cd ON c.id = cd.company_id AND 1=?d }
{ LEFT JOIN ?_company_info ci ON c.id = ci.company_id AND 1=?d }
{ LEFT JOIN ?_company_sectors cs ON c.id = cs.company_id AND 1=?d }
{ LEFT JOIN ?_countries cn ON ci.country = cn.id AND 1=?d }
{ LEFT JOIN ?_company_docs cdes ON cdes.company_id = c.id
LEFT JOIN ?_company_docs_index cdi ON cdes.id = cdi.doc_id AND 1=?d }
{ LEFT JOIN ?_job_company jc ON c.id = jc.company_id AND 1=?d }
{ LEFT JOIN ?_sectors sc ON cs.sector_id =sc.id }
WHERE 1=1
{ AND cd.name = ?s }
{ AND c.register_date >= ? }
{ AND ci.country = ?d }

{ AND cn.region_id = ?d }
{ AND ( cn.region_id = ?d OR ci.country = ?d ) }
{ AND cs.sector_id IN (?a) }
{ AND c.active = ?d }
{ AND c.suspended = ?d }
{ AND ( MATCH(`cd`.`description`) AGAINST (? IN BOOLEAN MODE) OR
MATCH(`cd`.`highlight_skills`) AGAINST (? IN BOOLEAN MODE) OR
MATCH(`cdi`.`context`) AGAINST (? IN BOOLEAN MODE) ) }
GROUP BY c.id
ORDER BY {$order_by}
LIMIT ?d, ?d
",
$fields,
(isset($count_open_pos) && $count_open_pos) ? array('jc' => array('job_id')) : DBSIMPLE_SKIP,

//LEFT JOIN ?_company_description
(isset($descr) && $descr) ? 1 : DBSIMPLE_SKIP,
//LEFT JOIN ?_company_info
(isset($info) && $info) ? 1 : DBSIMPLE_SKIP,
//LEFT JOIN ?_company_sectors
isset($filter['sector']) ? 1 : DBSIMPLE_SKIP,
//LEFT JOIN ?_countries
(isset($loc) && $loc) ? 1 : DBSIMPLE_SKIP,
//LEFT JOIN ?_company_docs
(isset($filter['free_text']) && $filter['free_text']) ? 1 : DBSIMPLE_SKIP,



//LEFT JOIN ?_job_company
(isset($count_open_pos) && $count_open_pos) ? 1 : DBSIMPLE_SKIP,

//AND cd.name = '?s'
isset($filter['company_name']) ? $filter['company_name'] : DBSIMPLE_SKIP,

//AND c.register_date >= ?
isset($filter['register_date_gt']) ? $filter['register_date_gt'] : DBSIMPLE_SKIP,



//AND ci.country = ?d
isset($filter['country']) && !isset($filter['region']) ? $filter['country'] : DBSIMPLE_SKIP,
//AND c.region_id = ?d
isset($filter['region']) && !isset($filter['country']) ? $filter['region'] : DBSIMPLE_SKIP,
//AND ( cn.region_id = ?d OR ci.country = ?d )
isset($filter['region']) && isset($filter['country']) ? $filter['country'] : DBSIMPLE_SKIP,
isset($filter['region']) && isset($filter['country']) ? $filter['region'] : DBSIMPLE_SKIP,

//AND cs.sector_id IN (?a)
isset($filter['company_sectors']) ? $filter['company_sectors'] : DBSIMPLE_SKIP,
//AND c.active = ?d
(isset($filter['active']) ? $filter['active'] : DBSIMPLE_SKIP),
//AND c.suspended = ?d
(isset($filter['suspended']) ? $filter['suspended'] : DBSIMPLE_SKIP),
//AND ( MATCH(...) AGAINST (? IN BOOLEAN MODE)
(isset($filter['free_text']) && $filter['free_text']) ? $filter['free_text'] : DBSIMPLE_SKIP,
(isset($filter['free_text']) && $filter['free_text']) ? $filter['free_text'] : DBSIMPLE_SKIP,
(isset($filter['free_text']) && $filter['free_text']) ? $filter['free_text'] : DBSIMPLE_SKIP,

//LIMIT
$page*$perPage,
$perPage

The code looks very fine and provides all the results needed. All but the "open_positions" which is returned with extremely high values. for example if a company has 2 jobs posted the count might return 18.

Does anybody have an ideea where might be the mistake?

Thanx in Advance