chautob0t chautob0t - 4 months ago 14
MySQL Question

Perform an Inner Join in Zend PHP Framework

I want to perform an inner join on two table.

Table A -

item_id

item_title

varX

Table B -

item_id

varY

someVar

This is how I've done this using a RAW SQL query.

$sql = 'SELECT tableA.item_id, tableY.item_title AS Name, 5 * varX + 5 * count(*) AS myScore
FROM tableA
INNER JOIN tableY ON tableA.item_id=tableY.item_id
WHERE someVar=\'8\'
GROUP BY item_id
ORDER BY myScore DESC
LIMIT 10';
$stmt = $this->_db->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();


Now I want to do this using a Zend Query.

This is what I've written -

$data = $this->_db->select()
->from(array('tablA'=>'tableA'), array('item_id', 'item_title'), 'myScore'=>'(5*'tableA'.'varX') + 5*count(*)')
->joinInner(array('tablB'=>'tableB'), 'tablA'.'item_id' = 'tablB'.'item_id')
->where('someVar = 8')
->GROUP('item_id')
->order('myScore DESC')
->limit(10);

$dataResult = $this->_db->fetchAll($data);


But I get this error -

syntax error, unexpected '=>' (T_DOUBLE_ARROW), expecting ',' or ')'


in line ->from(array('tablA'=>'tableA'), array('item_id', 'item_title'), 'myScore'=>'(5'tableA'.'varX') + 5*count()')

Not sure what to do do here as I've read the official documentation but still can't figure this out. Any help is appreciated!

Answer

The quotes were used in wrong way in your code. Also, tt seems like, you have used the unnecessary third parameter for 'myScore' field. It should be placed in the second parameter
Try the following:

...
$data = $this->_db->select()
->from(array('tablA'=>'tableA'), array('item_id', 'item_title', 'myScore'=>'(5 * tableA.varX) + 5*count(*)'))
->joinInner(array('tablB'=>'tableB'), 'tablA.item_id = tablB.item_id')
->where('someVar = 8')
->group('tablA.item_id')
->order('myScore DESC')
->limit(10);
Comments