数据库查询未正确检索

Question

I am using zend framework and my sql to display some data.

Query is given below:

    $table = new Timesheets_Table_Tasks();
    $select = $table->select(true)
                ->setIntegrityCheck(false)
                ->join('timesheets_projects', 'timesheets_projects.ID=timesheets_tasks.ProjectID', array())
                ->join('timesheets_clients', 'timesheets_clients.ID=timesheets_projects.ClientID', array())


    return $this->filterSelect($select);

And the function filterSelect is given below:

protected function filterSelect($select)
    {
        $select->where("timesheets_tasks.UserID=?", Zend_Auth::getInstance()->getIdentity()->id);
        foreach($this->_filters as $filter => $value)
        {
            if(($value == "" || $value == "none") && $filter != "status")
            {
                continue;
            }

            switch($filter)
            {
                case 'q':
                    $select->where($this->_db->quoteInto('timesheets_tasks.Comment LIKE ?', '%' . $value . '%'));
                    break;
                case 'after':
                    $select->where($this->_db->quoteInto('timesheets_tasks.Date >= UNIX_TIMESTAMP(STR_TO_DATE(?, "%d/%m/%Y"))', $value));
                    break;
                case 'before':
                    $select->where($this->_db->quoteInto('timesheets_tasks.Date <= UNIX_TIMESTAMP(STR_TO_DATE(?, "%d/%m/%Y"))', $value));
                    break;
                case 'client':
                    $select->where($this->_db->quoteInto('timesheets_clients.ID = ?', $value));
                    break;
                case 'project':
                    $select->where($this->_db->quoteInto('timesheets_tasks.ProjectID = ?', $value));
                    break;
                case 'status':
                    $select->where($this->_db->quoteInto('timesheets_tasks.Status = ?', $value));
                    break;
                case 'jobtype':
                    $select->where($this->_db->quoteInto('timesheets_tasks.TypeID = ?', $value));
                    break;
            }
        }

        return $select;
    }

I displayed the query and it displays as:

SELECT timesheets_tasks.* FROM timesheets_tasks INNER JOIN timesheets_projects ON timesheets_projects.ID=timesheets_tasks.ProjectID INNER JOIN timesheets_clients ON timesheets_clients.ID=timesheets_projects.ClientID WHERE (timesheets_tasks.UserID='33') AND (timesheets_tasks.Status = 0) ORDER BY date DESC

But the query doesn't give the actual data.

I think it is an issue with join tables. When I removed the line " ->join('timesheets_clients', 'timesheets_clients.ID=timesheets_projects.ClientID', array())" from the query, the output showed correctly. But I have to select some of the field from the other two tables too. So I need a join property. Any other join can I use to get the column values from the other two tables and get the correct output?

I am new to joins. Any help should be appreciated!

Answer 1

Could be help you to resolve

  $table = new Timesheets_Table_Tasks();
    $select = $table->select(true)
                ->setIntegrityCheck(false)
                ->joinLeft('timesheets_projects', 'timesheets_projects.ID=timesheets_tasks.ProjectID', array())
                ->joinLeft('timesheets_clients', 'timesheets_clients.ID=timesheets_projects.ClientID', array())


    return $this->filterSelect($select)

Also please check with echo your select statement with join

hope this will work for you.