I am trying to perform this sql query in zend:
SELECT a.id AS aid,
a.assignment_name AS name,
IFNULL((a.id = sa.aid),'0') AS status,
a.assignment_name AS assignment_name,
c.class_name AS class_name,
a.due_date AS due_date,
a.post_date AS post_date,
(a.id = sa.aid) AS status,
IF(a.description="",'No description given',a.description) AS description,
IF(a.grading="",'No grading policy',a.grading) AS policy,
CONCAT(u.prefix,' ',u.lname) AS teacher
FROM `assignments` as a
JOIN classes AS c ON a.class_id = c.id
JOIN enrollments as e ON e.student_id = 9 AND e.approved = 1 AND c.id = e.class_id
JOIN users as u on u.id = a.teacher_id
LEFT JOIN student_assignments as sa ON a.id = sa.aid
group by a.id;
my zend code is this:
public function getAllStudent($count, $offset) {
$result = Zend_Db_Table::getDefaultAdapter()->select()
->from(array('a' => 'assignments'),
array(
'id' => 'a.id',
'assignment_name' => 'a.assignment_name',
'class_name' => 'c.class_name',
'due_date' => 'a.due_date',
'post_date' => 'a.post_date',
'status' => "(a.id = sa.aid)",
'description' => 'IF(a.description="",\'No description given\',a.description)',
'policy' => 'IF(a.grading="",\'No grading policy\',a.grading)',
'teacher' => 'CONCAT(u.prefix,\' \',u.lname)'
))
->join(array('c' => 'classes'), 'a.class_id = c.id', array())
->join(array('e' => 'enrollments'), 'e.student_id = ' . $this->auth->id . ' AND e.approved = 1 AND c.id = e.class_id', array())
->join(array('u' => 'users'), 'u.id = a.teacher_id', array())
->joinLeft(array('sa' => 'student_assignments'), 'a.id = sa.id', array())
->group('a.id')
->order('a.due_date DESC')
->limit($count, $offset)
->query()
->fetchAll();
return $result;
}
I am calling this from my controller as follows:
$this->view->assignments = $this->assignments->getAllStudent(20, 0);
When i perform this query in navicat (the first code paragraph) i get these results:
ID name !STATUS! assignment_name class date etc etc
24 Kill Animal 0 Kill Animal biology 2011-01-27 2011-01-26 You must kill a frog Instant death = 100points. Slow death = 40points Mr. Mihai
25 Solve Exercies 1 Solve Exercies Math 2011-01-31 2011-01-26 1 You must solve all exercises All exercises = 100points Mr. Mihai
All is fine, that is the result that i want to get. Look at STATUS column (i've put !! to emphasize it). For one result STATUS = 0 and for the other STATUS = 1. This is what it should return. However if i perform the same query using the above code (second code paragraph) i get the following:
array(2) {
[0]=>
array(9) {
["id"]=>
string(2) "25"
["assignment_name"]=>
string(14) "Solve Exercies"
["class_name"]=>
string(4) "Math"
["due_date"]=>
string(10) "2011-01-31"
["post_date"]=>
string(10) "2011-01-26"
["status"]=>
NULL
["description"]=>
string(28) "You must solve all exercises"
["policy"]=>
string(25) "All exercises = 100points"
["teacher"]=>
string(9) "Mr. Mihai"
}
[1]=>
array(9) {
["id"]=>
string(2) "24"
["assignment_name"]=>
string(11) "Kill Animal"
["class_name"]=>
string(7) "biology"
["due_date"]=>
string(10) "2011-01-27"
["post_date"]=>
string(10) "2011-01-26"
["status"]=>
NULL
["description"]=>
string(20) "You must kill a frog"
["policy"]=>
string(48) "Instant death = 100points. Slow death = 40points"
["teacher"]=>
string(9) "Mr. Mihai"
}
}
Here is what $result->__toString returns in zend for the query:
SELECT `a`.`id`,
`a`.`assignment_name`,
`c`.`class_name`,
`a`.`due_date`,
`a`.`post_date`,
(a.id = sa.aid) AS `status`,
IF(a.description="",'No description given',a.description) AS `description`,
IF(a.grading="",'No grading policy',a.grading) AS `policy`,
CONCAT(u.prefix,' ',u.lname) AS `teacher` FROM `assignments` AS `a`
INNER JOIN `classes` AS `c` ON a.class_id = c.id
INNER JOIN `enrollments` AS `e` ON e.student_id = 9 AND e.approved = 1 AND c.id = e.class_id
INNER JOIN `users` AS `u` ON u.id = a.teacher_id
LEFT JOIN `student_assignments` AS `sa` ON a.id = sa.id
GROUP BY `a`.`id`
ORDER BY `a`.`due_date`
DESC LIMIT 20
As you can see, status is NULL here. Why is this happening?
