Thread: 500 HTTP error when passing WHERE clause in objectMapper->fetchAll()

Hi friends,

I was torn with whether to post this under jquery or here. I'm going to post it over there too. Whoever replies first, I'll just remove the other thread. I'm just really not sure whether the error is in how I have set up my db tables or how I'm using jQuery. Here's the situation:

I post data to a php function (an action in a controller) using jQuery's $.ajax method. In the PHP function, I attempt to access database information and send it back as a JSON-encoded array. No big deal, seems simple enough. However, when getting the information from the database, the following works:

Code:

$projectMapper = new Application_Model_ProjectMapper();
$projects = $projectMapper->fetchAll();

But this grabs all projects. I only want projects from a certain client, so I try this:

Code:

$projectMapper = new Application_Model_ProjectMapper();
$projectWhere = 'ClientID = ' . $client;
$projects = $projectMapper->fetchAll($projectWhere);

When I try this, jQuery returns a 500 status code. I don't get it. The field ClientID exists in the Projects table, and I can access it from other contexts. If I just create a regular zend controller / action, use this code and display the projects to the screen, the where clause is accepted. But when trying using jQuery, I get the 500 error code. Does this make sense? Here's some more information:

I have a few drop-down menus on a page used for filtering a report. All projects in this system belong to a client. So when the user selects any client from the $clientSelect drop-down list, I want to update the $projectSelect drop-down list to only show projects for the selected client. Here is sample code for both drop-down lists:

Code:

<select name="clientSelect" id="clientSelect">
  <option value=""></option>
  <option title="Client1" value="1">Client 1</option>
  <option title="Client2" value="2">Client 2</option>
</select>

Code:

<select name="projectSelect" id="projectSelect">
  <option value=""></option>
</select>

Initiall, projectSelect is empty. When someone selects a client from the client drop-down list, the projectSelect would look like this:

Code:

<select name="projectSelect" id="projectSelect">
  <option value=""></option>
  <option title="Project1" value="1">Project 1</option>
  <option title="Project2" value="1">Project 1</option>
  <option title="Project3" value="1">Project 1</option>
</select>

This form is located in /application/views/scripts/admin/reporting.phtml . At the bottom, I have this jQuery code:

Code:

<script type="text/javascript">
    
    $('#clientSelect').change( function() {
        
        var client = $('#clientSelect').val();
        
        var dataMap = {'client': client};

        $.ajax
        ({
            type: "POST",
            url: "/domain.com/admin/update-filter-boxes/",
            data: dataMap,
            dataType: 'json',
            cache: false,
            success: function(data)
            {
                $('#projectSelect').html(data.projectSelectHtml);
            },
            error:function (xhr, ajaxOptions, thrownError){
                alert('Error: ' + xhr.status + ': ' + thrownError);
            }
        });
    });
</script>

As you can see in the above jQuery code, I post the value of "client" to admin/update-filter-boxes. In my application/controllers/AdminController.php, I have the following action:

Code:

public function updateFilterBoxesAction()
    {

        $this->_helper->layout()->disableLayout();
        $this->_helper->viewRenderer->setNoRender(true);
        
        $resultArray = array();
        
            $client = $this->getRequest()->getParams('client');

            $projectSelectHtml = '<option value=""></option>';
            
            if($client)
            {
                $projectMapper = new Application_Model_ProjectMapper();
                
                $projects = $projectMapper->fetchAll();
                //$projectWhere = 'ClientID = ' . $client;
                //$projects = $projectMapper->fetchAll($projectWhere);
                if(count($projects) > 0)
                {
                    foreach($projects as $proj)
                    {
                        $projectSelectHtml .= '<option value="'.$proj->getProjectID().'">'.$proj->getName().'</option>';
                    }
                } 
            } 

        $resultArray['projectSelectHtml'] = $projectSelectHtml;
        echo json_encode($resultArray);
    }

I'm expecting the thing echoed from this function to be a JSON-encoded array with the key 'projectSelectHtml' whose value is the html code for the <options> of the projectSelect drop-down list. The above code will work, because I have not included the WHERE clause. However, if I include the WHERE clause, jQuery returns a 500 error code. Please let me know what I'm doing wrong!